Auth Data Model

Purpose

Define the database design for API V3 App-based auth and access control.

Scope Status

• This page is a design specification and SQL blueprint for the upcoming auth implementation phase.

Naming Rule

All new auth tables start with system_.

Table List

Table	Purpose
system_api3_app	App master (identity, status, lifecycle)
system_api3_app_secret	Secret hash versions and rotation history
system_api3_permission	Permission master catalog
system_api3_route_permission	Route key to permission mapping
system_api3_app_permission	App-level granted permissions
system_api3_app_organization	App-level allowed organizations
system_api3_token	Opaque access token sessions
system_api3_audit_log	Security and auth audit events
system_api3_config	Runtime auth settings (TTL, grace hours)

Relation Overview

erDiagram
    system_api3_app ||--o{ system_api3_app_secret : has
    system_api3_app ||--o{ system_api3_app_permission : grants
    system_api3_permission ||--o{ system_api3_app_permission : assigned_to
    system_api3_permission ||--o{ system_api3_route_permission : required_by
    system_api3_app ||--o{ system_api3_app_organization : allowed_org
    system_api3_app ||--o{ system_api3_token : issues
    system_api3_app ||--o{ system_api3_audit_log : produces
    system_api3_token ||--o{ system_api3_audit_log : referenced_by

Loading

Key Design Notes

• Secrets and tokens are stored as hashes, not plaintext values.
• Route permission mapping is stored in DB so authorization policy is auditable and not scattered in controllers.
• Organization access is explicit per app.
• system_api3_audit_log is designed for traceability of:
  • token issue/revoke
  • permission denied
  • organization denied
  • app suspend/revoke
  • secret rotation
• Soft references (organization_id, actor_uid) are indexed strings to avoid migration failures in installations where legacy column types differ.

Suggested Retention Rules

• system_api3_token: purge expired + revoked tokens older than retention policy (for example, 90 days).
• system_api3_audit_log: purge by config (AUDIT_RETENTION_DAYS) with archival if required.

Migration Safety

1. Create new system_api3_* tables first.
2. Seed permission catalog and route mappings.
3. Add new auth middleware in shadow mode (log only).
4. Enable enforcement endpoint-by-endpoint.
5. Remove legacy broad-access behavior after migration cutoff.