File size: 15,501 Bytes
c14ceee c98e1b1 79cd247 c14ceee ce4ad7b c14ceee 5a07a58 c14ceee d598032 c14ceee 79cd247 c14ceee 79cd247 c14ceee | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 | -- 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;
|