Database schema

Oko database schema#

Oko’s hosted database schema is owned by the dedicated `wisent-supabase-oko` repository. The deployed Supabase project reference is alvaewvbyxpgwdpugnxy.

Source of truth#

The ordered migrations in wisent-supabase-oko are the canonical definition of the database. They own the tables, functions, policies, grants, indexes, and other schema objects consumed by Oko products.

Application repositories may depend on this contract, but they must not redefine or privately own the schema. Client code, including Oko’s desktop, web, and iOS surfaces, must treat the database as an external versioned dependency.

Change workflow#

Every schema change starts as a migration in wisent-supabase-oko and is reviewed through a pull request. Pull-request CI applies the complete migration history to a fresh database and runs Splinter so that failures are caught against the full schema rather than an already-mutated developer project.

A pull request proves that the migration chain can create the expected database; it does not deploy the hosted project.

Deployment#

After review, merging to main deploys the canonical migrations to the Supabase project declared by SUPABASE_PROJECT_REF. Application repositories do not deploy schema changes and must not contain a competing migration history.

This boundary keeps review, fresh-stack verification, and production deployment attached to one auditable source.

Message identity allocations#

Message identity and message content have different lifecycles. A native turn UUID keeps its identity when its text is corrected. A message without a native ID is identified by its original timestamp at full source precision and its complete content digest, or by its source ordinal when no timestamp exists.

oko_identity_allocations keeps each allocated origin and task ID independently of oko_operator_messages. Replaying or appending a transcript does not assign an existing task's evidence, verdicts or qualification receipts to another occurrence. Removing a message projection does not remove its allocation. source_at retains the original timestamp spelling used by the canonical encoder; the PostgreSQL timestamp alone cannot recover precision it rounded. The allocation table itself refuses changes to its organization/member, session, source key, native UUID, allocated origin/digest or task ID. Without a native UUID, the original timestamp and content digest are also immutable; an untimed source additionally keeps its identifying ordinal. Native-message corrections can update their body digest and observed time, and timestamped/native messages can move display ordinals without moving the allocated identity. A refusal names the original session/source and every changed field with its stored and supplied value; the rejected update leaves the binding unchanged on every API surface, including direct authenticated database requests.

The coordinated schema/application cutover includes the identity-origin, allocation, original-source owner, part-binding and reconciliation migrations. oko tasks derive --session <session-id> --transcript <original-transcript> --write supplies source coordinates and digests to the transactional reconciliation RPC. The RPC restores original precision, resolves retained ownership and allocates new occurrences before the writer loads classifications. It also recovers instruction part numbers from their actual task hashes. It does not re-key existing task references or copy a verdict to every formerly colliding message. A historical instruction part can exist before its task row or timestamp was materialized. Reconciliation uses that part's recorded source ordinal and requires any recorded task timestamp to agree; absent timestamps do not erase the part's ownership. The original-source and unique-owner checks still refuse missing or conflicting occurrences. Both real identity journeys require a genuine retained part without a recorded task timestamp: the pre-ingestion journey covers an absent source projection, and the historical-collision journey covers an existing projection. They verify unchanged identities and annotations after reconciliation rather than synthesizing missing metadata. Both journeys also require an actual surviving instruction whose recorded part number exceeds the session's remaining addition count. Earlier withdrawals must not make that part disappear from reconciliation: candidate discovery includes the recorded numbers and still verifies each exact parent/part hash. oko database check, native Database Check and installation admission require oko_reconcile_message_origins(uuid,uuid,text,jsonb,bigint) with its declared argument names and permission for the current database role to execute it. They inspect the function catalogue without reconciling any message. Existing allocation columns alone are not a compatible writer contract: an absent, renamed or non-executable reconciliation RPC refuses with the required canonical migration before admission can report the database complete.

A read of a table this build declares in database/schema.json that the database answers with HTTP 404 refuses with the canonical migration that adds the table and oko database check as the confirmation to run after releasing it. Any other failed read keeps the database's own cause and names no migration: a request that got no HTTP answer (an unresolved host, a refused connection), a refused bearer or a server error is not repaired by releasing schema, so the refusal says what actually failed.

oko_message_identity_origins exposes canonical timestamp allocations and unreconciled projections. oko_unallocated_message_references exposes retained readings, additions and source-backed tasks that are not yet bound. Read-only consumers refuse these witnesses rather than silently inventing replacements. Their error identifies the session and message or reference requiring its original source. A body that the retained rows and the original source hold equally often in one source second pairs in order (oko_paired_message_sources): the n-th retained row by number takes the n-th original occurrence. Missing occurrences, conflicting ownership and a body whose retained and original counts in one second differ refuse atomically; an older partial capture cannot erase an allocation. The stored task-map reader uses the same typed message fields as the live identity consumer. Malformed timestamps, ordinals or message text refuse with the decoding cause instead of becoming an untimed or empty message. A returned session outside the requested map scope also refuses. CLI tasks map and the native map use this same reader and preserve its diagnostic. Kept readings (oko_message_readings) are decoded the same way by every reader of them — one session's ledger, the selected or whole map, and the readings of one kind. A kind this build does not know, a missing model or message id, or a read_at that is not a time refuses with message reading to (read message classifications of session <key>, … of all sessions, read messages classified as <kind>`), instead of the reading vanishing or a malformed time losing the newest-wins order between members.

Installations must qualify this cutover against an isolated real historical source with retained tasks, parts and receipts, including replay, append and refusal paths. A migration file, schema check or unchanged task count is not evidence that those references survived. The historical-collision journey also invokes read-only tasks derive and stored tasks map --session <session-id> after reconciliation and after appending another equal-text occurrence in the same second. It compares each consumer's returned root and instruction-part identities with the fixture's retained ownership, not just the stored allocation rows. A reader that reconstructs a different timestamp identity therefore fails qualification even when the database migration preserved its rows.

The readings-identity test target also includes allocation_identity_updates_refuse_and_native_corrections_preserve_the_binding. Supply OKO_TEST_IDENTITY_IMMUTABILITY as a JSON array naming one dedicated native, timestamped and untimed allocation. Each entry carries session, source_key and original_source; the native entry also carries corrected_native_source, a genuine capture with changed body text under the same native turn ID. The journey uses oko database request to attempt every protected-field update and checks the concrete refusal and unchanged stored bindings. It then uses normal tasks derive --write to apply and restore the native correction while retaining the task identity. Request bodies, real source captures/digests, commands and persisted-state readbacks remain in the existing .build/readings/identity/ evidence directory. Use only dedicated fixture work, never the operator's active tasks. Each command's evidence is written before opening its retained request body or executing the real interface. An unreadable body records its path and OS cause; an execution failure records its operation and OS cause without inventing an exit status. Completed commands retain the actual exit/termination status and stdout/stderr. A failed setup remains failed qualification, not a missing command silently omitted from the report. The native-correction journey attempts restoration even when executing the correction or reading its persisted result fails. It verifies the restored allocation against the original rows before propagating the failed observation; an unreadable result cannot silently leave corrected fixture state behind.

Backend delivery inputs#

The schema repository also owns the deployed checkout, seat-sync, push, invitation and chat functions. Deployment scripts require SUPABASE_PROJECT_REF; the workflow reads the repository variable of that name. The selected project is not inferred from a compiled-in address.

The operator declares BILLING_MINIMUM_SEATS and BILLING_LIFETIME_QUANTITY as positive integers. The workflow publishes those repository variables through set-billing-policy.sh. Missing or invalid declarations refuse before checkout or seat updates; an invalid value reports <NAME> must declare a positive integer billing quantity. Stripe price IDs determine prices. Monthly checkout and reconciliation apply the declared minimum to the measured organization membership count; lifetime checkout uses the declared quantity. The internal seat response exposes members, minimum_seats and the resulting seats.

These functions serve the same application checkout and membership flows. Configuration is not evidence that Stripe accepted a change: inspect the stored entitlement and the corresponding subscription quantity.

Notification and invitation diagnostics#

The push function measures the full serialized APNs payload against Apple's 4096-byte regular notification limit. It keeps fitting messages whole and shortens only messages that exceed that limit, at complete grapheme boundaries. JSON escaping and the alert envelope count toward the limit. Replies expose payload_bytes and body_truncated; failures preserve the provider's complete HTTP body or the network cause. sent means provider acceptance, not confirmed device display.

Invitation requests and integration responses are read fully as strict UTF-8. The authoritative database RPC validates the normalized email address, including its current 320-character maximum. The Edge Function does not maintain a different JavaScript character-count limit. Authentication, role, selected organization and exact request-field checks still apply. An integration refusal persists its full response or decoding cause as the invitation's delivery error. A provider message ID is required for a successful delivery result; this does not prove the email reached an inbox.

Login email deployment verifies the saved template and subject values by reading the Management API after the write, not by checking a minimum length. brand-login-email.sh takes SUPABASE_PROJECT_REF, SUPABASE_ACCESS_TOKEN and RESEND_SMTP_PASSWORD; optional LOGIN_TEMPLATE_FILE selects the source HTML. Without that override, it uses the repository's canonical login template.

Real qualification#

The schema repository's tests/backend/run.ts provides run --fixture PATH, status --run-dir PATH and cleanup --run-dir PATH. Run it with Deno's read, write, environment, network and git,supabase,bash subprocess permissions, with DENO_DIR under the checkout's ignored .build/ directory. The repository README defines the fixture fields and complete commands.

A run deploys committed source to a declared dedicated Supabase project and uses real Stripe sandbox, APNs and invitation dependencies. It exercises checkout quantities, subscription seat changes, long Unicode push messages, invitation persistence and resend, short login templates and meaningful refusals. It retains revision and input hashes, commands, exit statuses, full provider responses and final state. Cleanup deletes only owned rows, accounts and sandbox billing objects and confirms their absence; failed cleanup cannot produce a pass. Deployed functions and declared settings remain in the dedicated test project.

These backend checks do not claim browser, device-display or recipient-inbox verification. Source changes and test definitions are not an executed result.