← Auth / Security

Data model and contracts

auth / security · workzone
1 Outward contract The API error format. What the client sees.
Problem Details (RFC 9457) + extensions
Content-Type: application/problem+json
{
  "type":       "/errors/invalid-credentials"  error type (URI)
  "title":      "Invalid credentials"          type title
  "status":     401                            mirrors the HTTP status
  "code":       "INVALID_CREDENTIALS"          machine-readable
  "detail":     "Invalid credentials"          human, i18n-ready
  "request_id": "req_a1b"                   tracing · = X-Request-Id header
}
422 — + errors:      [{ "field", "message" }]
429 — + retry_after: 30 sec · + Retry-After header
Codes by HTTP status
  • 401INVALID_CREDENTIALS
  • 401TOKEN_EXPIRED
  • 401TOKEN_INVALID
  • 403FORBIDDEN
  • 403LAST_OWNER_PROTECTED
  • 403PASSWORD_CHANGE_REQUIRED
  • 404NOT_FOUND
  • 404SETUP_UNAVAILABLE
  • 409CONFLICT
  • 409SMTP_NOT_CONFIGURED
  • 409ALREADY_LINKED
  • 410INVITE_EXPIRED
  • 410INVITE_USED
  • 410RESET_EXPIRED
  • 410LINK_EXPIRED
  • 422VALIDATION_ERROR+ errors
  • 429RATE_LIMITED+ retry_after
Principle → Brute-force
Don't reveal internal details. 401 is always generic (not “user not found” vs “wrong password”). Validation errors (422) are returned in the same format with an errors array, not in the framework's default form.
why so
By default FastAPI returns a ValidationError in its own format — we intercept it and wrap it in Problem Details.
Format · RFC 9457
The error body is application/problem+json per RFC 9457 (type · title · status · detail) + the code extension (a short, stable token the client matches on) and request_id in the role of instance (the identifier of the specific occurrence).
2 Identity core The identity. The root of all relations.
users accounts
MFA v2
id BigInteger PK —
email Text UNIQUENOT NULL login identifier · lower()
password_hash Text NULLCHECK NULL for SSO · CHECK ↔ auth_provider
full_name Text NOT NULL —
role Text NOT NULLCHECK owner · admin · member
status Text NOT NULLDEFAULTCHECK DEFAULT 'active' · active · deactivated
auth_provider Text NOT NULLDEFAULTCHECK DEFAULT 'local' · local · okta · azure_ad
must_change_password Boolean NOT NULLDEFAULT true after a temporary password is issued · gate on sign-in
created_at DateTime(tz) DEFAULT server_default=now()
updated_at DateTime(tz) DEFAULT now() + trigger
last_login_at DateTime(tz) NULL denormalization → Last login
timezone Text NULL override · IANA tz · NULL = inherits org
locale Text NULLCHECK override · ru · en · NULL = inherits org
date_format Text NULLCHECK override · NULL = inherits org
last_chat_model Text NULL personal sticky chat-model pick · seeds new conversations · NULL = admin default
mfa_enabled Boolean NOT NULLDEFAULT true after the 1st code is confirmed v2
mfa_secret Text NULL AES-256-GCM v2
mfa_recovery JSONB NULL hashes of single-use codes (argon2id) v2
User status
Deliberately two states, with no intermediates (suspended, pending). deactivated — a block by an admin, not self-removal.
Temporary-password gate → Forced password change
must_change_password is set to true when a temporary password is issued and cleared on a successful change at the gate. While the flag is true, the server rejects any authenticated request except password change and sign-out (403 PASSWORD_CHANGE_REQUIRED) — the application admits neither the UI nor a direct API call. The login / refresh response carries the flag so the client can send the user straight to the change screen.
Locale — personal override → Language and region
timezone / locale / date_format — nullable: NULL means “inherit the organization's default”, set in the profile. They affect only the frontend display; storage and the API are UTC. The full precedence chain — in Admin Panel.
Last login → Admin Panel
Denormalization — updated on a successful sign-in.
why so
The full sign-in history is in audit_log. The duplicate on users is needed so the user list opens without a JOIN to the journal.
Email normalization
Email is lowercased on input, uniqueness is UNIQUE (lower(email)) (or citext). A case-sensitive UNIQUE would let Alice@x and alice@x through as two accounts.
why so
The same normalized form is used in invite_tokens.email, identity_mapping.source_email, and in the brute-force key (brute:account:{email_hash}): otherwise a guessing attempt resets the counter by changing case.
CHECK vs ENUM
Fields with a fixed set of values (role, status, auth_provider, result) are plain Text with a CHECK constraint, not a native PostgreSQL ENUM.
why so
Easier to migrate via Alembic.
3 Sessions and access The sign-in lifecycle. → Crypto core
refresh_tokens JWT rotation
FK → users
id BigInteger PK —
user_id BigInteger FK→usersIDX CASCADE
token_hash Text NOT NULLUNIQUE cryptographically random (unlike family_id)
family_id UUIDv7 NOT NULLIDX reuse detection → Token family
is_revoked Boolean NOT NULLDEFAULT DEFAULT false
expires_at DateTime(tz) NOT NULL sliding 30d
absolute_expires_at DateTime(tz) NOT NULL ceiling 90d
remember_me Boolean NOT NULL DEFAULT false choice at sign-in · rotation reissues a cookie of the same kind
user_agent Text NULL device · captured at sign-in and refresh
ip Text NULL address · real IP from nginx · geo at render time
created_at DateTime(tz) DEFAULT now() · the session's “last activity”
invite_tokens invitations 48h
FK → users
id BigInteger PK —
email Text NOT NULL who is invited
role Text NOT NULLCHECK role to assign
token_hash Text NOT NULLUNIQUE —
invited_by BigInteger FK→usersIDX CASCADE
expires_at DateTime(tz) NOT NULL 48h
accepted_at DateTime(tz) NULL NULL = not yet accepted
created_at DateTime(tz) DEFAULT now()
reset_tokens password reset 1h
FK → users
id BigInteger PK —
user_id BigInteger FK→usersIDX CASCADE
token_hash Text NOT NULLUNIQUE SHA-256 · only the hash in the DB
expires_at DateTime(tz) NOT NULL 1h
used_at DateTime(tz) NULL NULL = not yet used
created_at DateTime(tz) DEFAULT now()
Reset token — single-use → Password reset
Use sets used_at; sending another email revokes the user's previous token. Expired and used are indistinguishable to the client — a single response 410 RESET_EXPIRED. The flow machinery — in authentication.
api_keys machine access
FK → users
id BigInteger PK —
user_id BigInteger FK→usersIDX owner · CASCADE · key → user_id → role / ACL
key_hash Text NOT NULLUNIQUE SHA-256 · only the hash in the DB
prefix Text NOT NULLIDX ach_xxxx · identification in the list, not a secret
scope JSONB NOT NULL access (read-only) + sources (null = all available) · never exceeds owner's permissions
expires_at DateTime(tz) NULL 30 / 90 / 365 days · NULL = never-expiring
last_used_at DateTime(tz) NULL the fact and time of use · “Used” in the list
is_revoked Boolean NOT NULLDEFAULT DEFAULT false · revoked manually or by cascade on deactivation
revoked_at DateTime(tz) NULL the moment of revocation · set once, never overwritten · “Revoked keys” in the profile
created_at DateTime(tz) DEFAULT now() · the moment of issue
updated_at DateTime(tz) DEFAULT now() + trigger · the key mutates in place (is_revoked, last_used_at)
Messenger link code — single-use → Messenger linking
Generated in a signed-in session (user_id is known at once), consumed by returning it to the bot in a DM: it links the returning source_user_id to the issuing account via identity_mapping. The code itself is channel-neutral — source (slack · telegram · mattermost) is set by the receiving bot; UNIQUE(source, source_user_id) catches a repeat link (409 ALREADY_LINKED). Expired and used are indistinguishable to the client — a single 410 LINK_EXPIRED.
Token family
family_id — a UUIDv7, assigned at sign-in, inherited through rotation. Reuse detection acts within a chain, without touching all of the user's sessions.
why so
Reuse detection deletes by family_id — it breaks off one rotation chain, not all of the user's sessions. UUIDv7, not v4: the timestamp in the prefix orders the keys, so inserts land at the end of the index (like an auto-increment). Stored as the native uuid type (16 bytes), not text. An internal grouping key, not a secret — cryptographic randomness isn't needed (unlike token_hash).
Session = token family → Concurrent sessions
In UI terms, one session = one family — a single sign-in from a device or browser. An active session is a family with a live refresh token (not is_revoked, not expired); the same criterion drives both the count in the user card and the list in session management. A session card in the UI is the family's last row: its created_at gives “last activity”, and user_agent and ip give the device and address.
who fills it and when
user_agent and ip are written by the backend at sign-in and on every refresh from the current request (the User-Agent header and the real IP from nginx — X-Forwarded-For), not on the client's word. There is no separate “last activity” field: rotation already inserts a new row, and its created_at is the last activity. Geo is not stored — it is derived from ip at render time (approximate; VPN / proxy distort it). The current session is identified by matching the request's refresh cookie with family_id.
4 Federation The link between the source sign-in channel ↔ user.
identity_mapping external identities
FK → users UNIQUE(source, source_user_id)
id BigInteger PK —
user_id BigInteger FK→usersIDX CASCADE
source Text NOT NULLUNIQUE* sign-in channel: SSO · slack · telegram · mattermost (part of the composite UNIQUE)
source_user_id Text NOT NULLUNIQUE* id at the provider (part of the composite UNIQUE)
source_email Text NULL auto-match — verified only
created_at DateTime(tz) DEFAULT now()
This is sign-in federation, not content identities → Knowledge Store
source here is the sign-in channel: an SSO provider (Okta, Azure AD) or a messenger bot (source='slack' → Slack, source='telegram' → Telegram, source='mattermost' → Mattermost), not a content source. Who authored content in Jira / Slack / Confluence and the consolidation of people into an identity is source_principal / identity in Knowledge Store; the bridge is identity.user_id → users.
5 Observability Append-only. Independent of the users lifecycle. → Audit log
audit_log event journal
no UPDATE / DELETE
id BigInteger PK —
actor_id BigInteger NULLIDX NULL = system · no FK (independent of the users lifecycle)
action Text NOT NULL —
target_type Text NULL —
target_id Text NULL —
result Text NOT NULLCHECK success · failure
ip Text NULL —
user_agent Text NULL —
meta JSONB NULL arbitrary context
created_at DateTime(tz) DEFAULTIDX now() · indexed
Layer decision · append-only
No UPDATE / DELETE. actor_id = NULL means system; no FK on users — the audit is kept when an account is deleted (independent of the users lifecycle). result CHECK success | failure.