Data Model

Kyqu uses PostgreSQL for all persistence. The schema is auto-migrated on every server start.

Entity Relationship

admin_users ──── admin_sessions
     │            admin_backup_codes
     │            admin_ip_allowlist
     │            admin_impersonation_sessions
     │
     │ (via workspace_members)
     ▼
workspaces ──── projects ──── project_auth_settings
                   │            project_api_keys
                   │            project_onboarding
                   │            project_jwt_keys
                   │            project_oauth_providers
                   │            project_oauth_states
                   │
                   ├── project_users ──── project_sessions
                   │       │              project_auth_tokens
                   │       │              project_user_backup_codes
                   │       │              project_user_oauth_links
                   │       │              project_user_roles ──── project_roles
                   │       │              project_passkeys
                   │       │              project_webauthn_challenges
                   │
                   ├── project_user_fields
                   ├── project_webhooks ──── project_webhook_deliveries
                   ├── project_email_templates
                   ├── project_email_outbox
                   └── project_logs

Tables

admin_users

Column	Type	Description
id	text PK	UUID
email	text UNIQUE	Admin email
normalized_email	text UNIQUE	Lowercase trimmed email
name	text	Display name
password_hash	text	scrypt hash (scrypt:salt:hash)
is_superadmin	boolean	Superadmin privileges
created_at	timestamptz	Creation timestamp
updated_at	timestamptz	Last update timestamp

admin_sessions

Column	Type	Description
id	text PK	UUID
admin_user_id	FK → admin_users	Owner
token_hash	text UNIQUE	SHA-256 of session token
created_at	timestamptz	Creation timestamp
expires_at	timestamptz	Expiry timestamp
revoked_at	timestamptz?	Revocation timestamp

projects

Column	Type	Description
id	text PK	UUID
workspace_id	FK → workspaces	Parent workspace
name	text	Project name
slug	text	URL-safe slug
product_type	text	web, app, extension, api, other
domain	text	Primary domain
status	text	active, suspended, archived
created_at	timestamptz	Creation timestamp
updated_at	timestamptz	Last update timestamp
UNIQUE	(workspace_id, slug)	Unique slug per workspace

project_auth_settings

Column	Type	Default	Description
project_id	FK → projects PK	—	Parent project
auth_enabled	boolean	true	Master auth toggle
email_password_enabled	boolean	true	Email/password auth
email_verification_required	boolean	false	Require email verification
password_reset_enabled	boolean	true	Password reset flow
magic_link_enabled	boolean	false	Magic link auth
two_factor_enabled	boolean	false	TOTP 2FA
allowed_origins	jsonb	[]	CORS allowed origins
blocklisted_domains	jsonb	[]	Blocked email domains
whitelisted_domains	jsonb	[]	Allowed email domains
password_min_length	integer	10	Min password length
lockout_enabled	boolean	true	Account lockout
lockout_max_attempts	integer	5	Failed attempts before lock
lockout_window_minutes	integer	15	Lockout window
login_url	text	''	Post-login redirect URL
email_verified_url	text	''	Post-verification redirect
password_reset_url	text	''	Password reset form URL

project_users

Column	Type	Description
id	text PK	UUID
project_id	FK → projects	Parent project
email	text	User email
normalized_email	text	Lowercase trimmed email
name	text	Display name
password_hash	text	scrypt hash
status	text	active, suspended
email_verified_at	timestamptz?	Email verification timestamp
password_reset_required	boolean	Force password reset
failed_login_count	integer	Consecutive failed logins
locked_until	timestamptz?	Lockout expiry
totp_secret	text?	TOTP secret (Base32)
totp_enabled_at	timestamptz?	TOTP enrollment timestamp
metadata	jsonb	Custom user fields data
UNIQUE	(project_id, normalized_email)	One account per project

project_sessions

Column	Type	Description
id	text PK	UUID
project_id	FK → projects	Parent project
project_user_id	FK → project_users	Owner
token_hash	text UNIQUE	SHA-256 of bearer token
ip_address	text?	Client IP at creation
user_agent	text?	User agent at creation
created_at	timestamptz	Creation timestamp
expires_at	timestamptz	Expiry timestamp
revoked_at	timestamptz?	Revocation timestamp

Other Tables

• workspaces — Top-level organizational unit
• workspace_members — Links admins to workspaces
• project_api_keys — Public/secret key pairs for project auth
• project_auth_tokens — One-time tokens (verification, password reset, magic link)
• project_roles / project_user_roles — Role-based access (extensible)
• project_user_fields — Custom field definitions per project
• project_user_backup_codes — TOTP backup codes (hashed)
• project_webhooks — Webhook endpoints per project
• project_webhook_deliveries — Webhook delivery log
• project_email_templates — Custom email templates per project
• project_email_outbox — Sent/stored email records
• project_logs — Audit event log
• project_onboarding — Per-project onboarding wizard state
• api_rate_limits — PostgreSQL-backed rate-limit state
• admin_backup_codes — Admin TOTP backup codes (hashed)
• admin_ip_allowlist — IP-based admin access restrictions
• admin_impersonation_sessions — Admin-to-user impersonation tracking
• project_oauth_providers — OAuth2 provider configs (Google, GitHub)
• project_oauth_states — OAuth2 state nonces for PKCE
• project_user_oauth_links — OAuth identity links per project user
• project_passkeys — WebAuthn/FIDO2 passkey credentials
• project_webauthn_challenges — WebAuthn challenge nonces (5-min TTL)
• project_jwt_keys — Per-project RSA-2048 signing keys (AES-256 encrypted)
• schema_migrations — Migration version tracking

Migration Strategy

Migrations are idempotent and run on every server start using CREATE TABLE IF NOT EXISTS and ALTER TABLE ... ADD COLUMN IF NOT EXISTS — no manual migration scripts needed.