File size: 15,501 Bytes
149aa87
 
 
 
 
 
 
 
 
 
 
8510233
 
 
 
 
 
 
 
 
 
 
 
 
149aa87
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
f530297
 
 
 
 
 
149aa87
4a80e55
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
149aa87
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
07a68d0
 
 
 
 
 
 
 
 
 
 
 
149aa87
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
8510233
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
149aa87
 
 
 
8510233
 
 
 
149aa87
 
 
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;