Docs Guides

Adopting over a database that has no revision column

Turnframe requires every mutable case to carry a monotonic revision, and every command to check that revision in the same statement that writes the row. An application built on Turnframe from the start gets this for free. An application being migrated does not, and the transition has a hazard that is easy to describe and easy to miss.

While the old runtime is still live, both runtimes write the same rows. If an old write changes a trip and leaves the revision where it was, a card that was bound to revision N stays valid over a record that has moved. The user then confirms a rebooking against a preview of a trip that no longer exists, and the confirmation is honoured, because as far as the check-and-bump statement can tell nothing happened in between. That is precisely the defect the revision binding exists to prevent, reintroduced by the migration itself. It never shows up in the Turnframe test suite, because the Turnframe write path is correct; it shows up only in the window where the two runtimes overlap, which is exactly the period nobody wants to be surprised in.

The recipe below closes it. The SQL is dependency-free (no extension, no ORM, nothing but PostgreSQL), and it is not a transcription: the integration test crates/turnframe-store-postgres/tests/revision_migration_recipe.rs reads these blocks out of this file and executes them against a live database, so the statements printed here are the statements that were proven. Adapt the table and column names; keep the shapes.

3.1 The table as the legacy application left it #

sql

-- turnframe-recipe: legacy-tableCREATE TABLE trip (    id          TEXT   PRIMARY KEY,    account_id  TEXT   NOT NULL,    total_cents BIGINT NOT NULL);

Two things are worth noticing before anything is added. There is no revision, and there is no way to tell from the row whether it changed since a card was rendered. Everything below exists to make that second question answerable.

3.2 Add the column #

sql

-- turnframe-recipe: add-revisionALTER TABLE trip ADD COLUMN revision BIGINT NOT NULL DEFAULT 1;ALTER TABLE trip ADD CONSTRAINT trip_revision_positive CHECK (revision > 0);

BIGINT NOT NULL DEFAULT 1 is deliberate. From PostgreSQL 11 onwards a new column with a constant default is recorded in the catalogue instead of being written into every row, so this is fast on a large table and needs no separate backfill statement: every existing row reads as revision 1 immediately. Keep the default rather than dropping it afterwards, because legacy inserts do not know the column exists and must still produce a valid row. The check constraint is there so that a write which computes a revision arithmetically can never park a row at zero, which is the value CaseRevision::ZERO reserves for "this case does not exist yet".

3.3 The statement Turnframe writes #

sql

-- turnframe-recipe: check-and-bumpUPDATE trip   SET total_cents = $3,       revision = revision + 1 WHERE id = $1   AND account_id = $2   AND revision = $4;

This is the only shape the executor uses. The check and the write are one statement, so no other transaction can slip between them, and the revision the card was bound to is the revision the write demands.

Zero rows affected is not a failure to retry. It means the case moved between the load and the write, and the executor turns it into ExecutionError::RevisionConflict, which the runtime turns into a stale card rather than into an overwrite. The account predicate is not decoration either: every lookup is account-scoped, and a stale revision and another tenant's row have to be indistinguishable from the outside.

3.4 Make every legacy write bump it #

A legacy write has no card to honour, so it does not gain a revision predicate. It only has to stop pretending that nothing happened. Each site changes from this:

sql

-- turnframe-recipe: legacy-write-forgottenUPDATE trip   SET total_cents = $3 WHERE id = $1   AND account_id = $2;

to this:

sql

-- turnframe-recipe: legacy-writeUPDATE trip   SET total_cents = $3,       revision = revision + 1 WHERE id = $1   AND account_id = $2;

The common case is that these writes are concentrated. The first adopter's own instance is sixteen UPDATE sites in a single file for one workflow and one upsert for the other, and at that scale this is a mechanical edit that a reviewer can check by eye. The upsert needs the same treatment, with the table name qualifying the column because EXCLUDED holds the row that was proposed rather than the row that is stored:

sql

-- turnframe-recipe: legacy-upsertINSERT INTO trip (id, account_id, total_cents, revision)VALUES ($1, $2, $3, 1)ON CONFLICT (id) DO UPDATE   SET total_cents = EXCLUDED.total_cents,       revision = trip.revision + 1;

Do not bump in a second statement. A separate UPDATE trip SET revision = revision + 1 after the data write leaves a window in which the row carries new data and an old revision, and a card validated inside that window is exactly the bug this whole section is about.

3.5 Prove that no write path skips it #

Grep finds the sites you already know about. It does not find the report job that writes through a view, the back-office screen in another service, or the statement built by an ORM whose text never appears in your repository. The proof has to come from the database, because the database is the one place every write must pass through.

sql

-- turnframe-recipe: audit-and-guardCREATE TABLE IF NOT EXISTS tf_revision_bump_audit (    id           BIGSERIAL   PRIMARY KEY,    table_name   TEXT        NOT NULL,    row_id       TEXT        NOT NULL,    old_revision BIGINT      NOT NULL,    new_revision BIGINT      NOT NULL,    observed_at  TIMESTAMPTZ NOT NULL DEFAULT now(),    statement    TEXT        NOT NULL); CREATE OR REPLACE FUNCTION tf_require_revision_bump() RETURNS triggerLANGUAGE plpgsql AS $$DECLARE    enforcing BOOLEAN := coalesce(        current_setting('turnframe.enforce_revision_bump', TRUE), 'off'    ) IN ('on', 'true', '1');BEGIN    IF NEW.revision > OLD.revision THEN        RETURN NEW;    END IF;    IF enforcing THEN        RAISE EXCEPTION 'write to %.% left revision at %',            TG_TABLE_SCHEMA, TG_TABLE_NAME, OLD.revision            USING ERRCODE = 'check_violation';    END IF;    INSERT INTO tf_revision_bump_audit        (table_name, row_id, old_revision, new_revision, statement)    VALUES        (TG_TABLE_NAME,         to_jsonb(OLD) ->> TG_ARGV[0],         OLD.revision,         NEW.revision,         left(current_query(), 500));    RETURN NEW;END;$$; DROP TRIGGER IF EXISTS trip_require_revision_bump ON trip;CREATE TRIGGER trip_require_revision_bump    BEFORE UPDATE ON trip    FOR EACH ROW    WHEN (OLD.* IS DISTINCT FROM NEW.*)    EXECUTE FUNCTION tf_require_revision_bump('id');

-- turnframe-recipe: audit-and-guard
CREATE TABLE IF NOT EXISTS tf_revision_bump_audit (
    id           BIGSERIAL   PRIMARY KEY,
    table_name   TEXT        NOT NULL,
    row_id       TEXT        NOT NULL,
    old_revision BIGINT      NOT NULL,
    new_revision BIGINT      NOT NULL,
    observed_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
    statement    TEXT        NOT NULL
);

CREATE OR REPLACE FUNCTION tf_require_revision_bump() RETURNS trigger
LANGUAGE plpgsql AS $$
DECLARE
    enforcing BOOLEAN := coalesce(
        current_setting('turnframe.enforce_revision_bump', TRUE), 'off'
    ) IN ('on', 'true', '1');
BEGIN
    IF NEW.revision > OLD.revision THEN
        RETURN NEW;
    END IF;
    IF enforcing THEN
        RAISE EXCEPTION 'write to %.% left revision at %',
            TG_TABLE_SCHEMA, TG_TABLE_NAME, OLD.revision
            USING ERRCODE = 'check_violation';
    END IF;
    INSERT INTO tf_revision_bump_audit
        (table_name, row_id, old_revision, new_revision, statement)
    VALUES
        (TG_TABLE_NAME,
         to_jsonb(OLD) ->> TG_ARGV[0],
         OLD.revision,
         NEW.revision,
         left(current_query(), 500));
    RETURN NEW;
END;
$$;

DROP TRIGGER IF EXISTS trip_require_revision_bump ON trip;
CREATE TRIGGER trip_require_revision_bump
    BEFORE UPDATE ON trip
    FOR EACH ROW
    WHEN (OLD.* IS DISTINCT FROM NEW.*)
    EXECUTE FUNCTION tf_require_revision_bump('id');

Installed like this the guard changes no behaviour: a write that forgot the bump still succeeds, and the only difference is that it now leaves its own name behind. current_query() records the statement text, so the audit table tells you which site to fix rather than that some site exists. The trigger argument names the primary key column, so the same function serves every table you migrate.

Two clauses deserve a note. WHEN (OLD.* IS DISTINCT FROM NEW.*) means an update that changed no column never fires the guard, which is right: a write that moved nothing did not invalidate anything. And a transaction that rolls back leaves no audit row, which is right for the same reason: a rolled-back write did not move the record, so no card became unsafe.

Leave the guard in audit mode for a full business cycle, not for an afternoon. The write paths that skip a revision are, in practice, the ones that run monthly.

3.6 Enforce #

sql

-- turnframe-recipe: enforceSELECT set_config('turnframe.enforce_revision_bump', 'on', FALSE);

Per session is how this is rehearsed and how the test exercises it. In production, once the audit table has stayed empty for a full cycle, set it once on the database with ALTER DATABASE app SET turnframe.enforce_revision_bump = 'on'; and let it apply to every new connection. From then on a write path that forgets the bump fails with SQLSTATE 23514 instead of quietly retiring the guarantee. That is the right trade: a rejected legacy write is an incident with a stack trace, while an accepted one is an incident with a confused user and no evidence.

3.7 When the writes are not concentrated #

If the writes are spread over dozens of call sites, generated by an ORM, or partly inside stored procedures, editing each one is not a plan. Let the database do the bump:

sql

-- turnframe-recipe: auto-bumpCREATE OR REPLACE FUNCTION tf_bump_revision() RETURNS triggerLANGUAGE plpgsql AS $$BEGIN    IF NEW.revision = OLD.revision THEN        NEW.revision := OLD.revision + 1;    END IF;    RETURN NEW;END;$$; DROP TRIGGER IF EXISTS trip_bump_revision ON trip;CREATE TRIGGER trip_bump_revision    BEFORE UPDATE ON trip    FOR EACH ROW    WHEN (OLD.* IS DISTINCT FROM NEW.*)    EXECUTE FUNCTION tf_bump_revision();

Name it so that it sorts before the guard. Triggers on the same event fire in name order, so trip_bump_revision runs first and trip_require_revision_bump then sees a revision that has already moved and lets the write through. Reverse the two names and the guard rejects exactly the writes the auto-bump was about to repair.

Three consequences are worth saying out loud. The auto-bump guarantees the bump but not the check, so the statement in 3.3 still carries its own AND revision = $n; the trigger leaves an explicit bump alone, which is why the two compose. It hides the sloppy sites instead of naming them, because with it installed the audit table stays empty whether or not any write path was ever fixed. And it bumps on writes with no semantic meaning (a denormalized counter, a last-seen timestamp), which invalidates more cards than strictly necessary. That last one is a cost in re-confirmations, not in correctness. Prefer the audit-then-enforce route while the writes are still few enough to fix by hand, and use the auto-bump when they are not, knowing that you traded discovery for safety.

3.8 The cards that predate the column #

Every existing row now reads as revision 1, a number that meant nothing an hour ago. Any interaction created before the backfill is bound to a revision drawn from a different numbering, and some of those will compare equal by accident. At cutover, invalidate every open interaction on the migrated workflow with InvalidationReason::Administrative { code } rather than letting it resolve. A card the user has to click again is a small annoyance; a card that matches by coincidence is the incident this section exists to prevent.