loopable / platform /harness /pg /schema.sql
fsanyoto's picture
Deploy AIOS web (React glide grid + FastAPI slice)
d598032 verified
Raw History Blame Contribute Delete
15.5 kB
-- 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 `<table_key>/<view_id>`. 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_<slug>` 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=<a-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;