Radius Database¶

One database per environment (radius-<env>) on the shared RDB instance. It holds the stock FreeRADIUS schema plus the tables the control plane adds for tenancy, admin authorization, and PKI. The schema is applied by the ordered scripts in db/radius/ (01…09) on a fresh database.

Script	Adds
`01-schema.sql`	Stock FreeRADIUS tables: `radcheck`, `radreply`, `radgroupcheck`, `radgroupreply`, `radusergroup`, `radacct`, `radpostauth`, `nas`, `nasreload`.
`02-tenancy.sql`	`organizations` (the tenant table) + a nullable `org_id` column on the FreeRADIUS tables.
`03-seed.sql`	A seed org `acme`, a test user (`bob`/`hello`) and a loopback NAS — the `radtest` smoke-test fixture. Optional.
`04-auth.sql`	`org_memberships` (admin authorization). Grants are keyed by the Zitadel `sub`; there is no local account table.
`05-radacct-org-trigger.sql`	Trigger that stamps `org_id` onto `radacct` rows from the NAS, so accounting tenancy can't be spoofed.
`06-drop-nas-ports.sql`	Drops the unused `nas.ports` column.
`07-pki.sql`	`certificate_authorities`, `client_certificates`.
`08-users.sql`	`radius_users` — first-class per-org end-user identities — and a `user_id` FK on `client_certificates`.
`09-drop-local-auth.sql`	Drops `local_users`. Admin identity is Zitadel only: a membership is a `sub` and nothing else.

Ownership¶

FreeRADIUS reads radcheck/radreply/nas (auth) and writes radacct/radpostauth (accounting). It only ever sees rows for the requesting NAS's org_id.
The control-plane API owns everything else: it creates orgs, manages NAS clients and radius_users, sets passwords (as radcheck Cleartext-Password rows), records admin grants in org_memberships, and runs the PKI tables.

Tenancy columns¶

org_id uuid (nullable, REFERENCES organizations(id)) on the shared FreeRADIUS tables. Nullable so the stock schema still works, but the org-scoped auth queries treat a non-matching or NULL org_id as no match (fail-closed). org_memberships.org_id is instead nullable-as-platform-scope: NULL there means a platform-wide admin grant.

End users vs. admins¶

Two distinct populations, never mixed:

radius_users — the WPA/captive-portal end users, per org (UNIQUE (org_id, username)), with source = local | ldap | oidc | …. Auth methods attach to them: a password (radcheck) and/or a client certificate (client_certificates.user_id).
org_memberships — the humans who administer an org through the control plane, keyed by their Zitadel sub.