System Users Schema

System user and group objects anchor authentication plus authorization.

Namespace

These tables should move into the platform PostgreSQL schema.

• platform.users
• platform.user_preferences
• platform.user_sessions
• platform.revoked_tokens
• platform.user_groups
• platform.user_group_memberships

Tables

platform.users

Core account object.

• Primary key: id
• Required fields: username, email, password_hash
• Key constraints: unique username, unique email

This object is now platform-global rather than tenant-owned.

platform.user_preferences

Per-user defaults for context selection.

• Primary key: user_id
• Foreign keys:
  • user_id -> platform.users.id (ON DELETE CASCADE)
  • default_tenant_id -> platform.tenants.id
  • default_project_id -> public.projects.id (ON DELETE SET NULL)
• Required fields: default_tenant_id

platform.user_sessions

Active authenticated session context.

• Primary key: id
• Unique key: token_jti
• Foreign keys:
  • user_id -> platform.users.id (ON DELETE CASCADE)
  • current_tenant_id -> platform.tenants.id
  • current_project_id -> public.projects.id (ON DELETE SET NULL)
• Required fields: user_id, token_jti, current_tenant_id, expires_at

platform.revoked_tokens

Token revocation ledger for sign-out and token invalidation.

• Primary key: jti
• Foreign keys:
  • user_id -> platform.users.id (ON DELETE CASCADE)
  • tenant_id -> platform.tenants.id
• Required fields: expires_at, revoked_at

platform.user_groups

Named groups used for tenant-level access control.

• Primary key: id
• Unique partial key: (tenant_id, name) where project_id IS NULL
• Unique partial key: (tenant_id, project_id, name) where project_id IS NOT NULL
• Foreign keys:
  • tenant_id -> platform.tenants.id
  • project_id -> public.projects.id (ON DELETE SET NULL)

platform.user_group_memberships

Membership join table for users and groups.

• Primary key: id
• Unique keys: (tenant_id, user_id), (tenant_id, user_group_id)
• Foreign keys:
  • tenant_id -> platform.tenants.id
  • user_id -> platform.users.id (ON DELETE CASCADE)
  • user_group_id -> platform.user_groups.id (ON DELETE CASCADE)

Relationships

• A user does not carry direct tenant_id or person_id columns.
• Tenant access is resolved through group membership and tenant-scoped authorization tables.
• A tenant can define many groups.
• Membership rows connect users to groups within tenant scope.
• Revoked tokens are deleted automatically when a user is deleted.
• This namespace is intentionally System-level: it owns auth and authorization without embedding business-domain identity linkage directly on the user row.