RESEARCH -- Per-client Supabase/infra topology + platform-vs-client ownership boundary (ID-95.1)

RESEARCH — Per-client Supabase/infra topology + platform-vs-client ownership boundary (ID-95.1)

Task: ID-95 — Per-client Supabase/infra topology + platform-vs-client ownership boundary. Subtask: {95.1} RESEARCH. Author date: 09/06/2026. Status: DECISION-SUPPORT. This consolidates four read-only research lanes (R1 in-repo multi-tenancy inventory, R2 separate-backend-repo question, R3 per-client topology options, R4 first-client-deploy gap analysis) into one cited decision brief. ID-45 (cutover) and ID-95 (this Task) both lacked a spec dir — this is the consolidation they were missing. Round 2 (09/06/2026) — ADDED: sections 8 (Coolify middle-ground / BYOC on-prem option), 9 (canonical-content propagation to isolated client DBs — OQ-95-3 resolution), and 10 (BYOC reference model), plus round-2 resolution addenda on OQ-95-1, OQ-95-3, and the section 5 recommendation. Round 2 answers the owner’s “art of the possible” follow-up on (1) a Coolify-hosted middle ground for client databases and (2) how comparable platforms handle per-client DBs plus propagation of platform-owned canonical knowledge. Sections 1-7 are round-1 content, preserved intact.

This is the {95.1} artefact. {95.2} PRODUCT, {95.3} TECH, {95.4} PLAN are separate fresh-Planner instances (Q-PLANNER-2) if the Orchestrator elects to upgrade. Every load-bearing in-repo claim is cited as file:line; every external Supabase-behaviour claim carries a doc URL (preserved from the research lanes). Open questions for Liam are collected in section 6 as OQ-95-N.

TL;DR

Near-term (first client, safeguarding/education, UK): managed Supabase project-per-client in eu-west-2 (London), Team plan org-wide, PITR add-on given the data class. One canonical migration set -> fan-out db push per project. Platform keeps a separate managed project in the same org as its own dev/demo instance — never co-tenant with client data.
Long-term: hybrid — managed project-per-client by default; self-hosted Supabase inside client infrastructure for any client whose contract mandates client-tenancy hosting. One canonical migration set drives both; only the transport differs (Management API vs Docker/IaC).
Separate-backend-repo verdict: DO NOT split platform migrations into a private repo. The split does not deliver privacy (git-history purge does, and that pass is happening anyway for ID-68), and supabase gen types reads a LIVE DB, not migration files — so publishing migrations exposes only generic DDL already embedded in database.types.ts. Defer any private repo to a per-client provisioning repo under ID-95, only when real per-client artefacts emerge.
No-cross-client-contamination constraint: met only by physical isolation (project-per-client). Shared-instance + RLS (today’s shape) FAILS the constraint and is acceptable only for the platform’s own dev/demo instance.
Cross-cutting design principle (the template): per-client values live as DATA, not schema. The public.signup_policy config table that landed this session is the canonical worked example of this pattern.

1. Question and constraints

ID-95 must decide the topology for standing up clients on Knowledge Hub without compromising the platform’s ownership of its canonical schema or letting any client’s data touch any other client’s. Restated from the owner’s framing (R4 TARGET):

Platform owns the canonical schema. One migration set, platform-controlled, defines the structure for every deployment. We own it, manage it, improve it.
Per-client DB isolation. Each client’s content lives in an isolated database; ideally under client data sovereignty (residency / tenancy as the contract demands).
No cross-client contamination. The first client is safeguarding/education — a regulated domain, very likely contractual — so a logical-isolation-only design (one mis-scoped RLS policy leaks across clients) is unacceptable.
Platform needs its own dev/demo instance. A neutral, platform-branded deployment we can develop and demo against, never co-tenant with real client data.
First client must not preclude multi-tenancy. V1 is single-tenant (Phew), but the first-client stand-up must not foreclose the multi-client topology that follows (R4: “ID-95 multi-tenancy is post-first-client but first client must be stood up not-precluding it”).

Decision shape: the single biggest fork is managed vs self-hosted Supabase for the first client given residency (OQ-95-1). That choice cascades into provisioning automation, ops burden, cost model, and the AGPL-conveyance question on the pipeline side.

2. Current state (what is already in place)

2.1 Application / Vercel (per-client deploy is single-knob, not multi-tenant)

Branding overlay codegen. scripts/generate-client-branding-map.ts:1-107 globs lib/branding/clients/*.json -> lib/branding/client-branding-map.generated.ts, falling back to default. Current map: default + phew. Tracked: lib/branding/clients/default.json, lib/branding/clients/phew.json, public/clients/phew/* assets.
Client-ID flow. lib/env-client.ts:40-73 — NEXT_PUBLIC_CLIENT_ID is REQUIRED (missing -> S196 branding-fallback corruption). lib/client-config.ts:584-630 loadBranding() resolves id -> map -> default; BRANDING is computed once at module init. Baked at build via SWC (vercel.json:3 build:vercel; package.json prebuild runs generate:branding before next build).
Deploy substrate. App = Vercel; ingestion pipeline = Coolify/IONOS. Per-client deploy is currently a single-knob runbook (client-app-deploy.md:36-47: NEXT_PUBLIC_CLIENT_ID, branding assets, sign-up domain, auth-hook, custom domain, Supabase keys + APP_URL).
Branding-overlay AUTOMATION is NOT built — it is the outstanding half of {68.22}. The public repo still tracks phew.json / public/clients/phew/*; the untrack is a HARD SEQUENCE — only after the overlay is wired (client-app-deploy.md:94-99).
Prod is not live. knowledge-hub Vercel project -> kh.phew.org.uk is the intended first-client target. A neutral platform dev/demo deploy + platform brand is currently UNTRACKED.

2.2 Supabase (single prod + staging pair, config-driven hooks)

Config topology. supabase/config.toml:65-80 declares [remotes.staging] project_id = turayklvaunphgbgscat and [remotes.staging.db.seed] sql_paths = ["./seed.sql"]. The auth hook is wired via [remotes.staging.auth.hook.before_user_created] -> pg-functions://postgres/public/hook_restrict_signup_to_allowed_domain. supabase/.temp/project-ref = turayklvaunphgbgscat. The prod ref (rovrymhhffssilaftdwd) appears only in comments — there is no [remotes.prod] block. The pattern is extensible: add [remotes.<client>].
Seed boundary (canonical-only by contract). supabase/seed.sql:9-35 — the contract states schema-only data true across ALL deployments; per-client data (guides, taxonomy, Q&A, profiles) lives elsewhere (runbooks/staging-refresh.md “Per-client seeding”). Every INSERT is ON CONFLICT ... DO NOTHING. No seed.<client>.sql pattern exists.
Two databases, migration-complete + parity-verified. One prod project (rovrymhhffssilaftdwd) plus a persistent staging branch (turayklvaunphgbgscat).

R4’s lane described the sign-up restriction as GUC-based and “broken on managed Supabase with no replacement in code”. That is now stale. This session (commit a135b1bba, “feat(ID-68.21): config-table signup-domain policy (supersedes GUC)”) landed the fix:

The GUC approach (current_setting('app.allowed_signup_domain')) is dead on managed Supabase: setting it needs ALTER DATABASE postgres SET app.allowed_signup_domain = '...', which fails with ERROR: 42501: permission denied to set parameter (no superuser in the managed SQL editor). Cited verbatim in migration 20260609160000_config_table_signup_domain_policy.sql header.
Replacement (LANDED): migration supabase/migrations/20260609160000_config_table_signup_domain_policy.sql creates a single-row public.signup_policy config table (id boolean PRIMARY KEY DEFAULT true, allowed_domain text, singleton CHECK), enables RLS deny-all, grants SELECT to supabase_auth_admin only with a permissive SELECT policy (the documented Supabase auth-hook table-read pattern), and CREATE OR REPLACEs the hook to read the table — keeping the exact function name/signature and fail-closed semantics. No client domain literal appears in the migration.
Per-environment data step (out-of-band, never committed): INSERT INTO public.signup_policy (allowed_domain) VALUES ('<domain>') ON CONFLICT (id) DO UPDATE SET allowed_domain = EXCLUDED.allowed_domain;
RESIDUAL: the client-app-deploy.md runbook step 3c still prescribes the old ALTER DATABASE ... SET app.allowed_signup_domain GUC command. It must be updated to the INSERT INTO public.signup_policy ... data step above. (Code fix landed; runbook lags.)

This is the template. signup_policy is the canonical worked example of the cross-cutting design principle (section 3.5): platform-owned schema, per-client values as DATA set out-of-band. Every future per-client knob (branding id, flags, display name, allowed domain) follows this pattern — config ROWS / .env / auth settings, never bespoke columns or per-client tables.

2.4 Pipeline / Coolify (IONOS) — ID-66 B1 DONE

ID-66 B1 is complete: cocoindex + pullmd + Playwright + Trafilatura co-located on one IONOS VPS (77.68.122.71, 8 vCPU / 16 GB / 480 GB), Coolify 4.1.1. ARMED: LMDB -> S3 cold-snapshot backup (cron 30 3 * * *, bucket onprem-backups); datapath monitor; /walk scheduled task created-but-DISABLED both envs; COCOINDEX_SOURCE_PATH = /cocoindex-state/corpus. Boot is burn-safe (walk only on bearer-gated POST /walk, ID-83).
HTTPS ingress is live under TEMPORARY platform domains (kh-pipeline-staging.aisolutionhub.co.uk / kh-pipeline.aisolutionhub.co.uk), Traefik path-scoped /walk + /health (onprem-b1-deploy.md:308-345). Hostnames are revisited per client at brand decision.
onprem-b1-deploy.md supersedes the old cocoindex-deploy.md Cloud Run runbook (Cloud Run manifests kept for reversibility, ID-66 inv 24).

2.5 Filesystem / corpus

Client fileserver: local-fs (dev) -> /cocoindex-state/corpus mount (prod). The corpus is never in git. Root manifest .kh-workspace-map.json is currently MANDATORY — missing/unparseable aborts the whole flow (ID-69 RESEARCH:232-238, flow.py:1701-1725). The canonical-corpus folder convention is an OPEN ID-69 gap (curation discipline, allowlist/manifest, layout does not encode taxonomy; ID-69 RESEARCH section 7:292-305).

2.6 Readiness verdict

Per-client-READY today: branding JSON/codegen/client-id-bake/signup-hook (now config-table driven) are all generic; the pipeline image is client-agnostic; seed.sql is canonical-only by design. Overall readiness ~60-70 percent. Remaining gaps: add [remotes.<client>] blocks; per-client seed-data pipeline (no seed.<client>.sql); per-client Coolify app projects; the app multi-tenant isolation decision (this Task).

3. Topology options

Foundational fact: each Supabase project is a dedicated Postgres instance on its own server (https://supabase.com/docs/guides/platform/manage-your-usage/compute ; https://supabase.com/docs/guides/platform/manage-your-usage/compute-and-disk). Therefore project-per-client = physical isolation; one-project-many-tenants = logical isolation only.

3.1 Option A — Managed project-per-client (one org, N projects)

This is Supabase “for Platforms”. Provisioning is via the Management API: a PAT for projects in our own org, OAuth2 for client-owned projects (https://supabase.com/docs/reference/api/introduction ; https://supabase.com/docs/guides/integrations/supabase-for-platforms ; https://supabase.com/docs/guides/integrations/build-a-supabase-oauth-integration). POST /v1/projects {organization_id, name, region, db_pass, compute} (https://supabase.com/docs/reference/api/v1-create-a-project). Rate limit ~120 req/min per-user/per-scope.

Schema delivery: one canonical migration set -> N projects via supabase link + db push per project (CI fan-out over a registry of refs). The .temp/project-ref-relink-before-push gotcha becomes operationally CRITICAL at N (a stale link silently lands a push on the wrong project).
Region is FIXED AT CREATION, cannot be changed (create-new-and-migrate is the only path) — so residency must be baked into provisioning as a contract input (https://supabase.com/docs/guides/troubleshooting/change-project-region-eWJo5Z ; https://supabase.com/docs/guides/platform/migrating-within-supabase). UK = eu-west-2 London (https://supabase.com/docs/guides/platform/regions).
Cost scales by COMPUTE, not flat per-project (https://supabase.com/pricing): Pro $25/mo/org includes ~$10 compute credit (~one Micro); each extra project adds ~Micro compute. Team $599/mo/org adds SOC2 + ISO27001 + 14-day backups + audit logs (HIPAA add-on). PITR $100/mo/project (7-day).
Isolation: strong (separate instances). Sovereignty: partial — data sits in AWS regions Supabase controls (UK via eu-west-2), adequate for most residency clauses, but NOT in the client’s own tenancy — insufficient if a contract demands client-tenancy hosting. Ops: lowest (managed backups, PITR, monitoring, Management API all available).

3.2 Option B — Self-hosted Supabase in CLIENT infrastructure

Docker Compose stack: Studio, Kong, Auth (GoTrue), PostgREST, Realtime, Storage, imgproxy, postgres-meta, Postgres, Edge Runtime, Supavisor (+ optional Logflare/Vector) (https://supabase.com/docs/guides/self-hosting/docker ; https://supabase.com/docs/guides/self-hosting). Licences are all permissive (Apache-2.0 / MIT / PostgreSQL) — NO AGPL in the core stack (https://supabase.com/docs/guides/getting-started/architecture).

“We manage, you host”: the client owns server/OS/Postgres-maintenance/HA/backups/DR/ monitoring; the platform owns schema delivery (init via /docker-entrypoint-initdb.d; ongoing via supabase db push against the client’s connection string — the same canonical set). Config is all .env-driven; upgrades are MANUAL (bump image tag, run.sh pull/recreate).
KEY LIMITATION: self-hosted CANNOT use branching, managed backups/PITR, advanced metrics, analytics buckets, ETL, or the Management API; Studio has no multi-org/project. So provisioning automation must be self-built (Docker/Terraform/Ansible — KH already runs IONOS + Coolify).
Isolation: maximal. Sovereignty: maximal — data never leaves client infra; the ONLY option meeting client-tenancy clauses. Ops: highest (all DR/HA/upgrade burden on us); licence cost lowest.

3.3 Option C — Shared instance + RLS (today’s shape)

FAILS the no-cross-client-contamination requirement. Logical isolation only: one mis-scoped RLS policy / a SECURITY DEFINER function missing search_path / a service-role query without a tenant filter leaks across clients. Acceptable ONLY for internal / demos / pre-contract pilots — a good fit for the platform’s OWN dev/demo instance, not for regulated client data. (Listed for completeness; it is the constraint-violating baseline.)

3.4 Option D — Hybrid

Managed-per-client (A) as the default; self-hosted-in-client-infra (B) for regulated/sovereignty clients. ONE canonical migration set drives both — only the transport differs (Management API vs Docker/IaC). This is the long-term shape.

3.5 Cross-cutting: per-client config as DATA, not schema

Per-client values (allowed sign-up domain, branding, feature flags, display name) MUST be config rows / .env / auth settings — never bespoke columns or per-client tables. The migration set must be client-agnostic: no client name in any migration; per-client values seeded post-migration. KH is already doing this — the signup_policy config table (section 2.3) is the live worked example. This principle is what keeps Options A/B/D viable off a single canonical schema.

3.6 Options summary

Option	Isolation	Sovereignty	Cost	Ops burden	Verdict
A Managed-per-client	Strong (physical)	Partial (Supabase-AWS, UK region)	Compute-scaled; Team $599/org + PITR $100/proj	Lowest (managed)	Near-term pick
B Self-hosted-in-client	Maximal	Maximal (client tenancy)	Lowest licence, highest ops	Highest (all DR/HA on us)	Reserve for contract-mandated tenancy
C Shared + RLS	Logical only	None	Lowest	Low	FAILS constraint; dev/demo only
D Hybrid (A default + B regulated)	A or B per client	A or B per client	Mixed	Mixed	Long-term shape

4. Separate-backend-repo thread (FIRST-CLASS)

The owner raised this explicitly: should the platform’s Supabase migrations move into a private repo BEFORE the public flip? Recommendation: NO — stay in the public repo, genericised. Do not split migrations into a separate private repo. Optionally a lightweight hybrid (a private per-client PROVISIONING repo) only when real per-client artefacts emerge.

4.1 No official Supabase stance for a separate DB repo

All Supabase docs assume supabase/ (migrations + config.toml + seed.sql) is committed in the project repo (https://supabase.com/docs/guides/local-development/overview ; https://supabase.com/docs/guides/deployment/database-migrations ; https://supabase.com/docs/guides/deployment/managing-environments). The ecosystem co-locates migrations with dependent code; there is no widely-documented public-app + private-migrations pattern. The closest legitimate analogue is declarative-schema (SchemaHero/Skeema, one-schema-source -> many-DBs) — relevant only if KH adopts declarative schema, which it does not (it uses imperative timestamped migrations).

4.2 DECISIVE finding: types-gen reads the LIVE DB, not migration files

supabase gen types typescript reads a LIVE DB (--project-id / --linked / --db-url / --local), NOT migration files (https://supabase.com/docs/reference/cli/supabase-gen-types ; https://supabase.com/docs/guides/api/rest/generating-types). This is proven in KH CI: the supabase-types-parity job (ci.yml:1229-1236) regenerates types from live staging via --project-id, with no migrations checkout — just SUPABASE_ACCESS_TOKEN + ref.

Consequence: the public app keeps regenerating database.types.ts and keeps the parity gate green with ZERO migrations access. schema-parity.yml and supabase-advisors.yml likewise need DB access, not migration FILES. The ONLY PR-blocking job that needs migration files is migration-revoke-guard.yml (it diffs supabase/migrations/** against the base SHA).

4.3 The split does not solve privacy — history-purge does

Privacy is governed by git-history purge (git filter-repo), NOT by repo topology (https://docs.github.com/en/authentication/keeping-your-account-and-data-secure/removing-sensitive-data-from-a-repository).

Going public makes the WHOLE git history public, including all 115 existing migrations. Moving migrations private going-forward does nothing about the history already there.
KH must run filter-repo to genericise for ID-68 anyway. That same pass scrubs migrations. The residual benefit of a separate repo is ~zero — and schema DDL is low-sensitivity (it is already embedded in database.types.ts).

4.4 Ergonomics and CI: the split is net-negative

Ergonomics: the split forces a cross-repo two-PR dance versus today’s atomic migration + types + code PR (enforced by the supabase-types-parity gate). Two checkouts / link states aggravate the .temp/project-ref drift gotcha.
CI / Inv 30: KH already has a GitHub-App token bridge for the private docs-site (.github/actions/resolve-private-docs/action.yml), but it is opt-in-lanes-only — “no PR-blocking CI job may use this action (Inv 30)”. The REVOKE-guard (migration-revoke-guard.yml) is PR-blocking, so a split would either force that gate into the private repo OR breach Inv 30.

4.5 The reframe and recommendation

The natural seam is NOT public-vs-private migrations. It is:

PUBLIC generic platform schema — the canonical migration set, stays in the going-public repo.
PRIVATE per-client instantiation/config — per-client seed/config/domains/secrets, a private PROVISIONING repo under ID-95.

Same-migrations -> N-projects fan-out works from an in-repo directory just as well as from a separate repo. Recommendation: stay-in-public-genericised for platform schema/migrations; defer any split to a per-client PROVISIONING repo (private), mirroring the resolve-private-docs App-token bridge and respecting Inv 30 (opt-in lanes only), created only when real per-client artefacts exist. Decisive factors: (1) the split does not deliver privacy — history-purge does, and runs anyway; (2) types-gen reads the live DB, so publishing migrations exposes only generic DDL already in database.types.ts; (3) atomic schema + types + code PRs are an asset the split destroys; (4) the split forces the REVOKE-guard out of public or breaches Inv 30.

5. Recommendation

5.1 Near-term (first client — Phew, safeguarding/education, UK)

Option A: managed Supabase project-per-client.

Region: eu-west-2 (London). Region is immutable post-creation — bake residency into provisioning as a contract input now.
Plan: Team plan org-wide (SOC2 + ISO27001 + 14-day backups + audit logs), with the PITR add-on given the safeguarding/education data class.
Platform dev/demo: a SEPARATE managed project in the SAME org — never co-tenant with client data. This is the only legitimate place for an Option-C-style shared/RLS shape.
Why not B now: self-hosting forfeits managed backups/PITR/monitoring/Management-API and pushes all DR/HA/upgrade ops onto us for a single first client — disproportionate UNLESS the contract explicitly mandates client-tenancy hosting (then escalate that client to B). This is the OQ-95-1 fork.
Schema delivery: one canonical migration set -> supabase link + db push per project. Per-client values (allowed domain via signup_policy, branding id, flags) set out-of-band as DATA, never in migrations.

5.2 Long-term

Option D: hybrid. Managed-per-client by default; self-hosted-in-client-infra for regulated/sovereignty clients. One canonical migration set drives both. Build the provisioning fan-out (registry of refs + relink-safe db push) so adding a client is a config entry, not a code change.

5.3 Contamination + residency, addressed explicitly

No cross-client contamination: met by physical isolation (project-per-client, Option A/B/D). Shared + RLS (Option C) is rejected for client data; it survives only as the platform dev/demo instance, which holds no client data.
Residency / sovereignty: Option A in eu-west-2 satisfies UK-residency clauses (data in a UK AWS region). For a contract mandating client-TENANCY hosting (data never leaving client infra), only Option B satisfies it — escalate that specific client to B while keeping the rest on A. Bake region/tenancy as a per-client provisioning input because region cannot change after creation.

5.4 Round-2 sharpening — the third tier is BYOC, not bespoke self-host

Round 2 reframes the long-term sovereignty option. Round-1 Option B (“self-hosted Supabase in client infra”) is still correct, but its OPERABLE form is now named and de-risked: it is Coolify-hosted BYOC (section 8), the textbook control-plane/data-plane BYOC pattern (section 10), not a hand-rolled Docker/Ansible build. The recommendation therefore becomes a three-tier ladder:

Tier 1 (interim, default now): managed Supabase project-per-client (Option A). Stand up the first client this way — quickest to market.
Tier 2 (long-term default): still Tier 1 for standard clients.
Tier 3 (long-term, regulated/sovereignty clients): Coolify-hosted BYOC — one Coolify server per client, Supabase as a Service in an isolated compose network, an in-compose one-shot migration container re-run on each push->redeploy, driven by a PRIVATE per-client repo via a shared scoped GitHub App (section 8). This replaces the abstract Option B with a concrete, proven-pattern offering. It is a genuine third tier with real ops cost (own the backups, own the upgrades; section 8.4), NOT a free midpoint.

Both eras are served by ONE canonical migration set and ONE one-directional push mechanism (catalog + fan-out worker + per-client version table; sections 8.6 / 9.4). Only the TRANSPORT differs: Management-API db push for managed (Tier 1); a private-repo GitOps channel reconciled by a Coolify deploy for BYOC (Tier 3). The decision of WHEN to build Tier 3 stays with Liam, but the topology is no longer an open design risk.

6. Dependencies and open questions

6.1 Task dependencies

ID-66 (on-prem pipeline pivot): B1 is DONE (section 2.4). The B2 crux — self-host-Supabase-on-Coolify vs managed — is the same managed-vs-self-host fork as OQ-95-1, viewed from the pipeline side (ID-66 RESEARCH section 2.2-2.4, section 5; deferred OQ-66-7). ID-66’s recommended Option C Hybrid (shared infra for cost-sensitive clients, dedicated UK VPS for regulated/premium) aligns with this brief’s Option D. AGPL-pullmd conveyance (OQ-66-7) bites only if the on-prem pipeline ships into the client perimeter at handover — mitigations in order: (1) don’t-convey (client pulls the upstream image; cleanest), (2) host-it (KH keeps hosting pullmd as a network service; re-opens egress, weakens sovereignty), (3) drop pullmd for Docling (MIT, already in the cocoindex image; lower HTML quality). Liam’s call, deferred.
ID-45 (re-ingest cutover gate): the cutover sequence that produces the first-client handover DB. ID-45’s task-list deps are ['101','28','36','41','69']; 28/36/41 are done, but 101 and 69 are NOT — so ID-101 is a HIDDEN gate of ID-45 (cocoindex entity-relationship + holder-rule parity, a direct ID-45 dependency). The cutover D-gates (ID-45/T7): D1 re-ingest the full corpus on a non-prod target (OQ-95-2 / OQ-64-8: fresh preview branch vs persistent staging); D2 cat .temp/project-ref then relink prod ONLY at deliberate cutover (the drift gotcha); D3 prod cutover -> client-handover DB state.
ID-68 (genericise + history purge): the public-flip programme. The filter-repo history purge (section 4.3) runs under ID-68 and scrubs migrations in the same pass — which is precisely why a separate migrations repo buys nothing. The branding-overlay automation ({68.22}) and the phew-asset untrack (hard sequence) gate the first-client app deploy. The signup_policy pivot landed under {68.21} this session.
ID-69 (client-FS corpus structure): the canonical-corpus folder convention is an OPEN ID-69 gap (section 2.5). The per-client filesystem layout we define/provide at handover depends on closing ID-69.
ID-101 (cocoindex entity-relationship + holder-rule parity): surfaced above as the hidden gate of ID-45 — opened this session (aded3fbe4). Not a direct ID-95 dependency but it gates the cutover that produces the first-client DB.

6.2 Owner open questions (for the Orchestrator to surface to Liam)

OQ-95-1 (BIGGEST — forks the whole topology): managed vs self-hosted Supabase for the first client, given residency? Managed Option A (this brief’s near-term pick) versus self-hosted Option B (only if the contract mandates client-tenancy hosting). Everything downstream (provisioning automation, ops burden, cost, AGPL conveyance) forks here.
- OQ-95-1 RESOLUTION (round 2 — art of the possible): the fork is no longer binary. INTERIM = managed Supabase project-per-client (Option A; quickest to market, what the owner stated as the interim goal). LONG-TERM = the Coolify-hosted BYOC option (section 8) — one Coolify server per client, a Supabase Service plus an in-compose one-shot migration container, driven by a private per-client repo via a shared scoped GitHub App — for clients who want data in their own tenancy. The owner’s described “middle ground” between managed-cloud and bespoke-manual-self-host IS viable and IS named: it is BYOC (Bring Your Own Cloud, section 10). It is a genuine THIRD tier sitting between Option A (managed) and a hand-rolled Option B, NOT a free midpoint — it carries real ops cost (you own the backups via a pg_dump->S3 sidecar, you own upgrades, you lose PITR/Management-API/branching; section 8.4). The topology is now de-risked and named; WHEN to build the BYOC tier remains a Liam decision (it is an on-prem offering to stand up per regulated/sovereignty client, not a day-one build).
OQ-95-2 (= OQ-64-8): re-ingest target — a fresh Supabase preview branch (owner’s lean; cleaner rollback) vs the persistent staging branch? Gates the ID-45 D1 D-gate.
OQ-95-3 (the platform “main” DB — no design exists): what IS the platform “main” DB concretely? There is NO cross-DB sync design anywhere. If clients are fully isolated, how does platform knowledge improvement propagate to / from client DBs? Is the “main” DB a real shared knowledge store, or just the dev/demo instance? This needs a decision before any “platform owns canonical schema + clients isolated” topology is final.
- OQ-95-3 RESOLUTION (round 2): the platform “main” DB is the control-plane canonical source the platform curates — the single place platform-owned knowledge (reference content, taxonomy, catalogue requirements) is authored and improved. It is NOT a live shared store every client reads from. Knowledge reaches isolated client DBs via one-directional periodic push (Pattern 2) plus API-layer serving (Pattern 5) for content that need not live in the client DB (section 9). For a hard-isolation (safeguarding) client, AVOID postgres_fdw (Pattern 3) and logical replication (Pattern 4): both reintroduce a STANDING cross-DB dependency and break the moment the client DB sits in the client’s VPC. No bespoke cross-DB sync ENGINE is needed — reuse the same catalog (client -> DB URL) + small fan-out worker + per-client version table that delivers schema migrations (section 9). The same one-way push mechanism serves BOTH eras: a direct DB connection while platform-hosted (interim), and a git/GitOps-delivered versioned payload once the DB is in the client VPC (BYOC). This is the realistic answer to “how does platform knowledge improvement reach isolated client DBs” — and it removes OQ-95-3 as a blocker on the topology decision.
OQ-95-4 (= OQ-66-7): AGPL-pullmd conveyance mitigation if the on-prem pipeline ships into the client perimeter at handover (don’t-convey / host-it / Docling-swap).
OQ-95-5 (= ID-69): client-FS corpus folder structure — the canonical layout we define and provide at handover.
OQ-95-6: platform brand / domain — standing open item (neutral platform dev/demo deploy + brand pick are UNTRACKED).
OQ-95-7 (runbook residual, not a decision): client-app-deploy.md step 3c still prescribes the old ALTER DATABASE ... SET app.allowed_signup_domain GUC. Update it to INSERT INTO public.signup_policy (allowed_domain) VALUES (...) ON CONFLICT (id) DO UPDATE SET allowed_domain = EXCLUDED.allowed_domain;. Code fix landed this session; runbook lags.

7. Spec-upgrade recommendation

ID-95 warrants a PRODUCT + TECH upgrade, but it is gated on OQ-95-1 (managed vs self-hosted for the first client) and OQ-95-3 (the platform “main” DB definition). These two owner decisions fork the entire topology and the cross-DB-sync design — writing PRODUCT/TECH before they are answered would specify against an undecided shape.

Recommended gating decision before {95.2} PRODUCT: Liam resolves OQ-95-1 and OQ-95-3. With those answered, PRODUCT can specify per-client deployment behaviour (provisioning, isolation invariants, residency-as-input, config-as-data) and TECH can specify the canonical-schema fan-out mechanics (refs registry, relink-safe db push, the private provisioning repo if/when warranted). Until then, this RESEARCH brief plus the near-term Option-A recommendation is sufficient to stand up the first client non-precludingly.

Round-2 update to section 7: OQ-95-3 is no longer a hard gate on PRODUCT — round 2 resolves it (section 9: control-plane canonical source + one-way push + API-layer; no bespoke cross-DB engine). OQ-95-1 is reframed from a binary fork into an interim/long-term ladder (managed now -> Coolify BYOC later; section 5.4), so PRODUCT can specify the interim managed topology and the long-term BYOC tier as a named, de-risked option rather than waiting on a single undecided fork. The only genuinely open owner decision remaining is WHEN to build the BYOC tier (a sequencing call, not a topology unknown).

8. Coolify middle-ground (the BYOC on-prem option)

Round 2, lane C1. The owner asked for the “art of the possible” on a Coolify-hosted middle ground for client databases — between Tier-1 managed Supabase cloud and a hand-rolled Option-B self-host. Verdict: the Coolify middle-ground is coherent and operable — a genuine third tier with its own ops cost, NOT a free midpoint.

8.1 Coolify ships a complete one-click Supabase service (full official stack)

Coolify has a first-class one-click Supabase service (https://coolify.io/docs/services/supabase), backed by a docker-compose Service template (https://github.com/coollabsio/coolify/blob/v4.x/templates/compose/supabase.yaml). It is the COMPLETE official stack: Postgres, Studio, Kong, GoTrue (Auth), PostgREST, Realtime, Storage, postgres-meta, imgproxy, Supavisor, Analytics (Logflare), Edge Functions, Vector, plus a bundled MinIO for object storage. This is not a partial port — it is the same set of services as the upstream self-host Compose (section 3.2), wrapped for Coolify’s deploy/secrets lifecycle.

Two known bugs to design around:

Public-Postgres-exposure bug — the Coolify docs themselves warn the Supabase template can expose Postgres publicly; workaround is hand-editing the compose plus a firewall rule (https://coolify.io/docs/services/supabase).
container_name rewrite breaks Realtime DNS — Coolify rewrites container_name with a UUID suffix, which breaks Realtime’s DNS-based service discovery (coollabsio/coolify issue 2060, https://github.com/coollabsio/coolify/issues/2060).

8.2 GitHub App integration — private repos plus auto-deploy on push

Coolify deploys from a repo via Dockerfile / Nixpacks / docker-compose (https://coolify.io/docs/applications/ci-cd/github/overview). PRIVATE repos are supported two ways: a GitHub App (recommended — least-privilege read-only contents, one App serves many repos, auto-configures the webhook; https://coolify.io/docs/applications/ci-cd/github/setup-app) or a Deploy Key (https://coolify.io/docs/applications/ci-cd/github/deploy-key). Auto-deploy on push is native (https://coolify.io/docs/applications/ci-cd/github/auto-deploy). One scoped GitHub App can therefore serve N private per-client repos — the same shape as KH’s existing resolve-private-docs App-token bridge (section 4.4), reused for the BYOC delivery channel.

8.3 The canonical migration-delivery mechanism — a one-shot migration container

There is NO post-deploy command-hook field for compose deploys in Coolify (https://coolify.io/docs/builds/packs/docker-compose). Migration delivery has three mechanics, of which only one is the real repo->Coolify->client-DB path:

Init scripts (/docker-entrypoint-initdb.d) run ONLY on a fresh Postgres volume — bootstrap-only, never re-run on redeploy.
RECOMMENDED: a one-shot migration container in the compose, marked exclude_from_hc: true (https://coolify.io/docs/knowledge-base/docker/compose), running idempotent migrations. It re-runs on each push->redeploy. This is the canonical repo->Coolify->client-DB migration path.
GitHub Actions pre-build — run migrations in CI before the build.

Secrets are injected encrypted at deploy time (${VAR}, :?-required syntax, and the magic SERVICE_PASSWORD_* generators) (https://coolify.io/docs/knowledge-base/docker/compose).

So the canonical BYOC schema-delivery loop is: push to the private per-client repo -> Coolify auto-deploys -> the in-compose one-shot migration container (exclude_from_hc: true) re-runs the idempotent migration set against the client’s Supabase Postgres.

8.4 Isolation — per-COMPOSE-resource Docker networks are strong; Projects/Environments are organisational only

Coolify’s hierarchy is Projects -> Environments -> Resources -> Server / Destination (Docker network) (https://coolify.io/docs/knowledge-base/destinations/). Each compose deploy gets its OWN isolated bridge network (UUID-named); each resource has per-resource encrypted env; cross-stack reachability is opt-in only. That per-compose network isolation is strong.

BUT: Projects and Environments are ORGANISATIONAL groupings, not a hard security boundary. A standalone Coolify Database joins the destination network and does NOT get its own isolated net (whereas a Supabase running as a compose SERVICE does). All stacks on one host share the kernel, the Docker daemon, Traefik, CPU and RAM. Multi-server is native (a Server dropdown per resource). Conclusion: internal multi-tenancy on one host is fine; but for a client who needs data in their OWN tenancy, the clean boundary is ONE Coolify server per client. Multi-server is already proven in the live setup (section 8.5 — two servers/apps registered).

8.5 What you LOSE vs managed (and what you do NOT)

Cons vs managed Supabase cloud (the real ops cost of this tier):

Backups / PITR — Coolify backups are logical pg_dump -> S3 with cron plus retention; there is NO WAL / PITR (https://coolify.io/docs/databases/backups). Worse, because the Supabase Postgres runs INSIDE a Service (not as a first-class Coolify Database), it does NOT get the first-class backup UI at all — you must wire a DIY pg_dump -> S3 sidecar explicitly.
Manual upgrades — bump the image tag and recreate; plus the container_name / Realtime bug (section 8.1) to manage on each upgrade.
No Management API / branching — the managed-platform conveniences (Management API provisioning, preview branches) are unavailable.

NOT losses (these come for free in the template):

TLS — Traefik ACME issues certificates automatically.
Connection pooling — Supavisor is in the template.

On operability this Coolify path dominates a bespoke manual self-host: it gives you GitOps delivery, encrypted secrets, auto-deploy, and TLS out of the box. It is strictly the better operable form of round-1 Option B.

8.6 SIZING — do NOT co-locate a per-client Supabase on the Phew pipeline VPS

The live Phew setup: Coolify server knowledge-hub-phew (IONOS 77.68.122.71, Ubuntu 24.04, 8 vCPU / 15.6 GiB, Traefik 3.6.17), one project, two compose apps (kh-onprem-pipeline-production / -staging from ai-solution-hub/knowledge-hub main / staging). There are currently NO Coolify Databases or Services — pipeline only; the Phew Postgres is the managed Supabase staging branch via COCOINDEX_DB_DSN, so there is zero DB load on the VPS today.

Do NOT co-locate a per-client Supabase on this box. A Supabase Service is ~15 containers at ~3-5 GiB idle; the pipeline already runs a 3.7 GB Playwright image plus ingestion bursts that pressure the 16 GiB RAM ceiling. Stacking a per-client Supabase on top risks OOM and muddies the isolation story. RECOMMEND a separate Coolify server per client — a dedicated IONOS VM, or a VM in the client’s own cloud added as a second Coolify server.

The existing deploy/coolify/docker-compose.{production,staging}.yaml in the public repo already PROVE the GitHub -> Coolify -> compose + secrets pattern: secrets-as-${VAR}, Traefik labels, and named volumes. The BYOC path reuses exactly this proven shape, swapping the pipeline image for the Supabase Service template plus the one-shot migration container.

8.7 Section 8 recommendation

Managed now (Tier 1). Build Coolify BYOC as the on-prem offering (Tier 3) when a regulated/sovereignty client warrants it: ONE Coolify server per client, the Supabase Service template plus an in-compose one-shot migration container (exclude_from_hc: true) driven by a PRIVATE per-client repo via a shared scoped GitHub App, with an EXPLICIT pg_dump -> S3 backup job (the Service Postgres will not get the first-class backup UI).

9. Canonical-content propagation to isolated client DBs (OQ-95-3 resolution)

Round 2, lane C2. This resolves OQ-95-3: how does platform-owned canonical knowledge reach physically-isolated client DBs, and how does one canonical schema fan out across N of them?

9.1 Schema fan-out across N client DBs is a SOLVED problem

One schema applied across N databases is a well-trodden multi-tenant pattern: a catalog (tenant -> DB URI) plus a migration-orchestrator worker that fans the SAME migration out to each DB, plus a per-tenant version table (tenant_migrations). It is never manual. Prior art:

Azure SaaS DB-per-tenant patterns + the Elastic Database Client Library (https://learn.microsoft.com/en-us/azure/azure-sql/database/saas-tenancy-app-design-patterns?view=azuresql).
Azure Elastic Jobs — run T-SQL across a target group that enumerates all DBs in pools, with @max_parallelism; idempotent statements required (https://learn.microsoft.com/en-us/azure/azure-sql/database/elastic-jobs-overview?view=azuresql).
django-tenants migrate_schemas — migrate public first, then loop tenants (--executor=multiprocessing).
Rails apartment — migrate tenants after the primary; the CD caveat is that migrations must be super-safe backward-compatible / expand-contract (https://github.com/rails-on-services/apartment).

Silo (DB-per-tenant) vs shared+RLS trade-off: silo gives strong isolation and per-tenant backup/restore, cost scales linearly, and failed-migration handling is harder; shared+RLS is cheapest, with AWS documenting RLS-centralised enforcement (https://aws.amazon.com/blogs/database/multi-tenant-data-isolation-with-postgresql-row-level-security/). The 2026 consensus is HYBRID — pool standard tenants, isolate regulated/enterprise ones (https://dasroot.net/posts/2026/01/multi-tenancy-database-patterns-schema-database-row-level/) — which is exactly this brief’s Option D / three-tier ladder.

KH reduction: the Supabase CLI already applies migrations to any target via supabase db push --db-url (https://supabase.com/docs/reference/cli/supabase-migration). So KH does not need the heavy Azure/Rails machinery — it needs only a **catalog of client DB URLs + a small fan-out loop

a per-client version table + expand-contract migrations**. That is the Azure-Elastic-Jobs / django-tenants / Rails-apartment pattern reduced to a Supabase-CLI loop.

9.2 Five content-propagation patterns, with isolation implications

The distinction is vendor-owned SHARED/reference data (common, platform-curated) vs tenant-specific data (Azure’s “reference data stored once, shared” section). Five patterns:

Seed-at-provision — one-way copy at DB creation, then diverges. Isolation perfect; currency poor (never updated after provision).
Periodic one-way push — platform -> tenant upsert by stable key, with tombstone deletes; reuses the catalog + worker. Isolation strong (one-way only); realism HIGH (cron + upsert); currency good. This is the workhorse.
Shared read-only reference DB via postgres_fdw (https://www.postgresql.org/docs/current/postgres-fdw.html, https://www.crunchydata.com/blog/understanding-postgres_fdw) — WEAKENS physical isolation: it is a STANDING cross-DB dependency. DISQUALIFYING for a contractually-isolated safeguarding client; acceptable only for non-regulated tenants.
Logical replication / CDC — publication/subscription with row filters (https://www.postgresql.org/docs/current/logical-replication.html; Citus reference tables). One-directional, but a STANDING live connection into each tenant DB, which is often network-blocked once the DB is in a customer VPC. Reserve for the platform-hosted tier only.
API-layer serving — canonical content is NEVER copied into the tenant DB; the app merges it at request time. STRONGEST isolation, always-live, high realism for KH (which already has Vercel + an API + a knowledge layer). Trade-off: no DB-local joins, and the tenant DB is not self-contained (matters for an air-gapped deployment).

Isolation ranking: 5 and 1 are cleanest; 2 is clean if strictly one-way; 3 and 4 compromise isolation.

9.3 RESOLUTION for a hard-isolation (safeguarding) client

Default to one-directional periodic push (Pattern 2) for content that must live in the client DB, plus API-layer serving (Pattern 5) for content that need not be copied into the client DB at all. AVOID postgres_fdw (Pattern 3) and logical replication (Pattern 4) for regulated clients — both reintroduce a standing cross-DB dependency, and both break the moment the DB sits in a client VPC (network-blocked, contractually disallowed). Pattern 1 (seed-at-provision) is the degenerate first tick of Pattern 2.

9.4 The through-line — one mechanism, both eras

The SAME one-directional push mechanism (catalog + fan-out worker + per-client version table) serves BOTH deployment eras:

Interim (platform-hosted, Tier 1): the fan-out worker holds a direct DB connection and upserts the canonical payload into each managed client project.
BYOC (Tier 3, DB in client VPC): the canonical payload is delivered as a git/GitOps-versioned artefact via the private per-client repo (section 8.2), applied by the Coolify-side reconciler (section 10). No standing inbound connection into the client VPC is required.

This is the realistic, contractual-grade answer to “how does platform knowledge improvement reach isolated client DBs”: NOT cross-DB replication, but a one-way push (DB-direct interim, GitOps-delivered BYOC) over a single reusable catalog + worker. No bespoke cross-DB sync engine is needed.

10. BYOC reference model

Round 2, lane C2. The owner’s described “managed DB in the client VPC plus a dedicated client repo” is the textbook BYOC (Bring Your Own Cloud) pattern. This section names it and supplies the closest real-world analogue.

10.1 BYOC is a named, mainstream pattern — control plane / data plane split

BYOC: the vendor delivers and operates its software INSIDE the customer’s own cloud / VPC; the customer owns the infrastructure and the data; the vendor never holds the data (https://distr.sh/glossary/byoc-definition/, https://blog.railway.com/p/what-is-byoc-developer-guide-2026). The universal mechanic is a control-plane / data-plane split (https://nuon.co/blog/byoc-control-plane-data-plane-architectures, https://bentoml.com/llm/getting-started/bring-your-own-cloud):

Control plane (vendor): UI, auth, APIs, orchestration, health metadata — NEVER the customer’s data.
Data plane (customer VPC): the engine, compute, storage, and the data itself.

“Manage without hosting the data” is achieved via: (1) agent-based reconciliation — an agent in the customer cluster polls the vendor API and reconciles desired-vs-actual; (2) scoped cross-account IAM / zero-access-by-default (WarpStream / Confluent, https://www.confluent.io/learn/bring-your-own-cloud/); (3) secrets held in the customer’s own secret store. Software and schema delivery are declarative artefacts (K8s / Helm / Terraform / GitOps) applied by a customer-side reconciler.

10.2 Closest real analogue — ClickHouse Cloud BYOC

ClickHouse Cloud BYOC is the closest analogue to the KH shape (https://clickhouse.com/docs/cloud/reference/byoc/architecture, https://clickhouse.com/blog/announcing-general-availability-of-clickhouse-bring-your-own-cloud-on-aws):

Control plane in the VENDOR VPC; data plane is an EKS cluster in the CUSTOMER VPC.
Control <-> data communicate over HTTPS:443 for config / health / deploy only — never data.
DB and infrastructure upgrades are delivered via ArgoCD GitOps plus Fast / Regular / Slow release channels plus a maintenance window.
Data and backups stay in the customer’s own S3.

(Snowflake Openflow BYOC is a second reference point, https://www.snowflake.com/en/blog/openflow-byoc-data-integration/.)

10.3 Mapping to KH

Data plane = client-VPC Supabase / Postgres + app + Coolify (self-host Docker, https://supabase.com/docs/guides/self-hosting/docker; CLI push to any --db-url).
Dedicated client repo = the GitOps delivery channel — ClickHouse’s ArgoCD in miniature.
Coolify deploy = the reconciler (the customer-side agent that applies the declarative artefact).
Control plane = the KH platform: authors migrations and curates canonical content centrally; never holds client data.
Updates = release-channel semantics + maintenance windows (per ClickHouse), delivered as versioned repo payloads (per section 9.4).

Note that BYOC favours a git-repo-delivered seed/sync payload (Pattern 2 over GitOps) over FDW/replication — precisely because VPC isolation rules out the standing cross-DB connections that Patterns 3/4 require (section 9.3). The content-propagation resolution and the BYOC reference model agree.

10.4 Honest BYOC cost

BYOC is not free sovereignty (per Confluent / ClickHouse). The customer accepts partial infrastructure / security / support responsibility; it suits experienced customers. For KH the standing costs are: per-client provisioning automation, client-owned secrets, scoped / zero-standing-access support (the support engineer has no default access to the client’s data plane), and release-channel semantics (Fast/Regular/Slow + maintenance windows rather than ad-hoc pushes). These are the real ops cost of the Tier-3 BYOC offering (section 5.4 / OQ-95-1 resolution) — the reason it is a genuine third tier, not a free midpoint.