The data model
The gateway uses SQLAlchemy Core and parameterized SQL against the private execbound PostgreSQL schema. There is no ORM entity layer. Alembic migrations are the DDL source of truth; SCHEMA_REVISION, the table/function catalog and privilege checks are in db.py. Read those values from the checkout being deployed. The migration history below describes the schema changes, while Supabase records dated hosted applies; repository state does not establish the live schema.
Relationships
erDiagram
tenants ||--o{ principals : contains
principals ||--o{ credentials : authenticates
tenants ||--o{ connector_accounts : configures
connector_accounts ||--o{ operation_mappings : binds
connector_accounts ||--o{ resources : represents
context_sources ||--o{ resources : attests
resources ||--o{ resource_aliases : names
resources ||--o{ incidents : relates
resources ||--o{ resource_facts : describes
context_sources ||--o{ resource_facts : attests
resources ||--o| resource_identities : maps
canonical_targets ||--o{ resource_identities : unifies
canonical_targets ||--o{ approval_scopes : assigns
tenants ||--o{ principal_groups : groups
principal_groups ||--o{ principal_group_members : holds
principals ||--o{ principal_group_members : joins
principals ||--o{ executions : requests
canonical_targets ||--o{ executions : targets
policy_bundle_contents ||--|| policy_bundle_lifecycle : governs
policy_bundle_contents ||--o{ executions : freezes
executions ||--o{ execution_keys : retries
executions ||--o{ execution_events : transitions
executions ||--o| approvals : requires
approvals ||--o{ approval_decisions : records
executions ||--o| execution_impact : accounts
executions ||--o| resource_claims : guards
credentials ||--o{ human_sessions : binds
tenants ||--o| audit_heads : sequences
tenants ||--o{ audit_events : records
This is a conceptual relationship map; the inventory below covers every table. Tenant ID participates in scoped primary and foreign keys. An execution is scoped to a principal, while its canonical target can unify multiple account-specific resource rows. A pending execution need not yet have an impact reservation or target claim.
Table inventory
All tables below have enabled and forced tenant RLS. Runtime access always includes SELECT. “Append” means INSERT without UPDATE/DELETE/TRUNCATE; listed mutable columns are the only allowed updates. Registry provisioning uses a separate trusted owner, not the protected agent or normal runtime.
| Table | Key / important bindings | Purpose and runtime writes |
|---|---|---|
tenants | tenant_id | Tenant name/active status; read only |
principals | tenant + principal_id; space FK | space_id with CHECK ((kind = 'AGENT') = (space_id IS NOT NULL)): an agent is owned by one space and a person is a member of spaces. AGENT, HUMAN_APPROVER or ADMIN; active status; label (1 to 80 characters), created_at (null for rows that predate 0036) and deactivated_at; read only, written through the definer functions; email (the address a person signs in with, lower case and globally unique across tenants, written only by admin_set_email or owner tooling) |
credentials | tenant + credential_id; principal FK | Token digest, issuance, expiry, revocation; read only |
spaces | tenant + space_id; unique (tenant, lower(name)) | A division of ownership and view inside one tenant: name (1 to 80), detail (1 to 160), creator and archived_at. Never deleted, only archived; read only, written through the six space definer functions |
space_members | tenant + space + principal; space and principal FKs | A person's role in one space, ADMIN or HUMAN_APPROVER. The role narrows and never raises: a person's authority is min(principals.kind, space role). An active ADMIN principal with no row here is a tenant administrator; read only, written through space_assign and space_unassign |
space_policy_selection | tenant + space; space and bundle FKs | The bundle one space has selected, with when and by whom. The primary key is what gives "at most one bundle per space"; the runtime holds INSERT and UPDATE (bundle_id, changed_at, changed_by), because activation is already a fenced store function with an audited ADMIN check |
audit_heads | tenant | Current sequence and hash; INSERT/UPDATE for serialized append |
audit_events | tenant + sequence; unique tenant/event ID; actor FK | Immutable canonical payload/hash chain; append |
connector_accounts | tenant + account; provider binding | Active configured provider account identity, no raw credentials; read only |
context_sources | tenant + account + source | Inventory/incident source kind, allowed writers, active status; read only for the runtime, created and deactivated by the owner context-source-* commands |
operation_mappings | tenant + operation reference | Exact account/provider/action/schema/context versions; read only |
resources | tenant + account + provider + resource type + resource ID | Provider ID/version, source, facts/observation time, active status; read only for the runtime; the owner context-import command updates the inventory columns, source, observation time and active flag, and version (the provider's expected version) only when a manifest states it |
resource_aliases | tenant + alias ID; full resource FK | Normalized hostname/provider ID with validity interval; read only |
incidents | tenant + account + incident ID; full resource/source FKs | Independently linked incident status/severity/version/time; read only for the runtime, created and updated by context-import |
resource_facts | tenant + account + provider + resource type + resource ID + fact name; resource and source FKs | Generic scalar facts for contracts that use row storage, with source, version and observation time; read only for the runtime, written and retracted by context-import |
agent_runs | tenant + run ID; principal FK | Server-issued run scope and expiry; append |
policy_bundle_contents | tenant + bundle ID; unique content hash binding | Immutable full bundle JSON, hash and creator; append |
policy_bundle_lifecycle | tenant + bundle ID; exact content FK | DRAFT/VALIDATED/ACTIVE/RETIRED; INSERT, update state, changed_at |
tenant_policy_control | tenant; stopped_by principal FK | Shared tenant fence, emergency stop (stop_id, stopped_at, stopped_by, stop_reason: all set or all null), enforcement mode (ENFORCE or OBSERVE), approval deadline (approval_deadline_seconds: 120 by default, 120 to 3600, or null for off), and assistant switch (assistant_enabled, false by default). Runtime INSERT and column-limited UPDATE; active bundle selection moved to space_policy_selection in 0041 |
canonical_targets | tenant + canonical target ID | Shared target identity; read only |
resource_identities | full account resource key; canonical target FK | Account representation → shared canonical target; read only |
approval_scopes | tenant + approver + canonical target + action | Explicit active reviewer assignment; read only |
principal_groups | tenant + group_id; unique tenant/name | Owner-provisioned agent group (name of 1 to 80 characters) that a group-scoped limit rule names; read only |
principal_group_members | tenant + group + principal; group and principal FKs | Current membership of active AGENT principals, at most 64 groups each, changed only by the owner group-assign and group-unassign commands under the tenant fence; read only |
executions | tenant + execution ID; unique tenant/operation ID | Frozen plan, identity, hashes, bundle, state and proof (a terminal OBSERVED execution keeps its would-be decision in result); since 0025_checkpoint, settlement_tier (ATTESTED or ASSERTED, null until a report settles an externally executed operation and null forever for one ExecBound dispatched) and report_expires_at (written once in the insert for a mapping that declares an external executor in a tenant that is enforcing, so it marks "this execution would be a checkpoint" and never "a grant is live"; a DENIED or stale row carries one too, and every reader pairs it with a state); INSERT, update state, changed_at, owner_hash, dispatch_attempted_at, result, settlement_tier. The report window has no update path at all |
execution_keys | tenant + principal + idempotency key | Immutable retry binding to execution and wire/intent hashes; append |
execution_events | tenant + event ID; execution/actor FKs | Immutable transitions and payloads; append |
approvals | tenant + approval ID; unique tenant/execution | Frozen plan/bundle/obligations, expiry, required approver count, completing decision identity; INSERT, update state, decided_by, decided_credential_id, decided_at |
approval_decisions | tenant + approval + principal ID; approval, principal and credential FKs | One recorded decision per distinct human per approval: credential, APPROVE or DENY, time; INSERT only |
execution_impact | tenant + execution ID; principal FK | Exact action, approved flag, unresolved/confirmed/nonexecution contribution, or an OBSERVED projection row that only observe-mode evaluations count; account_id, group_ids (JSON array) and facts (JSON object) retain the frozen intent's account, the principal's memberships at reservation and the frozen plan's scalar facts, written once at insert so limit scopes count over the row and never over current state (null where a row predates 0022_limit_scopes and the backfill could not derive them; group_ids is always null on such rows); INSERT, update state, confirmed_at |
resource_claims | tenant + execution ID; canonical target FK | One unreleased owner per target; INSERT, update released_at |
human_sessions | tenant + session ID; principal/credential FKs | Session/CSRF digests, credential binding, role and expiry (at most eight hours after creation and never past the credential); INSERT, update revoked_at; the expiry is extended only through the renew_human_session definer function; label, one of a fixed set of device names chosen from the User-Agent at sign-in and written with the row (0035) |
approval_notifications | tenant + notification ID; unique approval/destination; approval FK | Outbox row per pending approval and operator destination, queued in the approval's transaction; INSERT, update the delivery columns (state, attempts, next_attempt_at, lease_until, lease_token, delivered_at, last_error) |
export_cursors | tenant + destination ID | One security-event export cursor per operator destination: last exported sequence and hash, last failure window, attempts, lease and state; INSERT, update the cursor and delivery columns |
authentication_failures | failure ID; unique surface/reason/minute window/tenant/subject (nulls not distinct); nullable tenant FK | Bounded failed-authentication counters per surface (http, mcp, human, monitor) and reason (MALFORMED, UNKNOWN, MISMATCH, EXPIRED, REVOKED, INACTIVE); tenant and subject set only when authentication verified the named credential or monitoring source exists, otherwise all three null; INSERT, update count, last_at |
executor_keys | tenant + key_id; tenant FK | Owner-provisioned Ed25519 public keys an external executor's report is verified against (External execution checkpoint): key id, the raw 32-byte public key as 64 lowercase hex characters, created_at, revoked_at (at or after creation) and, since 0026_key_compromise, revocation_reason, NOT NULL exactly when the row is revoked and one of RETIRED or COMPROMISED. No private key or shared secret exists here, so read access cannot forge the attested tier; runtime SELECT only, with no INSERT allowlist entry and no update grant, written by the four executor-key-* owner commands |
arcade_hooks | tenant + principal + arcade_execution_id; principal and execution FKs | One control point hook exchange (Arcade logic extension): the platform's own execution id, the derived idempotency key, the tool identity, the reported metadata and user id as retained inert text, the extension's decision (OK, CHECK_FAILED or RATE_LIMIT_EXCEEDED), the ExecBound execution and operation, the grant's report secret until settlement, the reported success flag and platform codes, the two contradiction stamps and the creation and settlement times. Keyed on the principal as well as the tenant, because an Arcade execution id carries no ExecBound scope and two projects of one tenant minting the same id needs no misbehaviour from either. The one place in the system that holds a usable report secret at rest: it is nulled at settlement and cleared opportunistically once a report window has closed. INSERT, SELECT, a bounded UPDATE over the decision, execution, operation, secret, reported and contradiction columns and the settlement time, and no DELETE or TRUNCATE; the tenant, principal, Arcade id, derived key, tool identity, reported metadata and user id and creation time are written once |
onboarding_slots | tenant (PK, tenant FK); unique seed + slot number | A pre-seeded sandbox slot (sandbox onboarding): seed label, slot number, seeded_at and claimed_at (null while the inactive tenant waits for a claim), plus the lifecycle of open signups — expires_at (kept by a trigger at claimed_at plus fourteen days, for every writer), notice_at, extended_until, deactivated_at and converted_at, with the effective end coalesce(extended_until, expires_at) and a converted sandbox having none; runtime SELECT only, written by the seeder, sandbox-adopt, sandbox-extend and sandbox-convert over the owner connection and, for claimed_at, notice_at and deactivated_at, by the onboarding_claim, sandbox_notice and sandbox_expire definer functions |
onboarding_invitations | tenant + invitation_id; slot FK; at most one row per slot that is neither redeemed nor revoked (partial unique index) | Operator-minted single-use codes: SHA-256 of the ebi1 code, a note of who it was for, issue and expiry times, redeemed_at, revoked_at and failure_count (0 to 5; the fifth secret mismatch revokes); no runtime privilege at all, written by the invite-* owner commands and the claim function |
assistant_turns | tenant + turn_id; principal_id FK | A person's assistant turns (#330): context kind and id, the question (up to 1,000 characters), state (RUNNING, COMPLETED, FAILED with one reason from a closed list), steps, the answer as structured text, cited ids, model and token counts; runtime SELECT only, written through assistant_turn_create, assistant_turn_step, assistant_turn_complete and assistant_turn_retire, which nulls the text thirty days after completion and keeps the counts |
principal_passwords | tenant + principal_id; principal FK | One salted scrypt hash with its own algorithm, n, r, p and dklen beside it, so a row written under older parameters keeps verifying; runtime SELECT only, written by password_redeem and password_replace |
password_resets | tenant + reset_id; principal FK | One-time set-password codes as their SHA-256, with expires_at, redeemed_at, revoked_at and a failure counter bounded at five; at most one live row per person by partial unique index; runtime SELECT only |
principal_identities | tenant + principal_id + provider + subject | A provider account bound to a person: google or github, the provider's stable subject, and the address and display name as hints. One account is one person anywhere (a global partial unique index on provider and subject) and one person holds one account per provider; runtime SELECT only |
Open sandbox signups add two tables in the operator tenant (design, #243), because every row belongs to a tenant and a signup request precedes the prospect's:
| Table | Key and scope | What it holds |
|---|---|---|
signup_requests | operator tenant + request_id | One stranger's request for a sandbox: the bare lower-cased email (nulled by retention), its domain and whether it is free mail, the consent_version the form named, requested_at, the state (QUEUED, INVITED, CLAIMED, EXPIRED, REFUSED), a refusal_code present exactly when refused, the slot_tenant_id and invitation_id it was given, invited_at, claimed_at, notice_sent_at and purge_after; forced RLS, runtime SELECT only, written solely by the signup_request, signup_allocate and signup_mark definer functions and read by signups --list over the owner connection |
signup_messages | operator tenant + message_id; request FK; one row per request per class | The outbox: the message_class (invitation, waitlist, lifecycle), the slot and invitation it names, and the delivery state, attempts (0 to 5), next attempt, lease and last error. Rows are inserted by signup_allocate; the runtime holds UPDATE on the seven delivery columns and nothing else, so a leased queue can move its own lease and nothing can re-point the invitation a message carries. The address is not on the row: the send reads it from the request. |
Monitoring adds seven tenant tables: monitor_profiles (immutable, versioned detection profiles set by a current ADMIN; one active per tenant, retired never edited or deleted; findings name the version that produced them), monitor_sources (story/token digest/lifecycle and receipt counters, plus, after a rotation, the current token digest beside the immutable registration digest, the previous digest with its grace deadline, the rotating and revoking administrators, and the persisted receipt-time health state with its change and check times), monitor_observations (immutable event plus mutable evaluation state), monitor_findings (stable rule binding plus acknowledgement; a source-health finding binds to its source with no observation, a clock-skew finding to the observation whose reported time is implausible), monitor_deliveries (bounded retry/lease state), monitor_connections (one active read-only collection connection per tenant: pinned origin, optional team scope, the API key's SHA-256 fingerprint and never the key, probed capabilities, health with its episode start, the durable audit cursor, the platform tenant pinned by the first collected record, and the last run's outcome, error code and retry hint) and monitor_inventory (allowlisted story, action and credential metadata per connection, with the outbound classification left null for an action type the collector does not recognize, and on story rows the coverage class and gap episode the last reconciliation decided). Scout adds four more: monitor_agents (one row per directory principal a connection enumerated, with the class the directory's own object type states, the sponsors as object identifiers only, a sponsor state that keeps absent, unreadable and not_read apart, the blueprint and the credential kinds it holds, and the ExecBound principal an ADMIN has linked), monitor_agent_grants (each grant the agent holds, with the object it actually sits on, so an inherited permission names the blueprint where it has to be removed), monitor_agent_reach (one row per action per path, with the certainty that path can establish, the enforcement mode it can support, the catalog version that produced it and the moment its proof lapses) and monitor_reach_exceptions (an ADMIN's recorded decision to live with one ungoverned path). Nothing in any of the four is deleted: a grant or a path that is gone is marked gone_at and kept, and the runtime role holds no DELETE on them at all. monitor_connections gains a kind whose origin check is per platform, so one tenant runs one connection per platform rather than one in total. monitor_observations carries either a source_id or a connection_id and never both, with evidence distinguishing a customer-instrumented report from a platform-collected record, and monitor_findings follows the same origin rule. All force RLS, use composite tenant/source foreign keys and deny runtime DELETE/TRUNCATE. Source identity and event content cannot be updated by the runtime. The monitoring guide documents atomicity, grants and limits. Monitoring history is separate from the protected execution audit chain.
resources.privilege_class is nullable to preserve endpoint rows and constrained to standard, privileged, tier0 when present. Identity resolution requires appropriate trusted values even though the shared database column is nullable. Application validation is stricter than some generic registry SQL columns.
Important unique indexes enforce one active bundle per tenant, one unresolved execution per tenant/principal/intent hash, and one unreleased claim per tenant/canonical target. execution_keys retains key ownership across completion. The explicit action checks on approval scopes and impact admit only the six catalog actions. Read the migration for exact column lengths, timestamp constraints and covering indexes before changing a model.
Audit versus projections
audit_events, execution_events, retry keys and bundle contents are append-only to the runtime. Current heads, execution state, approval decisions, impact settlement, claims and sessions are mutable projections with column grants. Mutating a projection directly bypasses the coordinator's atomic invariants even if SQL permissions technically allow it; use the domain functions.
Every audit append locks/creates the tenant head and records one deterministic canonical event with its previous hash. Durable execution intent, approval consumption and reserved impact commit together. A later proof produces new outcome/reconciliation events. There is no automatic purge or supported “reset budget” operation. Runtime DELETE/TRUNCATE is denied on every tenant table.
The replay export captures audit history and referenced bundle content rather than copying mutable tables as historical truth. Legacy gaps remain unresolved. Independently retaining a checkpoint is necessary for stronger tamper detection than a self-contained export/head pair. See Replay.
Roles and connection behavior
Every tenant_isolation policy compares tenant_id against (SELECT current_setting('execbound.tenant_id', true)). The subquery is uncorrelated, so PostgreSQL evaluates it once per statement as an InitPlan instead of once per row; the isolation it enforces is identical to the direct call. tests/test_database.py asserts that no policy in the schema uses the per-row form, so a new table cannot reintroduce it.
authentication_failures deliberately keeps two permissive policies rather than one merged OR: tenant_isolation for a tenant's own rows and unattributed_counters for rows with no tenant, which a pre-authentication failure cannot have. Supabase's advisor reports the pair as multiple_permissive_policies. Merging them would save one policy evaluation on a table written only on failed authentication and read only by the administration surface, and would cost the separation that makes each grant readable and separately auditable — a single expression is easier to widen by accident when editing the other half. The pair stays until the table is measured to be hot.
execbound_app must not own application objects, create schema objects, bypass RLS, create roles/databases, replicate or inherit another role's authority. check_runtime() verifies the role, exact table catalog, forced RLS, allowed writes, the exact function catalog and Alembic revision; /health/ready invokes it.
Migration 0021_admin_functions adds five SECURITY DEFINER functions owned by the schema owner with a pinned search_path: admin_create_principal, admin_issue_credential, admin_revoke_credential, admin_assign_approver and admin_unassign_approver. execbound_app holds EXECUTE on them and nothing else new: principals, credentials and approval_scopes stay SELECT-only for the runtime. Each function reads the tenant from execbound.tenant_id (the setting forced row-level security uses, which also fences the owner), verifies that the actor is an active ADMIN of an active tenant, refuses an ADMIN kind, principal or credential, bounds a credential lifetime to 1 minute to 90 days and the caller's clock to 5 minutes from the database's, and writes the row shape the owner tooling writes; a refusal raises its code (INVALID_ACTOR, INVALID_KIND, INVALID_PRINCIPAL, INVALID_LIFETIME, INVALID_CLOCK, INVALID_DIGEST, INVALID_CREDENTIAL, INVALID_TARGET, INVALID_ACTION, INVALID_SCOPE, INVALID_TENANT) and rolls the caller's transaction back. check_runtime() asserts that exactly these functions run as definer, are not owned by the runtime role and are executable by it, and that no other function the runtime could call exists in the schema (trigger functions, which cannot be called, are the one exception). Migration 0027_approval_deadline adds renew_human_session, the only way a live console session's expiry moves, and 0028_onboarding adds two more with the same ownership and pinned search_path: onboarding_claim, which needs no acting ADMIN because its authority is the invitation only the owner connection can mint (it verifies the invitation's hash, expiry and single use under a row lock, counts a secret mismatch by answering NULL so the count survives the caller's rollback, and then, all or nothing, names and activates the slot, inserts the first ADMIN and its credential, and marks the invitation redeemed and the slot claimed; refusals raise INVALID_INVITATION, INVALID_SLOT, INVALID_NAME, NAME_TAKEN or the 0021 codes), and sandbox_reattest, which for an active ADMIN of a claimed sandbox re-stamps resources, resource_facts and incidents with the current time, values untouched, and sets each target's resources.version from the resulting_version in the retained receipt of the latest EXECUTED operation ExecBound dispatched to it. Migration 0042_console_invitations adds onboarding_invitation_revoke, which is the console's half of the same family: no actor, because a slot tenant holds no principal to check, and its authority is the operator's configuration exactly as onboarding_invite's is. Migrations use a separate owner connection. Local setup and hosted owner provisioning differ; follow Supabase for hosted grants.
Database.transaction(tenant_id) sets execbound.tenant_id transaction-locally with set_config(..., true). Normal transactions use READ COMMITTED; coherent snapshot reads can request REPEATABLE READ. Pool reuse cannot retain the prior transaction's tenant. Both USING and WITH CHECK apply to the tenant predicate. A missing setting sees no tenant rows.
The private schema has no grants to Supabase API roles or PUBLIC. RLS protects scoped application use, not compromise of the trusted runtime credential. Do not replace the explicit tenant predicate with a Supabase Auth/JWT policy without designing a new authentication boundary.
Migration history
| Revision | Change | Tenant tables after upgrade |
|---|---|---|
| 0001_foundation | Identity, credentials and audit; private grants and forced RLS | 5 |
| 0002_identity_context | Account/mapping registry, trusted sources/resources/aliases/incidents, runs | 12 |
| 0003_database_tuning | Covering indexes and once-per-statement tenant setting lookup | 12 |
| 0004_policy_bundles | Immutable content, lifecycle and tenant policy fence | 15 |
| 0005_execution_state | Canonical identity, scoped approval, execution, impact and claims | 24 |
| 0006_human_sessions | Bounded revocable human sessions | 25 |
| 0007_identity_context | Read-only privilege evidence and explicit identity action checks | 25 |
| 0008_monitoring | Separate sources, observations, findings and delivery state; downgrade refuses retained history | 29 |
| 0009_context_contracts | Read-only generic resource facts for versioned context contracts | 30 |
| 0010_authentication_failures | Bounded, attributed authentication failure counters; a second policy admits unattributed rows to every tenant context; downgrade refuses retained rows | 31 |
| 0011_tenant_stop | Emergency stop columns on the tenant control row; downgrade refuses while a stop is active | 31 |
| 0012_source_rotation | Monitoring source token rotation with a bounded grace window and revocation attribution; the registration digest stays immutable and the runtime updates only the current digest, expiry and lifecycle columns | 31 |
| 0013_source_health | Persisted source health state and source-health and clock-skew finding rules; findings without an observation; downgrade refuses retained health findings | 31 |
| 0014_detection_profiles | Versioned per-tenant detection profiles, profile-stamped findings and the sensitive-action.v2, restricted-target.v1 and observed-rate.v1 rules; downgrade refuses retained profiles and findings | 32 |
| 0015_approval_notifications | Transactional outbox for approval notifications to operator destinations; downgrade refuses retained rows | 33 |
| 0016_export_cursors | Per-destination cursors for the security event export; downgrade refuses while a cursor has advanced | 34 |
| 0017_approval_quorum | Frozen required approver count on approvals and one decision row per distinct human; downgrade refuses while decision rows remain | 35 |
| 0018_soc_actions | Widened action checks to the six catalog actions; downgrade refuses retained new-action rows | 35 |
| 0019_observe_mode | Per-tenant enforcement mode on the control row and the terminal OBSERVED execution and impact state; downgrade refuses while a tenant observes or observed rows remain | 35 |
| 0020_resource_facts_long | resource_facts integer values cover the full signed 64-bit Long range instead of 18 digits; downgrade refuses while a wider value is retained | 35 |
| 0021_admin_functions | Five SECURITY DEFINER administration functions executable by the runtime role, so the credential and approver pages write without any table privilege; downgrade drops them | 35 |
| 0022_limit_scopes | Owner-provisioned principal_groups and principal_group_members (forced RLS, runtime SELECT only) and the account_id, group_ids and facts impact attributes with an account covering index and a GIN index; existing rows backfilled from executions.frozen with group_ids left null; downgrade refuses while any group, membership or bundle content with a scoped rule or actions list exists | 37 |
| 0023_monitor_collection | monitor_connections and monitor_inventory for read-only platform collection, and observations and findings that carry either a reporting source or a collection connection with an evidence label; downgrade refuses while collected evidence is retained | 39 |
| 0024_monitor_rls_initplan | The six monitoring tables' tenant_isolation policies read the tenant setting through an uncorrelated subquery, so PostgreSQL evaluates it once per statement as 0003 already does elsewhere; isolation is unchanged and no grant or table changes | 39 |
| 0025_checkpoint | executor_keys (forced RLS, runtime SELECT only) and the settlement_tier and report_expires_at columns on executions, with UPDATE (settlement_tier) as the runtime's only new grant; downgrade refuses while any executor key, settlement tier or report window is retained | 40 |
| 0027_approval_deadline | tenant_policy_control.approval_deadline_seconds (default 120, null for off, checked to 120..3600) with UPDATE for the runtime role; the human_sessions lifetime ceiling raised from 15 minutes to 8 hours and the renew_human_session SECURITY DEFINER function that extends a live, unrevoked session forwards only, within that ceiling and the credential's expiry; downgrade cuts extended sessions back to 15 minutes | 40 |
| 0026_key_compromise | executor_keys.revocation_reason, backfilled RETIRED and constrained to be present exactly when the row is revoked, so nothing can retire a key without saying whether it was an ordinary retirement or a compromise the verifier reads backwards; downgrade refuses while any key is recorded COMPROMISED | 40 |
| 0028_onboarding | onboarding_slots and onboarding_invitations (forced RLS; runtime SELECT on slots only), a case-insensitive unique index on tenants.name (the upgrade refuses while two tenant names collide), and the onboarding_claim and sandbox_reattest definer functions; downgrade drops all of it and leaves claimed sandboxes as ordinary tenants | 42 |
| 0039_signups | signup_requests and signup_messages (forced RLS; runtime SELECT on both, UPDATE on the outbox's seven delivery columns and no INSERT or DELETE), the onboarding_slots lifecycle columns with the trigger that keeps expires_at, and the seven definer functions onboarding_invite, onboarding_invitation_rekey, sandbox_expire, sandbox_notice, signup_request, signup_allocate and signup_mark. The upgrade stamps every sandbox already claimed as converted, so nothing running acquires a fourteen-day fuse because a migration ran; downgrade drops the tables, the columns and the functions and leaves every claim and invitation as it was | 43 |
| 0029_claim_clock | onboarding_claim stamps redeemed_at and claimed_at from the database clock instead of the caller's issue time, so a gateway clock behind the operator's or the seeder's cannot fail an honest claim's check constraints; signature, ownership and grants unchanged | 42 |
| 0030_scout_agents | monitor_agents, monitor_agent_grants, monitor_agent_reach and monitor_reach_exceptions, and the connection kind with a per-platform origin check; downgrade refuses while agent evidence is retained | 46 |
| 0031_scout_rules | The two agent episode markers and the agent a finding names, with the three Scout rules admitted to the finding check; downgrade refuses while agent findings are retained | 46 |
| 0032_connector_precondition | executions records the precondition class each dispatch rested on and, for CHECKED_BY_CONNECTOR alone, the window the connector measured; two check constraints bound the class to its four values and the window to that one class, written with IS NOT DISTINCT FROM so a NULL class cannot carry a window past a CHECK that rejects only an explicit FALSE | 46 |
| 0033_reported_failure | One table, arcade_hooks, and one vocabulary move. The correlation table is owner-shaped like the rest — forced RLS, a tenant_isolation policy, revocation from the five roles, runtime INSERT and SELECT and a bounded UPDATE, no DELETE or TRUNCATE. The state check constraint on executions.state, execution_events.from_state and execution_events.to_state is redefined on all three columns to admit FAILED_EFFECT_UNKNOWN, exactly as 0019_observe_mode did for OBSERVED; the impact states are untouched. Downgrade refuses while any correlation row is retained, and again while any of the three columns holds the new state anywhere in the database — all three, because a corrected row's own state is FAILED_NOT_EXECUTED or STALE_AUTHORIZATION and only its retained events name the new one | 47 |
| 0035_session_label | human_sessions.label (nullable text, 1 to 80 characters), the device label written at session issue and never updated; no grant change, because the runtime's table-level INSERT covers it | 47 |
| 0036_people | principals.label, created_at and deactivated_at, and the admin_label_principal, admin_deactivate_principal and admin_reactivate_principal SECURITY DEFINER functions; deactivation revokes the person's credentials and sessions in the same function; no grant change | 47 |
| 0037_agent_bindings | agent_bindings (one to one while active, forced RLS, runtime SELECT only) and the admin_bind_agent and admin_unbind_agent SECURITY DEFINER functions, which keep monitor_agents.linked_principal_id in step; the runtime role loses its UPDATE on that column; existing links to active agent principals become bindings and any other link is cleared; downgrade refuses while bindings exist | 48 |
| 0038_assistant_turns | assistant_turns (forced RLS, runtime SELECT only) with its four SECURITY DEFINER functions, and tenant_policy_control.assistant_enabled (false by default, runtime UPDATE on that column) | 49 |
| 0040_sign_in | principals.email with a global unique index, principal_passwords, password_resets and principal_identities (all forced RLS, runtime SELECT only) and seven SECURITY DEFINER functions; authentication_failures.subject_kind widens to admit principal. Two extra single-row SELECT policies do the one read that happens before a tenant is known: a principals row whose own email equals execbound.sign_in_email, and a principal_identities row whose own provider and subject equal execbound.sign_in_subject. The runtime gains no table write. Downgrade refuses while any failure row is attributed to a person | |
| 0041_spaces | spaces, space_members and space_policy_selection (all forced RLS with the tenant policy in its once-per-statement form; runtime SELECT on the first two and INSERT plus three update columns on the third), space_id on principals, connector_accounts, policy_bundle_contents, approval_scopes, executions, approvals, execution_impact, monitor_sources, monitor_connections, monitor_observations, monitor_findings and (nullable, a reader's filter only) audit_events, offered_tenant_wide on the two offerable tables, and human_sessions.current_space_id. Every tenant is given one Default space and every column is backfilled with it; every HUMAN_APPROVER principal is seated in it and no ADMIN is, so every existing administrator stays a tenant administrator. The selection moves off tenant_policy_control (the column, the runtime's grant on it and the "one ACTIVE per tenant" index all go), admin_create_principal and admin_assign_approver are recreated with a space, and the six space functions and session_set_space are added. Downgrade refuses while any tenant holds more than one space and otherwise restores the column, the grant, the index and the two old signatures | |
| 0042_console_invitations | onboarding_invitation_revoke, a fenced SECURITY DEFINER function with no actor that ends an invitation which is neither redeemed nor already revoked, and a column-level runtime SELECT on onboarding_invitations naming every column except token_hash. The console needs a revoke rather than a rekey to a dead digest, because the partial unique index of 0028 counts a live row and not a usable code; it needs the columns to list the pool, and it needs the digest for nothing, so the grant stops short of it and tests/test_onboarding.py SO18 asserts the refusal column by column. No table gains a write, and downgrade drops the function and the grant |
Alembic alone owns application schema history. The app never migrates on startup. New migrations must preserve old frozen bytes, operation proof and retained uncertainty. Migration 0007 refuses downgrade while identity data or history remains in any tenant. Migration 0022 refuses downgrade while any group, membership or stored bundle content with a group, account or selector scope or an actions list exists, and otherwise drops the two tables and three columns, because the backfilled account and facts are re-derivable from executions.frozen. Migration 0018 refuses downgrade while any tenant retains a mapping, approval scope, impact row, frozen plan, execution event, bundle content or audit event that names endpoint.lift_isolation or identity.reset_password. Migration 0032 refuses downgrade while any Arcade correlation row is retained or any of its three constrained state columns holds FAILED_EFFECT_UNKNOWN, read over the whole database rather than one tenant as a migration must. Migration 0025 refuses downgrade while any executor key row exists or any execution carries a settlement tier or a report window, and 0026 while any key is recorded COMPROMISED, because 0025 reads every revocation as backward-permissive and dropping the reason would hand a stolen key back its authority over the grants it was holding. Migration 0011 refuses downgrade while any tenant's emergency stop is active. Migration 0010 refuses downgrade while any authentication failure row remains; unattributed rows (no tenant, subject kind or subject) are admitted by a second policy, unattributed_counters, in every tenant context because they carry no tenant data. Migration 0009 refuses downgrade while any tenant retains resource_facts rows, an operation mapping naming a contract other than endpoint-isolation.v1 or identity-control.v1, stored bundle content with context_settings or with a mapping that names another contract (including a DRAFT bundle that was never validated), or a frozen plan, execution event or audit event that names another contract. Migration 0041 refuses downgrade while any tenant holds more than one space, because merging would be a decision about which customer's objects become whose. Deleting history to force a downgrade is not a recovery procedure. Destructive round-trip tests run only on the guarded local test database.
Provider stores
Provider SQLite stores are outside the gateway schema and are not managed by Alembic. mock_store.py and identity_store.py own their schemas and operation semantics. Each stores provider resources, account representations and immutable terminal journals/cancellation tombstones; tests inspect invocation counts separately from physical effects.
SQLite BEGIN IMMEDIATE serializes the expected-version check, effect and terminal journal. Endpoint state records containment; identity state records enabled status, session generation, password generation and the must-change flag. Provider kind is bound to its store, and a service rejects a mismatched store. Recreating these stores while PostgreSQL retains unresolved executions destroys reconciliation evidence. See Provider and Operations.