ManyLayers stores everything in a single PostgreSQL database. The schema is managed with Goose SQL migrations that are embedded in the binary and applied automatically at startup, so a new deployment reaches the current schema without a separate migration step.
The model below is the canonical logical architecture. It is organised into twelve areas, from identity at the top through to spend governance at the bottom.
Entity relationship model
Naming in the shipped schema
The diagram above uses logical entity names. A few of them differ from the physical table names, because ManyLayers evolved from an earlier schema and existing tables were kept rather than renamed. Use these names when writing queries.
| In the diagram | Actual table | Why |
|---|
ORGANIZATIONS | orgs | The original tenant table, referenced by a dozen others. Foreign key columns are named org_id, not organization_id. |
TEAMS | org_teams | teams was already taken by the gateway’s runtime scope, which carries token budgets, rate limits and model policies. org_teams is the people-grouping in this diagram. |
TEAM_MEMBERS | org_team_members | Follows org_teams. |
AUTH_SESSIONS | sessions | The existing session table, extended with client_type, status and revoked_at. Its hashed-token column is token_hash. |
TEXT holding UUID strings generated in Go, and timestamps are fixed-width TEXT in UTC so they sort chronologically as plain strings. Booleans, integers and JSONB are native.
ROUTING_TARGETS additionally carries a workspace_id column that the diagram omits. It exists so a composite foreign key can prove a target’s provider belongs to the same workspace as its policy.
Roles live in code, assignments live in the database
There are no roles, permissions or role_permissions tables, and there never will be. The split is deliberate:
- The database answers which user or team holds which role, at what scope — that is all
user_role_bindings and team_role_bindings store.
- The application answers what that role allows. Roles (
owner, admin, member, viewer) and permissions (workspace:read, api_key:create, and so on) are Go constants, along with the matrix that maps one to the other.
This means permissions cannot be changed by editing a database row, and a role string that Go does not recognise grants nothing at all — it is discarded during resolution rather than treated as an unknown-but-valid role.
A binding with a NULL workspace is organization-scoped and applies inside every workspace of that organization. A binding with a workspace applies only there. Users receive roles directly and inherit any role bound to a team they belong to.
No principal abstraction
Human users and service accounts are modelled directly. There is no principals table and no generic owner column standing between a credential and its owner.
An api_keys row therefore points at either a user or a service account, enforced by a CHECK constraint that rejects a row setting both. budgets works the same way: each possible target — user, team, application, service account — is its own nullable, individually foreign-keyed column, and a CHECK allows at most one to be set. A budget with none set covers the whole workspace.
Tenant isolation is structural
A resource in one organization can never reference a parent in another, and this is enforced by the database rather than by application checks that a future code path might forget.
Workspaces, teams, applications, service accounts, providers, models and routing policies each carry a composite UNIQUE (id, parent_id), which lets every dependent table use a composite foreign key:
-- An application must live in a workspace belonging to the org it claims.
FOREIGN KEY (workspace_id, org_id) REFERENCES workspaces(id, org_id)
-- A service account's application must be in the service account's workspace.
FOREIGN KEY (application_id, workspace_id) REFERENCES applications(id, workspace_id)
-- A routing target's model must belong to the provider it is paired with.
FOREIGN KEY (model_id, provider_id) REFERENCES models(id, provider_id)
UNIQUE constraints on (org_id, user_id), (team_id, user_id), (workspace_id, user_id) and (workspace_id, team_id). Role bindings use UNIQUE NULLS NOT DISTINCT, so two organization-scoped bindings for the same user, organization and role collide — an ordinary UNIQUE would not catch them, because SQL treats NULL values as distinct.
Deletion semantics
Foreign keys are not uniformly ON DELETE CASCADE. Each relationship gets the behaviour its data deserves:
| Behaviour | Applies to | Reasoning |
|---|
RESTRICT | Organization → workspace, organization → team, workspace → application, application → service account, provider → credential, provider → model, service account → API key, user → API key | Core business resources and credentials. Deleting a parent that still has children fails loudly instead of silently destroying them. |
CASCADE | Routing policy → targets, user → sessions, user → external identities, all membership tables | Pure dependents that have no meaning without their parent. |
RESTRICT (all keys) | Budgets | Financial records must never disappear as a side effect of deleting something else. |
Secrets
No secret material is stored in an ordinary column.
- API keys are stored only as a SHA-256 hash, with a
key_prefix kept separately so a key can be identified in a list without revealing it. Keys support both expiry and explicit revocation; status is a generated column derived from revoked_at and disabled, so it can never disagree with them.
- Invitation tokens are stored hashed. The plaintext token is returned exactly once, when the invitation is created, and cannot be recovered afterwards.
- Session tokens are stored hashed. Revoking a session marks it revoked rather than deleting it, and the authentication lookup only accepts sessions whose status is active — so revocation takes effect immediately while the record survives for audit.
- Provider credentials store a
secret_reference, never a secret. The reference uses the same form the gateway configuration already resolves — ${ENV_VAR} or ${vault:secret/data/path#key} — so credential material stays in your environment or secret manager.
Migrations
Migrations are plain SQL files under apps/internal/store/migrations/postgres, numbered sequentially and embedded into the binary with go:embed. Goose applies any outstanding ones when the store opens, so starting the gateway against a new or an out-of-date database brings the schema up to date automatically.
You can also run them explicitly:
make db-migrate # apply outstanding migrations
make db-reset # DANGEROUS: drop everything and rebuild an empty schema
