Auth & identity

Crate: platyn-identity · Migration: 0003_identity.sql · Status: Built

Identity is the domain everything else depends on, and it was built second — immediately after the tenancy primitive — rather than last. Retrofitting tenancy onto a system that grew up single-tenant is what produces a single-row portals table and a year of unpicking it.

Domain model

erDiagram
    TENANTS  ||--o{ MEMBERSHIPS : "has"
    USERS    ||--o{ MEMBERSHIPS : "holds"
    ROLES    ||--o{ MEMBERSHIPS : "grants"
    ROLES    ||--o{ ROLE_PERMISSIONS : "expands to"
    USERS    ||--o{ SESSIONS : "authenticates"
    TENANTS  ||--o{ SESSIONS : "scoped to"
    USERS    ||--o{ TOKENS : "single-use"
    TENANTS  ||--o{ VENDOR_ACCOUNTS : "owns"
    TENANTS  ||--o{ TENANT_KEYS : "wraps DEK"

The central idea: one human, many shops

app.users is global — deliberately not tenant-scoped. A bookkeeper who works for three decorators is one row with one password and one email, not three accounts. app.memberships is the join, and it is RLS-scoped.

That means the login flow has two steps: authenticate the human, then choose the shop.

1. POST /v1/auth/login verifies the password and mints a session with tenant_id null.
2. GET /v1/auth/tenants lists live memberships. One membership? The BFF selects it silently.
3. POST /v1/auth/tenants/{id}/select stamps sessions.tenant_id, after verifying a live membership exists.
4. Every request from then on opens its transaction with that tenant id, and RLS does the rest.

Tables

app.tenants

The shop. This table is the tenant, so its policy keys on id rather than a tenant_id column.

Column	Type	Notes
id	UUID PK
slug	CITEXT unique	URL-safe handle. CITEXT so Acme and acme collide at the database.
legal_name	TEXT
status	app.tenant_status	trial · active · past_due · suspended · cancelled
timezone	TEXT	Default America/New_York. Production schedules are local-time.
currency	CHAR(3)	Default USD.
settings	JSONB	Shop preferences that do not warrant columns.
suspended_at, created_at, deleted_at	TIMESTAMPTZ

ALTER TABLE app.tenants ENABLE ROW LEVEL SECURITY;
ALTER TABLE app.tenants FORCE  ROW LEVEL SECURITY;
CREATE POLICY tenant_self ON app.tenants
    USING (id = app.current_tenant_id()) WITH CHECK (id = app.current_tenant_id());

app.users

Column	Type	Notes
id	UUID PK
email	CITEXT unique	Case-insensitive at the database, not in application code.
email_verified_at	TIMESTAMPTZ	Null until the emailed token is consumed.
password_hash	TEXT	Argon2id. Nullable — SSO-only users have no password.
full_name, avatar_url	TEXT
last_login_at	TIMESTAMPTZ
created_at, disabled_at	TIMESTAMPTZ

No RLS: a user is not owned by a tenant. Reaching a user always goes through app.memberships, which is scoped.

app.roles and app.role_permissions

Seven seeded roles, ranked so “can this person manage that person” is a comparison rather than a lookup table:

id	code	label	rank
1	owner	Owner	100
2	admin	Admin	90
3	sales	Sales	60
4	account	Account Manager	60
5	designer	Designer	50
6	production	Production	40
7	viewer	Viewer	10

Permissions are rows, not an enum. A shop that wants “a senior CSR who can discount” adds a row rather than waiting for a deploy.

INSERT INTO app.role_permissions (role_id, permission) VALUES
    (1,'*'),                                  -- owner
    (2,'company.read'),(2,'company.write'), … -- admin

The permission vocabulary in use today:

Permission	Held by
company.read / company.write	read: all roles · write: admin
customer.read / customer.write	read: all · write: admin, sales, account
deal.read / deal.write	read: all · write: admin, sales, account
deal.price.read / deal.price.write	read: admin, sales, account · write: admin, sales
design.read / design.write / design.approve	read: all · write: admin, designer · approve: admin
catalog.read	all roles
member.manage	owner, admin
vendor.manage	owner, admin
*	owner only

Sales cannot see production-only screens, and vice versa

production deliberately has no customer.read. A press operator does not need the customer’s phone number to run a job, and the narrower the default grant, the less a compromised shop-floor terminal is worth.

app.memberships

Column	Type	Notes
id	UUID PK
tenant_id	UUID	RLS key.
user_id	UUID
role_id	SMALLINT
invited_by	UUID
accepted_at	TIMESTAMPTZ	Null while an invite is outstanding.
revoked_at	TIMESTAMPTZ	Soft revoke — history survives.

UNIQUE (tenant_id, user_id) — one role per person per shop. Two roles would mean resolving a conflict on every check, so the model refuses the question.

SELECT app.apply_tenant_rls('app.memberships');

app.sessions

Column	Type	Notes
id	UUID PK
token_key	BYTEA unique	SHA-256(pepper ‖ raw_token). The raw token is never stored.
user_id	UUID
tenant_id	UUID nullable	Null between login and tenant selection.
idle_expires_at	TIMESTAMPTZ	Slides forward on use.
absolute_expires_at	TIMESTAMPTZ	Never moves.
csrf_secret	BYTEA	For the BFF’s double-submit check.
ip_hash, user_agent	BYTEA, TEXT	Anomaly review. IP is hashed, not stored raw.
last_seen_at, revoked_at	TIMESTAMPTZ

Sessions are not RLS-scoped, and that is written down

Session lookup happens before the tenant is known — it is the step that determines the tenant. Gating it by tenant would make login impossible.

Rather than leaving that as tribal knowledge, app.sessions is a declared row in app.rls_exemptions (migration 0008) with the reason recorded, and the table carries a matching COMMENT. The bar for an exemption is stated in the migration: a table may only be exempted if it is looked up by an unguessable secret before any tenant context exists. token_key is SHA-256 of 256 random bits, so it clears that bar — the token is the capability, and tenant_id on the row is the session’s active tenant rather than an access grant.

app.rls_audit() skips exempted tables and fails CI on every other tenant table that is unprotected. See Testing.

Three partial indexes, all WHERE revoked_at IS NULL, so the hot path never walks dead rows: sessions_user, sessions_tenant, sessions_expiry.

app.tokens

Single-use tokens for email verification, invitations, and password reset. The primary key is the hash — the raw token exists only in the email that was sent.

Column	Type	Notes
token_hash	BYTEA PK
purpose	TEXT	email_verify · invite · password_reset
user_id, tenant_id	UUID	Both nullable — an invite predates the user row.
email	CITEXT	The invited address, before an account exists.
role_id	SMALLINT	The role the invite will grant.
expires_at, consumed_at	TIMESTAMPTZ	consumed_at makes reuse a no-op, not an error.

app.tokens is the second declared RLS exemption, for the same reason as sessions: an invite or reset token is resolved by token_hash before the user is authenticated. Single-use and time-boxed.

app.vendor_accounts and app.tenant_keys

A shop’s own negotiated vendor credentials, under RLS and under envelope encryption. They live in the identity migration because they are tenant configuration, but the catalog domain consumes them. The platform’s catalog-read credentials are not here — the catalog is global, so those are platform config.

Session lifecycle

stateDiagram-v2
    [*] --> Unauthenticated
    Unauthenticated --> TenantPending : POST /v1/auth/login
    TenantPending --> Active : POST /v1/auth/tenants/{id}/select
    Active --> Active : request slides idle_expires_at
    Active --> Revoked : DELETE /v1/auth/session
    Active --> Revoked : admin revokes membership
    Active --> Revoked : tenant suspended
    Active --> Expired : idle or absolute expiry passes
    Revoked --> [*]
    Expired --> [*]

Revocation is a single UPDATE and takes effect on the very next request. That immediacy is the entire reason sessions are opaque rows rather than JWTs — ADR-05.

API surface

Method	Path	Permission	Notes
POST	/v1/auth/login	—	Argon2id verify, mint token. Returns the raw token once.
DELETE	/v1/auth/session	session	Sets revoked_at.
GET	/v1/auth/session	session	User, tenant, role, flattened permissions.
GET	/v1/auth/tenants	session	Live memberships for this user.
POST	/v1/auth/tenants/{id}/select	session	Binds the session to a tenant.
POST	/v1/auth/password/forgot	—	Always 204, regardless of whether the email exists.
POST	/v1/auth/password/reset	token	Consumes a password_reset token.
POST	/v1/auth/verify	token	Consumes an email_verify token.
GET	/v1/members	member.manage	Memberships in the current tenant.
POST	/v1/members/invite	member.manage	Issues an invite token and emails it.
PATCH	/v1/members/{id}	member.manage	Change role. Cannot exceed your own rank.
DELETE	/v1/members/{id}	member.manage	Revokes membership and every session under it.
GET	/v1/vendor-accounts	vendor.manage	Metadata only — never the secret.
POST	/v1/vendor-accounts	vendor.manage	Seals the secret; verifies it against the vendor.

Full request and response shapes are in the API reference.

Security notes

• Argon2id for passwords, SHA-256 for session tokens. Not an inconsistency — a 256-bit uniform random token is not brute-forcible, so a slow hash buys nothing and costs ~50 ms per request. A human-chosen password is the opposite case.
• The pepper is application config, never a database column. A stolen dump alone cannot be used to forge session lookups.
• Login responses are uniform. Unknown email and wrong password produce the same body, status, and timing. Login is rate limited per IP and per email.
• Revoking a membership revokes its sessions in the same transaction. Anything less leaves a fired employee logged in.
• Role changes take effect on the next request, because permissions are resolved per request rather than baked into a token.
• 403 never explains itself. Telling an attacker which permission they lack is telling them what to go get.

Planned

• SSO via OIDC — password_hash is already nullable for it.
• TOTP second factor, with recovery codes in app.tokens under a new purpose.
• An audit log of membership and role changes.
• Scoped API keys for shop-built integrations, as a distinct principal type rather than a session.