Weldr Database Architecture

> Preliminary: These details describe the current alpha schema and may change as we finalize the hosted service.

Core Principles

• Single shared database with per-tenant isolation enforced via Postgres Row Level Security (RLS).

• JWT claims (tenant_id, plan) drive all user-facing access. Claims are injected by the Supabase access-token hook at login and refreshed during server-side sessions.

• Service role operations (migrations, background jobs) bypass RLS by presenting role: "service_role" in the claim payload.

• Stable identifiers (UUID) for users, chats, messages, tenants. Application code always scopes writes/reads by the current tenant ID before touching RLS-protected tables.

Key Tables

| Table | Purpose | Tenant Column | Notes | | --- | --- | --- | --- | | tenant | Tenant registry (slug, plan, status) | id (primary key) | Seeded on user sign-up; plan drives feature gates. | | User | Auth-linked users | tenantId | Foreign key to tenant.id. Guest/test users receive auto-provisioned tenants. | | Chat | Chat sessions | tenantId | RLS ensures only matching tenant can read/write. | | Message_v2 | Messages within chat | tenantId | Inherits chat tenant; queries always filtered by tenant. | | dsl_event / dsl_snapshot | DSL change log | tenant_id | Backed by the same RLS policies as chats. | | app_models, runtime_data, component_registry, virtual_fs | Generated artifacts / runtime state | tenant_id | All RLS protected to isolate generated assets. |

RLS & JWT Mapping

• RLS policies defined in lib/db/migrations/0012_multi_tenant.sql rely on helper functions:

- current_tenant_id() reads request.jwt.claims -> 'tenant_id'. - is_service_role() checks request.jwt.claims -> 'role'.

• Policies follow the pattern current_tenant_id() = tenantId (or table-specific field). Service role is exempt.

• The Supabase access-token hook (supabase/functions/access-token/index.ts) is responsible for embedding tenant_id / plan claims into JWTs.

• Server-side code guarantees claims stay fresh by calling ensureSupabaseClaims() after resolving the tenant ID locally (lib/auth/supabase-auth.ts).

Required Environment Variables

SUPABASE_URL=https://<project>.supabase.co
SUPABASE_SERVICE_ROLE_KEY=...
NEXT_PUBLIC_DEFAULT_TENANT_ID=00000000-0000-0000-0000-000000000001
POSTGRES_URL=postgresql://<user>:<pass>@<host>:<port>/<db>

Detailed setup instructions for deploying the access-token hook live in docs/deployment/supabase-setup.md.

Development Workflow

Run migrations against local Postgres: POSTGRES_URL=postgresql://... pnpm db:migrate.

Use the Supabase CLI (or dashboard) to deploy the access-token hook so JWT claims match expectations.

Start dev server; guest/test sessions automatically provision tenants via createTestSession() when X-Test-User header is present.

CLI sync/watch generated code – IR writes reuse the same tenant key as the chat session and are covered by RLS.

Testing Strategy

• Unit: tests/unit/supabase-claims.test.ts ensures service role sync updates app metadata while respecting env fallbacks.

• CLI: Fixture-driven smoke tests guarantee weldr sync/watch behave without network dependencies.

• Integration: tests/integration/tenant-guard.test.mjs spins up two tenants, proves tenant A can read DSL while tenant B is denied (403).

• Database (added now): tests under tests/db run migrations in an isolated database and assert RLS behaviour by manipulating request.jwt.claims.

Operational Checklist

• [ ] Deploy Supabase access-token hook after every schema change involving tenants/plans.

• [ ] Verify migrations on staging via disposable database before production roll-out.

• [ ] Keep .env.local safe – contains POSTGRES_URL and Supabase keys.

• [ ] When introducing new tables with tenant-critical data, ensure:

- Column tenantId / tenant_id exists. - RLS is enabled with the standard policy block. - Migrations update current_tenant_id() checks as needed.

Keeping these pieces aligned guarantees Weldr maintains strict tenant isolation and predictable migrations across environments.