platform/harness/pg/schema.sql

-- 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
-- "-- Optional:" marker; the tail below it is psql-variable syntax). 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.
--
-- ⚠ 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);

-- ─────────────────────────────────────────────────────────────────────────────────────────────
-- 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);

    -- 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;
$$;

-- Tenant #0, always. Its slug is the one the session cookie already carries.
INSERT INTO control.tenants (slug, name)
VALUES ('royal-imports', 'Royal Imports')
ON CONFLICT (slug) DO NOTHING;
SELECT control.provision_tenant_schema('royal-imports');

-- Optional: `psql -v slug=<a-slug>` provisions one more tenant schema in the same transaction.
-- `:'slug'` is unset in the plain invocation, so this block is skipped there.
\if :{?slug}
INSERT INTO control.tenants (slug, name) VALUES (:'slug', :'slug')
ON CONFLICT (slug) DO NOTHING;
SELECT control.provision_tenant_schema(:'slug');
\endif

COMMIT;