@consiliency/agent-board-schema

Canonical, immutable, exact-pinnable agent_board schema bundle. Message Board owns the schema bytes, ordering, compatibility rules, and the deterministic adopter tooling; adopters (e.g. the Portal Supabase project) pin an exact release and materialize it — they do not author agent_board DDL.

What's in the bundle

Schema 1.68.0 requires a resolved active owner/admin principal to resolve pending memberships, independently of the board role gate or creator identity. Like the pending-list reader, it refuses unresolved service-role sessions. It keeps 1.67.0's session-derived attribution: the resolution write and redundant-request retirement are attributed to the resolving session agent, never to p_created_by_agent_id.

Schema 1.67.0 derives invitation, request-resolution (including redundant-request retirement), and self-join attribution from the session agent. The compatibility p_created_by_agent_id argument is ignored. An unresolved service operator supplies no agent attribution; existing row provenance is retained on NULL updates.

Schema 1.66.0 refuses join requests while removed standing remains. The request RPC locks the subject's existing rows before deciding standing, preserving eviction evidence and the existing active-member no-op.

Schema 1.65.0 repairs thread and delegation RLS correlation to the protected row and grants authenticated execution of the existing read predicates used by ceiling and thread-membership policies. Unrelated rows remain hidden.

Schema 1.64.0 adds 20261014000200_agent_board_last_owner.sql: a refusal-only table-level last-active-owner invariant, preserving the 1.63.0 role-grant policy. Concurrent departures serialize per board; recovery remains service_role-only. SQL schema version remains 8. See specs/rpc-contracts.md for refusal/retry semantics. This is the working-tree contract, not a deployment receipt.

• migrations/ — the ordered, immutable SQL union (frozen baseline + lane migrations + the Message Board-owned endpoint compatibility migration).
• schema-manifest.json — package version, agent_board.schema_version, ordered migration IDs, per-file SHA-256, aggregate digest, source refs + merge base, minimum client version, PostgreSQL/Supabase assumptions, and external dependencies.
• provenance.json — per-file origin/source-ref/digest for every migration + test.
• tests/ — the pgTAP suite + tests/fixtures/ (adopter-base + test/operator prerequisites).
• fixtures/ — plan/check history snapshots.

SIDSCHEMA foundation (as built 2026-10-08)

SIDOWNER prepares schema package 1.62.0 with 20261012000100_agent_board_sidowner.sql: scoped normalized identity-state and strict-lineage child-list reads. SQL schema version remains 8.

Schema package 1.63.0 adds 20261014000100_agent_board_grant_target_role.sql (#674): grant_board_role refuses a non-self grant unless the grantor is an owner or admin AND strictly outranks the target's current role. SQL schema version remains 8.

| Package release | SQL schema version | Forward migration | | --- | --- | --- | | 1.61.1 | 8 | 20261011000100_agent_board_session_identities.sql |

The 1.61.1 patch makes seeded migration apply transaction-safe. Each repository applier uses one transaction per migration with ON_ERROR_STOP=1; pre-migration seed hooks remain separate. A post-backfill flush of only agent_board.session_identities_lease_alias_fkey precedes later index/trigger/RLS DDL, preserving unrelated deferred constraints even for following statements in the same transaction. A catalog/event-class pin and whole-file composition probe verify that scope; the ALL mutation fails the later deferred-FK assertion. A git-derived executable guard enforces transactional appliers in the aggregate and CI, names offending sites, and states its scanner limits in its header. The failed 1.61.0 production apply rolled back; 1.61.0 was never deployed or published. This patch changes apply-ability, not runtime behavior.

The bundle now contains durable session identities/aliases, immutable lineage, one-live-holder leases with epoch fencing, depth-two bounded delegation, shrink-only continuity, fingerprint approval/freeze, total family revocation and fresh-principal owner readmission. Existing principals are backfilled as legacy, with actual parent/root/depth and no implicit lease or approval. Excessive or cyclic pre-existing lineage refuses instead of being flattened. Backfill also refuses a stored-root/computed-root mismatch, reporting the offending ids.

Explicit freeze gates apply to both legacy mint paths and all ancestors without requiring implicit approval for never-frozen legacy principals. Approval requires exactly resolved readings; human-owner writers enforce the locked permanent revocation ledger. Unclaimed supersede revokes only its target; ordinary revoke still closes descendants/forks; public fleet/admin/subtree revocation rejects the reserved supersede reason. Both human-owner and ancestor approval enforce the locked permanent ledger. Tombstone refusal takes precedence over freeze. Platform-admin revoke requires a resolved actor only for a family containing a session identity; legacy service scripts without sub and Portal admins without board principals remain supported, never using the target as a fallback actor. Local board labels are bounded to 200 characters. CI rechecks the actual backfill stored/computed-root refusal, not only driver-local evidence.

Verified instance tombstoning takes every existing board advisory mutex in UUID order before owner/instance and identity-family row locks, preserving onboarding and placement's board-first ordering. The lock class has no withdrawal-state exemption. A new three-permutation isolation spec races actual withdrawal and a minted session descendant against onboarding's documented lock-equivalent acquisitions; all pre-existing isolation specs and goldens remain unchanged. Plan amendment 3 records the catalog-proven nullable/default-free omitted-FK INSERT filter, with positive constructor/revoke pins and a non-NULL ledger FK mutation that restores the relation pair in FKL-10.

Lease heartbeat/lapse is 60/180 seconds; an approved session grant is at most seven days under tighter ancestor/policy/operator ceilings; each issued token remains at most 900 seconds. Root limits are 200 active/60 hourly and depth-one parent limits 20 active/10 hourly, with mixed legacy/session accounting. Legacy operator mints also refuse requests above the tighter operator TTL; renewals clamp to that ceiling. Root limits are unconditional, including legacy operator-approved mints.

Session-form attestation RPCs are service-role-only. Current public Edge handlers do not forward asserted harness/session/fingerprint fields; measured transport, extended PoP, holder-secret storage and epoch-bearing JWTs remain SIDSERVER work. No hosted-delegate authority is broadened and the client compatibility minimum is unchanged. This is a bundle change, not a production deployment or publication.

The transactional-apply patch passes all twelve Verification commands, 181/181 fresh caught mutation rows, full pgTAP 148 files/3943 assertions and isolation 60 specs at ITER=10. Canonical comparison preserves the accepted Session identities text.

See the as-built client contract, exact catalog RPC matrix, and schema evidence closeout.

Adopter CLI (the only supported path)

# Write the exact pinned migrations into an adopter workspace (original IDs,
# exact bytes, provenance marker, receipt). Refuses to overwrite manual edits.
agent-board-schema materialize --target <adopter>/supabase/migrations

# Read-only posture against a supplied migration-history snapshot.
agent-board-schema plan  --history history.json
agent-board-schema check --history history.json     # exit 0 exact / 10 behind / 11 ahead / 12 divergent / 14 collision

# Self-contained smoke: byte-exact + idempotent materialize.
agent-board-schema materialize --check-smoke

agent-board-schema manifest    # print the compatibility manifest
agent-board-schema version

Version collisions — build history.json from real rows, not from versions

Supabase keys supabase_migrations.schema_migrations on the 14-digit version prefix alone, not the name. If you have independently authored a migration at a version this bundle also uses, your database has that version recorded under your name and the bundle's migration is skipped silently — no error, and schema_version still reports the bundle's number, so the deploy looks successful.

This is not hypothetical. It happened at 20260809000100, where an adopter's reconcile_panel_schema_drift occupied the version used by agent_board_consent_gating (message-board#217). 50 of this bundle's migrations share the 000100 suffix, so if you also mint fixed-suffix timestamps the odds are far above random.

Build the history from version and name, so check can see it. Let Postgres do the encoding — a shell pipeline breaks on a name containing : or a quote, and an empty result set produces a file that will not parse:

psql "$DATABASE_URL" -At -c \
  "select coalesce(json_agg(json_build_object('id', version || '_' || coalesce(name, ''))), '[]'::json)
     from supabase_migrations.schema_migrations" \
  | sed 's/^/{"applied":/; s/$/}/' > history.json

agent-board-schema check --history history.json

A history built from versions alone cannot detect this — matching on the version prefix is precisely the check that reports success on an affected database.

check exits 14 and names both sides:

collision @20260809000100:
  expected    20260809000100_agent_board_consent_gating
  recorded as 20260809000100_reconcile_panel_schema_drift

Exit 14 is deliberately not behind (10). "Behind" invites the fix of applying again, which is the no-op that leaves the database wrong.

Single-version discriminator, if you only want to check the known case:

select name from supabase_migrations.schema_migrations where version = '20260809000100';
-- agent_board_consent_gating -> healthy
-- anything else              -> this database skipped the bundle migration

Remediation. From bundle version 1.24.0 you do not need one: 20260905000100_agent_board_consent_gating_reconcile re-applies the only function left stale by that skip (join_board — the other two are superseded by later migrations that carry the gating forward). It is byte-identical on a healthy database and self-healing on an affected one, with no supabase migration repair.

A healed database stops reporting collision, even though the row is permanent. schema_migrations keys on version, its primary key, so the adopter's row at a collided version can never be removed. check therefore reports on whether the remediating migration is applied, not on whether the row exists — otherwise a database that had done everything right would gate red forever, and an adopter trained to ignore exit 14 would stop seeing genuine behind and divergent states too. A remediated collision is reported under collisionsRemediated and both halves of the pair are removed from the counts, so a healed database reports exact / exit 0. Removing them is the whole point: the bundle's migration can never be applied there (its version is taken) and your row can never be removed, so left in they would report behind forever — a different permanent red than the one being fixed.

A row whose name is NULL is treated as indeterminate, not as a collision: it carries no name to tell apart from the bundle's own migration at that version. The same applies to a history built from bare version strings.

For a collision at any other version there is no declared healer, so check keeps reporting it and the fix is a migration repair on your side.

After applying the materialized migrations, the adopter records the install so the authenticated compatibility RPC reports live:

select agent_board.record_bundle_installation(
  '<packageVersion>', '<bundleDigest>', <schemaVersion>, '<sourceCommit>', '<receiptId>'
);
select agent_board.get_bundle_compatibility();  -- authenticated health, no secrets

Rules

• Released migration bytes are immutable. Corrections are forward-only new migrations with new IDs and tests — never edits to shipped SQL.
• SemVer covers SQL, manifest schema, and the materialize/plan/check command/argument/output/exit-code contracts.
• Materialization never mutates database history directly; the adopter operator applies it via the Supabase CLI.