Ordered append

Some tables are a log, not a set: chat messages, run events, an outbox. They need three things a plain insert cannot give you.

This is a platform data primitive (compiler + trigger + membership RLS). It is not Buzz and not a hosted Chat Relay. Buzz is a Nostr websocket on another hostname. If your product stores conversations, declare them here, expose the virtuals, and optionally also turn on Buzz for Nostr clients.

1. A position the caller cannot choose. Two writers must never claim the same slot, and a client must not be able to place itself anywhere it likes.
2. A replay that is exactly once. A response lost after the commit must be recoverable by retrying the same request, without writing a second row.
3. An order a consumer can trust. A reader that has advanced past position 41 must never be shown a 39 afterwards.

Declare an append policy on the table and the compiler emits all three.

Declare

databases:
  - slug: relay
    tables:
      - slug: conversation_messages
        append:
          position_field: sequence
          scope: [conversation_id]
          idempotency:
            key_fields: [request_id]
            fingerprint_field: payload_hash
        fields:
          - { slug: conversation_id, type: uuid, required: true }
          - { slug: request_id, type: text, required: true }
          - { slug: payload_hash, type: text }
          - { slug: sequence, type: number, required: true }
          - { slug: content, type: text }

Apply it the same way as any other structure change, with data.appdata.structure.apply. See Declare structures.

What you get

Applying the policy emits, per table:

• a counter relation keyed by the scope columns;
• UNIQUE (scope…, position) — the order invariant;
• UNIQUE (scope…, key_fields…) — the replay key;
• a BEFORE INSERT trigger that allocates the position;
• an append_<table>(payload jsonb) function returning {record_uuid, position, replayed}.
• when append.effects is declared, the same function takes (payload jsonb, effects jsonb) and writes companion rows in the same transaction. Failure of an effect rolls the message back.

Both are SECURITY INVOKER, so nothing here raises your privilege.

Two ways to write

-- Through the function: you get the replay indicator back.
select append_conversation_messages(
  '{"conversation_id":"…","request_id":"req-1","payload_hash":"…","content":"hi"}'::jsonb
);
-- → {"record_uuid": "…", "position": 42, "replayed": false}

A plain insert — including a direct PostgREST POST — still works and still gets a server-allocated position, because the trigger fires on every insert path. You just don't get the replayed flag back.

Why a counter and not a sequence

An identity column or a Postgres sequence looks like the obvious answer. It fails requirement 3, and it fails by construction rather than by accident.

A sequence hands out its number outside the transaction. So two writers can take 41 and 42, the holder of 42 can commit first, a consumer can advance its watermark past 42, and only then does 41 commit — appearing below a point the reader has already passed. Measured, not assumed.

The allocation is therefore a counter row lock held until commit: allocation order is commit order, so a position can never surface late.

Replay

Same key, same fingerprint → the original row comes back with replayed: true. Nothing is written twice.

Same key, different payload → appdata_idempotency_conflict, surfaced over HTTP as a 409. A changed payload under a used key is a bug in the caller, and silently accepting either version would hide it.

Concurrency is handled: when several first-time callers race, one insert wins and the rest replay the winner's row rather than surfacing a unique violation.

Membership-scoped rows

Owner scoping answers "is this row mine". A conversation needs "am I in this conversation" — a fact that lives in another table.

      - slug: conversation_messages
        membership:
          via: conversation_participants
          match: [conversation_id]

The row is visible only when the caller holds a live row in conversation_participants matching on conversation_id.

This compiles to a RESTRICTIVE row-level policy scoped to your app's end-user role. Restrictive matters: permissive policies OR together, so they can only ever widen what an end-user reaches. Membership has to narrow, and AS RESTRICTIVE is the only thing in Postgres that ANDs with the tenant predicate instead.

By default the membership row is matched on owner_end_user_uuid. The membership table cannot itself be membership-scoped — that is what makes the check terminate.

Companion rows (append.effects)

A message and its delivery-intent must commit together. Declare effects on the append policy so the compiler emits append_<table>(payload jsonb, effects jsonb).

      - slug: conversation_messages
        append:
          position_field: sequence
          scope: [conversation_id]
          idempotency:
            key_fields: [request_id]
            fingerprint_field: payload_hash
          effects:
            - table: delivery_intents
              carry:
                conversation_id: conversation_id
                message_uuid: record_uuid
        fields:
          - { slug: conversation_id, type: uuid, required: true }
          - { slug: request_id, type: text, required: true }
          - { slug: payload_hash, type: text }
          - { slug: sequence, type: number, required: true }
          - { slug: content, type: text }

Apply with data.appdata.structure.apply (dry_run: false) on a new App. Introspect until the function is append_conversation_messages(payload jsonb, effects jsonb). One call writes both rows; a constraint failure on the intent leaves zero message rows for that request_id.

Two separate transaction.apply ops are not this contract. data.appdata.record.append is not registered — use the serving function.

The compiler only emits the two-argument signature when policy.effects is non-empty. A table with append but no effects stays (payload jsonb) only.

What you never do

• Allocate the position in your client and send it. It will be discarded, and any ordering you inferred from it is wrong.
• Use a uuid or a timestamp as the cursor for a log a consumer replays. Neither gives you a dense, gap-free, commit-ordered position.
• Reuse an idempotency key across payloads. That is a conflict by design.
• Reach for membership when owner already answers the question. Restrictive policies compose; every one you add is another predicate on every read.

Related

Declare structures · Ownership & workspaces · PostgREST HTTP