Download platform/harness/pg/schema.sql from fsanyoto/loopable: direct link, hf CLI and curl.
- Browser
- Download file 15.5 kB
-
https://huggingface.co/spaces/fsanyoto/loopable/resolve/9291c0100f4e7b8da2efcbbf1ebdea72ee1663e7/platform/harness/pg/schema.sql
- Command line
-
hf download hf://spaces/fsanyoto/loopable@9291c0100f4e7b8da2efcbbf1ebdea72ee1663e7/platform/harness/pg/schema.sql
-
curl -L -o schema.sql https://huggingface.co/spaces/fsanyoto/loopable/resolve/9291c0100f4e7b8da2efcbbf1ebdea72ee1663e7/platform/harness/pg/schema.sql
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; | |