Separating when a record is created from when it becomes valid

I've built three systems in unrelated domains that needed the same decision: a customs invoicing system in 2020, remittance terminals in 2022 and a meeting platform in 2023.

The products, teams and stacks were different each time, but each design came down to the same question: when does this record count? The moment it was written and the moment it binds are not always the same.

Most data models don't ask. They treat a row that exists as real. That works until the moment of creation and the moment of validity drift apart, and then the model fails without raising an error.

Every system that produces documents eventually runs into this, and most run into it too late.

Templates change: a layout is corrected, a legal notice updated or a logo replaced. Then someone opens a document issued two years ago and sees this year's template, with this year's legal text, on a record that was signed under different terms.

No error was raised and no test went red. The document had simply changed, and the archive no longer showed what had been issued.

The same problem appears wherever creation and validity can drift apart, for example with a coupon printed with no network or a whiteboard that only exists as a screenshot.

The customs invoicing requirement was absolute: an issued invoice must never change.

The default design is to store the invoice data and render the document when someone asks for it. That is the natural design, and here it breaks, because a document rendered on demand is a function of three things at read time: the data, the template and the rendering code. Change any of the three and every old document changes with it.

So at the moment of issue, the system renders the document and stores it as immutable HTML. Later template edits touch nothing already issued.

In Node.js and PostgreSQL the shape is roughly this:

1async function issueInvoice(db, invoiceId) {2  return db.tx(async (t) => {3    const invoice = await t.one(4      'SELECT * FROM invoices WHERE id = $1 FOR UPDATE',5      [invoiceId],6    )7    if (invoice.issued_at) throw new Error('Invoice already issued')8 9    const template = await t.one(10      'SELECT * FROM invoice_templates WHERE active',11    )12    const html = renderInvoice(invoice, template)13 14    await t.none(15      `INSERT INTO issued_invoices (invoice_id, html, issued_at)16       VALUES ($1, $2, now())`,17      [invoiceId, html],18    )19    await t.none(20      'UPDATE invoices SET issued_at = now() WHERE id = $1',21      [invoiceId],22    )23  })24}

Reading an issued invoice then means returning the stored HTML, without ever calling renderInvoice again.

This separates the definition of a document from the issued document, so the template can keep evolving while the history stays intact.

A second benefit is easy to miss: the frozen output no longer depends on the rendering code staying correct in the future. If documents were rendered on demand, a bug introduced next year in a date formatter would retroactively alter every invoice ever issued. With the output frozen, a future bug can damage only future documents.

Immutability that exists only in application code is a convention, and the next person with a migration script can break it. To make it a rule, let the database refuse the change:

1CREATE FUNCTION reject_change() RETURNS trigger AS $$2BEGIN3  RAISE EXCEPTION 'issued invoices cannot be changed';4END;5$$ LANGUAGE plpgsql;6 7CREATE TRIGGER issued_invoices_immutable8BEFORE UPDATE OR DELETE ON issued_invoices9FOR EACH ROW EXECUTE FUNCTION reject_change();

The remittance terminals had the opposite problem. A recipient collects cash at a filling station using a coupon code. The terminal has to keep working when the network is down, but a coupon that pays out twice is the one failure that costs real money.

If creation and validity are the same event, you're stuck. Either the terminal refuses to print without a connection and stops being useful where connectivity is worst, or it prints a coupon that's immediately payable, and you've built a way to pay out money the central system doesn't know about yet.

Splitting the two events removes the dilemma. The coupon prints but is not yet valid, and it becomes valid when the system says so: automatically when connectivity returns, or manually through an operator, who is signaled that an unactivated coupon exists.

On the terminal, in Kotlin, the shape is two states and a sweep that runs when the network comes back:

1enum class CouponState { PRINTED, VALID }2 3data class Coupon(4    val code: String,5    val state: CouponState,6    val printedAt: Instant,7    val validatedAt: Instant? = null,8)9 10class CouponSync(11    private val dao: CouponDao,12    private val api: CouponApi,13) {14    // printing only records that the coupon exists15    fun print(code: String): Coupon =16        Coupon(code, CouponState.PRINTED, Instant.now())17            .also { dao.insert(it) }18 19    // validity comes from the system, never from the terminal alone20    suspend fun validatePending() {21        dao.findByState(CouponState.PRINTED).forEach { coupon ->22            api.register(coupon.code)23            dao.markValid(coupon.code, Instant.now())24        }25    }26 27    // what the operator is shown28    fun pending(): List<Coupon> = dao.findByState(CouponState.PRINTED)29}

What matters in this code is that the in-between state is stored and visible. The system knows a coupon was created and not honored, so that state can't be lost without anyone noticing. A coupon that is printed but not yet valid is a known fact, with an operator attached to it.

The asymmetry decides which way to fail: better an unusable coupon than one paid out twice. An unusable coupon is an inconvenience that someone can resolve. A coupon paid out twice is money gone.

The meeting platform needed the whiteboard to survive the meeting as something you could still work with. The default here is a screenshot: when the session ends, save the canvas as an image. The board then exists in the sense that there's a record of it, but only as a picture, and a picture can only be viewed.

So the whiteboard is saved as structured objects instead of a flat image, and every stroke, shape and piece of text keeps its own properties. The board can be reloaded later and edited.

With Fabric.js the difference is one method call:

1// a picture of the board: viewable, not editable2const image = canvas.toDataURL({ format: 'png' })3 4// the board itself: every object with its properties5const board = canvas.toJSON()6await api.put(`/meetings/${meetingId}/board`, board)7 8// next time, the same board, still editable9const saved = await api.get(`/meetings/${meetingId}/board`)10await canvas.loadFromJSON(saved)11canvas.requestRenderAll()

At first glance the invoice and the whiteboard point in opposite directions. The invoice is frozen as rendered output, and the whiteboard is not.

In both cases, though, the question was what the stored record is for. An issued invoice is for proving what was issued, so the system stores exactly what was issued and nothing is allowed to re-derive it. A whiteboard is for continuing the work, so the system stores the objects you work on instead of a picture of them. The coupon adds time: the record exists first and counts later, and the system has to know which of the two it's holding.

In each of the three products, the record survived the event that usually destroys it.

Whenever a system produces something that someone will rely on later, I ask four things before designing the table:

1. Can what this record shows change after it's created? If it's derived at read time from anything that evolves, such as templates, code or reference data, then yes, whether you meant it to or not.
2. Should it? An invoice shouldn't. A whiteboard should stay editable. Neither answer is the default, and both have to be chosen.
3. Can it exist before it counts? If yes, those are two states, and they need two representations instead of one row and a hopeful assumption.
4. What moves it from one state to the other, and can anyone see the records in between? If a pending state is invisible, records get lost in it without anyone finding out.

On the customs system, freezing the issued document was correct but incomplete, because we never versioned the template itself. No issued invoice could change, but there was no record of which template version produced it, or of who changed that template and when.

The snapshot answered the urgent question and left the audit question open. The fix is small if you make it at the start: make templates append-only, like issued documents, and point each issued document at the version that produced it.

1CREATE TABLE invoice_template_versions (2  id          bigserial PRIMARY KEY,3  body        text        NOT NULL,4  changed_by  text        NOT NULL,5  changed_at  timestamptz NOT NULL DEFAULT now()6);7 8ALTER TABLE issued_invoices9  ADD COLUMN template_version_id bigint10  REFERENCES invoice_template_versions (id);

With the output frozen and the template versioned, the archive shows both what was issued and why it looks the way it does.