-- harness/pg/schema.sql — the control DB + per-tenant schema (X4 / EXIT-2b, 2026-07-30). -- -- WHY POSTGRES AT ALL (C1c, and it is not a preference). The HF Dataset store is a FILE HOST: -- no transactions, no PITR, no row-level anything, and `store.get()` is cache-first — so a write -- from another process is invisible to a running app until restart. That was observed LIVE in -- wave 11, and it is the whole reason the store leaves the product. Application/control data -- (users, sessions, tenants, audit, saved views, overlay fields, cohorts, folders, prefs, -- billing mirror) is transactional and needs joins; the ANALYTICAL mirror stays in DuckDB, -- one file per tenant, because file-per-tenant IS the isolation model there. -- -- ✅ RUN AGAINST A REAL SERVER 2026-08-04 (W19): applied end-to-end by verify_store_pg's -- integration half against the owner's Neon (psycopg executes everything ABOVE the psql-only -- TAIL SENTINEL near the bottom of this file; the tail beneath it is psql-variable syntax that -- no driver can run). Tenant provisioning, jsonb/bytea round-trips and 80 concurrent FOR-UPDATE -- writes all proven. The CUTOVER remains parked behind C1e's triggers; `STORE_BACKEND` still -- defaults to hf. -- -- ⛔ D-461 — THIS PARAGRAPH QUOTES THE SENTINEL ("-- Optional:"), AND THAT ONCE COST US THE -- SCHEMA. The seeder split on that marker as a bare SUBSTRING, matched the quotation here -- instead of the real one 160 lines below, and applied 916 of these ~10,000 characters: -- comments, zero executable SQL, no provisioning function. The splitter is now LINE-ANCHORED -- and asserts what it hands over, so the quotation above is deliberate and stays: it is the -- standing proof, in the shipping file, that a prose mention no longer truncates anything. -- What no line may do is BEGIN with the marker. Only the sentinel does. -- -- ⚠ CO-LOCATE (C1b). An Ashburn app with a European database adds ~90ms to every query and undoes -- the reason the market pivot happened. -- -- psql "$DATABASE_URL" -v ON_ERROR_STOP=1 -f harness/pg/schema.sql -- psql "$DATABASE_URL" -v ON_ERROR_STOP=1 -v slug=royal_imports -f harness/pg/schema.sql -- (the second form additionally provisions one tenant schema — see the bottom block) BEGIN; -- ───────────────────────────────────────────────────────────────────────────────────────────── -- CONTROL: what the platform knows about itself. One row per tenant, one per account. -- ───────────────────────────────────────────────────────────────────────────────────────────── CREATE SCHEMA IF NOT EXISTS control; CREATE TABLE IF NOT EXISTS control.tenants ( -- The SLUG is the identity everywhere: the session cookie's `t` claim, the store namespace, -- the DuckDB filename and the schema name are all derived from it. Constrained to what is -- safe in all four positions, which is stricter than Postgres alone would require. slug text PRIMARY KEY CHECK (slug ~ '^[a-z0-9][a-z0-9-]{0,58}[a-z0-9]$'), name text NOT NULL, -- `harness/tenants.py`'s literal, as data. Its own docstring says this becomes "a tenants -- store (DB / HF dataset)"; this is that store. jsonb rather than columns because the shape -- is per-connector and adding a source must not be a migration. config jsonb NOT NULL DEFAULT '{}'::jsonb, -- Connector CREDENTIALS never live here in plaintext. The Odoo key is the customer's, -- stored encrypted at rest by the secrets layer (C2: SOPS+age per tenant) and shown once. -- This column holds only a REFERENCE to where the secret lives. secret_ref text, active boolean NOT NULL DEFAULT true, created_at timestamptz NOT NULL DEFAULT now(), updated_at timestamptz NOT NULL DEFAULT now() ); CREATE TABLE IF NOT EXISTS control.users ( -- Mirrors `core/users.py`'s record exactly, so the HF→PG cutover (C-4) is a copy, not a -- redesign: salt + PBKDF2-HMAC-SHA256 hash (200k), never a plaintext password. tenant_slug text NOT NULL REFERENCES control.tenants(slug) ON DELETE CASCADE, username text NOT NULL CHECK (username = lower(username)), salt text NOT NULL, hash text NOT NULL, name text NOT NULL DEFAULT '', role text NOT NULL DEFAULT 'user', -- 'all' or a JSON array of Odoo team ids ([5]=Fisch, [6]=Royal) — the BU isolation input. bus jsonb NOT NULL DEFAULT '"all"'::jsonb, -- 'all' or a JSON array of registry keys — the module grant `may_open` reads. modules jsonb NOT NULL DEFAULT '"all"'::jsonb, agent text, -- own-book scope (res.partner.agent_ids name) email text, active boolean NOT NULL DEFAULT true, -- X3's stateless-session revocation handle. A signed cookie cannot be deleted server-side, -- so it carries the epoch it was minted under and every verification re-reads THIS number; -- a password change or a deactivation bumps it and every outstanding cookie dies at once. epoch integer NOT NULL DEFAULT 0, created_at timestamptz NOT NULL DEFAULT now(), updated_at timestamptz NOT NULL DEFAULT now(), PRIMARY KEY (tenant_slug, username) ); -- D2's SESSIONS MIRROR — an AUDIT view, deliberately NOT the source of truth (X3 keeps sessions -- stateless so the API process holds no per-tenant state). It answers "who is signed in, from -- where, since when", and it is what makes per-DEVICE revocation possible later. Arrives with -- C-2; the table is defined now so the cutover does not need a second migration. CREATE TABLE IF NOT EXISTS control.sessions ( id bigserial PRIMARY KEY, tenant_slug text NOT NULL, username text NOT NULL, epoch integer NOT NULL, issued_at timestamptz NOT NULL DEFAULT now(), last_seen_at timestamptz NOT NULL DEFAULT now(), absolute_expiry timestamptz NOT NULL, -- Truncated/derived client facts only. A full UA string plus an exact IP is more personal -- data than an audit trail needs, and the DPA is easier to keep when the row is smaller. ip_prefix text, user_agent text, revoked_at timestamptz, FOREIGN KEY (tenant_slug, username) REFERENCES control.users(tenant_slug, username) ON DELETE CASCADE ); CREATE INDEX IF NOT EXISTS sessions_live_idx ON control.sessions (tenant_slug, username) WHERE revoked_at IS NULL; CREATE TABLE IF NOT EXISTS control.audit ( id bigserial PRIMARY KEY, at timestamptz NOT NULL DEFAULT now(), tenant_slug text, username text, action text NOT NULL, -- 'login' | 'store.put' | 'user.deactivate' | … target text, detail jsonb NOT NULL DEFAULT '{}'::jsonb ); CREATE INDEX IF NOT EXISTS audit_at_idx ON control.audit (at DESC); CREATE INDEX IF NOT EXISTS audit_tenant_idx ON control.audit (tenant_slug, at DESC); -- W50-T25 (ruling R9 / contract C3): the saved-View trail is read PER DATABASE, and its target -- is `/`. The two indexes above answer "what happened, newest first" and -- "what happened to this tenant"; neither answers "what happened to THIS database's views", -- which is the only question the Administrator trail view ever asks. NO NEW TABLE: control.audit -- already existed with the right shape and no writer, so this ticket gave it one. CREATE INDEX IF NOT EXISTS audit_target_idx ON control.audit (target text_pattern_ops, at DESC); -- Durable idempotency and audit for the scale-to-zero automation worker. EventBridge and Step -- Functions are at-least-once transports; this row is what makes a retried scheduled minute one -- business run instead of two. No scheduler heartbeat is stored here -- rows exist only when -- actual work was dispatched. CREATE TABLE IF NOT EXISTS control.automation_jobs ( job_id text PRIMARY KEY, tenant_slug text NOT NULL REFERENCES control.tenants(slug) ON DELETE CASCADE, automation_id text NOT NULL, trigger_kind text NOT NULL, generation text NOT NULL DEFAULT '', scheduled_for timestamptz, status text NOT NULL CHECK (status IN ('running', 'completed', 'skipped', 'failed')), attempts integer NOT NULL DEFAULT 0, created_at timestamptz NOT NULL DEFAULT now(), started_at timestamptz, finished_at timestamptz, detail jsonb NOT NULL DEFAULT '{}'::jsonb ); CREATE INDEX IF NOT EXISTS automation_jobs_tenant_at_idx ON control.automation_jobs (tenant_slug, created_at DESC); CREATE INDEX IF NOT EXISTS automation_jobs_open_idx ON control.automation_jobs (status, created_at) WHERE status IN ('running', 'failed'); -- ───────────────────────────────────────────────────────────────────────────────────────────── -- PER-TENANT: one schema `t_` per tenant, holding what the HF store holds today. -- -- A SCHEMA PER TENANT, not a tenant_id column with RLS. Both are defensible; this one is chosen -- because the isolation is then structural — a query that forgets its tenant predicate cannot -- silently return another tenant's rows, it names a table that is not in the search_path. RLS -- makes the same guarantee only while every policy is correct on every table, forever, and -- [[aios-permissioning]] already records that a forgotten predicate is the failure mode we keep -- writing gates against. The cost is DDL per tenant, which is one function call below. -- ───────────────────────────────────────────────────────────────────────────────────────────── CREATE OR REPLACE FUNCTION control.provision_tenant_schema(p_slug text) RETURNS text LANGUAGE plpgsql AS $$ DECLARE -- ⚠ `-` IS NOT LEGAL UNQUOTED, and the slug carries them (`royal-imports`). Normalising to -- `_` here rather than quoting everywhere keeps every generated identifier plain, and -- `format('%I')` is what makes the interpolation injection-safe regardless. v_schema text := 't_' || replace(lower(p_slug), '-', '_'); BEGIN IF p_slug IS NULL OR p_slug !~ '^[a-z0-9][a-z0-9-]{0,58}[a-z0-9]$' THEN RAISE EXCEPTION 'refusing to provision an unsafe tenant slug: %', p_slug; END IF; EXECUTE format('CREATE SCHEMA IF NOT EXISTS %I', v_schema); -- store_kv — the JSON-per-key store `core/store.py` exposes, one row per key. `rev` is what -- the file host never had: an optimistic-concurrency counter, so a read-modify-write can -- detect that somebody else wrote between its read and its write instead of overwriting -- them. That is the concrete thing "no transactions" cost us. EXECUTE format($f$ CREATE TABLE IF NOT EXISTS %I.store_kv ( key text PRIMARY KEY, value jsonb NOT NULL, updated_at timestamptz NOT NULL DEFAULT now(), rev bigint NOT NULL DEFAULT 1 )$f$, v_schema); EXECUTE format($f$ CREATE TABLE IF NOT EXISTS %I.user_table_defs ( table_key text PRIMARY KEY, value jsonb NOT NULL, rev bigint NOT NULL DEFAULT 1, updated_at timestamptz NOT NULL DEFAULT now() )$f$, v_schema); EXECUTE format($f$ CREATE TABLE IF NOT EXISTS %I.user_table_records ( table_key text NOT NULL, row_id text NOT NULL, value jsonb, meta jsonb, rev bigint NOT NULL DEFAULT 1, updated_at timestamptz NOT NULL DEFAULT now(), PRIMARY KEY (table_key, row_id) )$f$, v_schema); -- store_blobs — `upload_bytes`/`download_bytes`/`delete_path`. Customer documents live here -- rather than base64'd into a JSON value, for the reason `core.store.upload_bytes` gives: -- the workspace blob is READ ON EVERY RENDER, so a few MB of base64 inside it would be paid -- on every keystroke by every user of that table. EXECUTE format($f$ CREATE TABLE IF NOT EXISTS %I.store_blobs ( path text PRIMARY KEY, bytes bytea NOT NULL, message text, updated_at timestamptz NOT NULL DEFAULT now() )$f$, v_schema); EXECUTE format('CREATE INDEX IF NOT EXISTS %I ON %I.store_kv (updated_at DESC)', v_schema || '_kv_updated_idx', v_schema); RETURN v_schema; END; $$; -- ═══════════════════════════════════════════════════════════════════════════════════════════ -- ⛔⛔ THE NEXT LINE IS THE TAIL SENTINEL. IT IS AN INTERFACE, NOT A COMMENT. -- -- Every driver-based reader of this file (`ops/seed_pg_from_hf.py`, and verify_store_pg's -- integration half through the same helper) truncates HERE, because everything below is psql -- meta-syntax that psycopg cannot execute. The contract is exact: the reader cuts at the first -- line that BEGINS, at column 0, with "-- Optional:". -- -- ⛔ SO: never start a line in this file with that marker except the one below, and never move -- the sentinel above the provisioning function. Discussing the marker mid-line is fine and this -- comment does it deliberately, because D-461 was a bare substring split matching a quotation -- 160 lines above the real thing. The seeder now asserts the surviving body still creates that -- function and ABORTS otherwise, but an abort is a cutover that did not run. -- ═══════════════════════════════════════════════════════════════════════════════════════════ -- Optional: `psql -v slug=` provisions that tenant schema in the same transaction. -- With no `slug`, plain psql retains the default tenant #0 provisioning below. Driver users stop -- before this psql-only tail: the seeder provisions only the explicitly guarded tenant rows. \if :{?slug} INSERT INTO control.tenants (slug, name) VALUES (:'slug', :'slug') ON CONFLICT (slug) DO NOTHING; SELECT control.provision_tenant_schema(:'slug'); \else INSERT INTO control.tenants (slug, name) VALUES ('royal-imports', 'Royal Imports') ON CONFLICT (slug) DO NOTHING; SELECT control.provision_tenant_schema('royal-imports'); \endif COMMIT;