Database Schema Domain Map

This document is a domain map of the Open ACE database. For column-level reference, the authoritative source is schema/schema-postgres.sql, which contains all 104 tables + 1 materialized view. When this document and that file disagree, the SQL file wins. Open ACE supports both SQLite (single-machine) and PostgreSQL (production); schema changes go through the Alembic migrations in migrations/versions/ (see SCHEMA_MIGRATION_GUIDE.md).

Domain Map — all 104 tables

Every table defined in schema/schema-postgres.sql, grouped by domain, one line of responsibility each. Column-level details for the 46 most-used tables follow in Common tables in detail; for all other tables, see the SQL file.

Users & Authentication (8)

Table	Responsibility
users	Core accounts: role (platform_admin/tenant_admin/manager/user/readonly), tenant membership, soft delete, per-user token/request quotas, token-invalidation watermark
sessions	API auth sessions: token, user, expiry, active flag
web_user_auth_sessions	Web UI session tokens with their own expiry lifecycle
user_permissions	Per-user permission overrides granted by an admin
role_permissions	Role → permission template rows backing RBAC checks
login_attempts	Failed-login counters powering account lockout
user_tool_accounts	Mapping of discovered tool/OS accounts to platform users (mapping source, status, verification)
tool_account_mapping_rules	User-defined pattern rules that auto-map discovered tool accounts

Messages & Sessions (3)

Table	Responsibility
daily_messages	Analytical fact table of every AI message across tools/hosts; not a Workspace runtime source (see the session data contract)
agent_sessions	Authoritative Workspace session summary, one row per session
session_messages	Authoritative Workspace transcript per session (source-tagged, idempotent by external_message_id)

The three session tables are governed by ../contracts/WORKSPACE_SESSION_DATA_CONTRACT.md, which defines the data boundary between them and the single product semantics of request_count.

Statistics & Metering (8)

Table	Responsibility
daily_stats	Pre-aggregated daily stats per tool/host/sender
hourly_stats	Hourly usage breakdown per tool/host
daily_usage	Tenant-scoped daily usage incl. cache tokens; sync target for autonomous usage
usage_summary	All-time per-tool/host summary for dashboards
user_daily_stats	Per-user daily aggregates
session_daily_usage	Per-session, per-day incremental usage for long-running WebUI sessions (Issue #3338)
request_performance	Raw request performance events (TTFT, durations; Issue #3080)
response_time_stats	Pre-aggregated response-time percentiles per day/tool/host

Multi-Tenant (4)

Table	Responsibility
tenants	Tenant registry: status, plan, billing cycle, over-limit strategy, soft delete
tenant_settings	1:1 tenant settings: filters, audit/retention knobs, SSO, branding, allowed_tools
tenant_quotas	1:1 tenant limits (token/request quotas, max users, max sessions per user)
tenant_usage	Per-tenant daily usage counters

SSO (4)

Table	Responsibility
sso_providers	SSO provider configs (oauth2/oidc/saml) with encrypted secrets, tenant-scoped
sso_identities	Provider identity → local user links
sso_sessions	SSO session tokens with provider access/refresh tokens
sso_auth_states	Short-lived OAuth login-flow state (PKCE verifier, nonce) with TTL cleanup

Autonomous Development (8)

Table	Responsibility
agent_runs	Run timeline anchor, 1:1 with agent_sessions (run_id == session_id)
agent_run_events	Append-only run event stream (run_id is a plain index, not an FK, by design)
agent_approvals	Durable permission request/response pairs keyed by request_id
autonomous_workflows	Autonomous dev workflow state: requirements, repo/PR binding, CI repair, retries, locks
workflow_events	Per-workflow event log (optionally milestone-scoped)
workflow_milestones	Milestone/phase tracking per workflow: dev rounds, review sessions, GitHub issue/PR numbers
policy_rules	Versioned policy rule definitions with matchers
policy_decisions	Per-request policy evaluation audit trail

Compliance & Retention (5)

Table	Responsibility
retention_policies	Per-tenant, per-data-type retention rules (days, action, archive target)
retention_executions	Each retention run: status, locking, scanned/affected counts
retention_evidence	Per-execution proof: before/after counts, archive location and checksum
legal_holds	Legal hold markers that block retention deletion
recycle_bin	Soft-deleted records awaiting expiry or restore

Notification & Integrations (5)

Table	Responsibility
webhook_settings	Singleton outbound-webhook config (encrypted secret, private-URL policy)
webhook_deliveries	Per-alert webhook delivery attempts with retry/cooldown state
dingtalk_settings	Singleton DingTalk integration config (encrypted signing key)
feishu_settings	Singleton Feishu integration config (verification status)
tenant_sensitive_keywords	Tenant-scoped custom sensitive keywords (Issue #2789)

Scheduling & Operations (9)

Table	Responsibility
scheduler_runs	Per-job scheduler execution history incl. lock strategy and fencing tokens (Issue #2187/#2333)
scheduler_leaders	Distributed leader-election state per scheduler job
consistency_violations	Detected data drift with expected/actual values and repair status
deregister_failures	Failed session-termination batches during machine deregistration, replayed in background (Issue #2596)
parse_failure_records	Tool-output parse failures with retry tracking (Issue #2589)
config_import_state	Import state per config key when config moves into the database
schema_metadata	Schema init/version marker used as a DB-level compatibility guard (Issue #2330)
tenant_migrations	Tenant-move batches: affected users/sessions/projects and progress (Issue #2163)
mapping_migration_status	Progress bookkeeping for tool-account mapping backfills

Metering & Billing (10)

Table	Responsibility
tenant_plans	Plan catalog: quota defaults, pricing, feature flags
tenant_period_history	Per-tenant period usage archived at each quota reset
usage_report_receipts	Idempotency receipts for remote usage reports (Issue #1891)
usage_report_rate_limits	Sliding-window rate limiting for usage report ingestion
aggregation_history	Tenant usage aggregation runs with quality report and error state
aggregation_locks	Database-backed distributed locks for aggregation jobs
backfill_logs	Historical backfill job logs (counts, date range, status)
alerts_history	Delivery history of sent alerts (recipients, channels, status)
archive_files	Archived data files with checksum, record count and expiry
tenant_keywords_version	Keyword-list version counter per tenant for multi-process cache consistency

Security (7)

Table	Responsibility
proxy_token_jtis	JTI registry for proxy tokens: single-use/replay protection (Issue #1758)
external_identity_nonces	Replay-protection nonces for the external-identity HMAC probes (issuer + nonce + expiry; Issue #3459)
encryption_keys	Encryption key metadata (fingerprint, status, rotation); plaintext lives in env vars, never in the DB
permission_checkpoints	Resumable checkpoints for async permission setup scans
permission_tasks	Async shared-project permission setup tasks with progress (Issue #2746)
tool_account_conflicts	Conflict events when tool-account mappings collide (Issue #2761)
test_execution_evidence	Structured per-command test verdicts (framework results, coverage)
command_execution_evidence	Authoritative per-command execution record for autonomous runs

Remote Runtime (7)

Table	Responsibility
remote_machines	Registered remote machines: capabilities, heartbeat, token revoke timeout
remote_runtime_commands	HTTP-polling command queue for remote agents (request/response)
remote_runtime_outputs	Persistent SSE replay buffer per remote session
machine_assignments	User → machine permission assignments
agent_tokens	Hashed agent tokens with rotation, pending revoke and versioning (Issues #2499/#2530)
registration_tokens	One-time machine registration tokens (hash + consumption)
api_key_store	Encrypted AI provider API keys per tenant with CLI routing config

Other Domains (25)

Governance & audit:

Table	Responsibility
audit_logs	Platform audit trail (actor, action, severity, tenant resolution)
compliance_reports	Generated compliance report payloads per period
insights_reports	AI-generated usage insight reports
anomaly_status	Deduplicated anomaly detection state with processing status
content_filter_rules	Global content filter rules
security_settings	Security configuration key-value store
retention_history	Legacy retention report snapshots (timestamp + report data)

Workspace & projects:

Table	Responsibility
projects	Registered project workspaces, tenant-scoped, with permission-setup status
user_projects	Per-user project rollups (sessions, tokens, requests, duration)
project_categories	Project category dictionary
prompt_templates	Reusable prompt templates; name is unique for idempotent seeding (Issue #2577)

Alerting & quotas:

Table	Responsibility
alerts	User-facing alert inbox with action links
quota_usage	Per-user, per-period (and per-tool) quota consumption
quota_alerts	Quota threshold alerts with acknowledgment tracking
notification_preferences	Per-user notification channels (email/push/webhook, severity floor)

Collaboration & sync:

Table	Responsibility
teams	Teams with settings
team_members	Team membership with roles
shared_sessions	Session share links with permissions and access counters
knowledge_base	Team knowledge-base entries
annotations	Session/message annotations with threading
sync_events	Multi-source sync event log

Platform settings:

Table	Responsibility
ai_agent_settings	AI agent configuration key-value store
model_gateway_config	Singleton LiteLLM-compatible model gateway config
smtp_settings	SMTP relay config with encrypted password
email_notification_logs	Email delivery attempts with retry state

Common tables in detail (46 tables)

The tables below are the ones most referenced in code and docs, with condensed column-level detail. All other tables (the remaining 58 of the 104) are documented only by schema/schema-postgres.sql — consult it directly.

Users & Authentication

users

Core user table with role-based access control.

Column	Type	Notes
id	integer PK	Auto-increment
username	varchar	UNIQUE, NOT NULL
password_hash	varchar	bcrypt (12 rounds)
email	varchar
is_admin	boolean	DEFAULT false
is_active	boolean	DEFAULT true
role	varchar	CHECK IN ('platform_admin','tenant_admin','manager','user','readonly')
daily_token_quota	bigint	Stored in M units (Issue #3018)
monthly_token_quota	bigint	Stored in M units (Issue #3018)
daily_request_quota	bigint	Stored as actual count (Issue #3018)
monthly_request_quota	bigint	Stored as actual count (Issue #3018)
tenant_id	integer	FK → tenants(id) ON DELETE SET NULL
must_change_password	boolean	DEFAULT false
system_account	text	OS username for multi-user mode
deleted_at	timestamp	Soft delete
avatar_url	varchar(500)	Avatar URL
auto_mapping_enabled	boolean	Auto tool-account mapping toggle
tenant_version	integer	Tenant-move tracking (Issue #2163)
tokens_valid_after	timestamp	Rejects tokens minted before this stamp (Issue #3379)

Indexes: idx_users_active, idx_users_deleted, idx_users_email, idx_users_role, idx_users_tenant

sessions

Authentication sessions with token-based access.

Column	Type	Notes
id	integer PK
token	varchar	UNIQUE, NOT NULL
user_id	integer	FK → users(id) ON DELETE CASCADE
created_at	timestamp
expires_at	timestamp	NOT NULL
is_active	boolean	DEFAULT true

Indexes: idx_sessions_active, idx_sessions_expires, idx_sessions_token, idx_sessions_user_id

web_user_auth_sessions

Web UI authentication sessions.

Column	Type	Notes
id	integer PK
user_id	integer	FK → users(id)
session_token	text	UNIQUE
created_at	timestamp
expires_at	timestamp

user_tool_accounts

Maps system accounts to platform users for different AI tools.

Column	Type	Notes
id	integer PK
user_id	integer	FK → users(id) ON DELETE CASCADE
tool_account	varchar(255)	UNIQUE
tool_type	varchar(50)
description	varchar(255)
mapping_source	varchar	How the mapping was created (Issue #2761)
mapping_status	varchar	Mapping lifecycle status
verification_status	varchar	Account-mapping verification (Issue #3273)
verified_at	timestamp	Verification time
tenant_id	integer	Tenant scope

user_daily_stats

Pre-aggregated daily usage per user for optimized queries.

Column	Type	Notes
id	integer PK
user_id	integer	FK → users(id) ON DELETE CASCADE
date	date
requests	integer	DEFAULT 0
tokens	integer	DEFAULT 0
input_tokens	integer	DEFAULT 0
output_tokens	integer	DEFAULT 0
cache_tokens	integer	DEFAULT 0

Unique: (user_id, date)

Messages & Sessions

The read/write boundaries for these three tables are contractual — see ../contracts/WORKSPACE_SESSION_DATA_CONTRACT.md.

daily_messages

Core message fact table — the analytical store for all AI interactions.

Column	Type	Notes
id	integer PK
date	varchar	NOT NULL
tool_name	varchar	NOT NULL
host_name	varchar	DEFAULT 'localhost'
message_id	varchar	NOT NULL
parent_id	varchar
role	varchar	NOT NULL (user/assistant/system)
content	text
full_entry	text
tokens_used	integer	DEFAULT 0
input_tokens	integer	DEFAULT 0
output_tokens	integer	DEFAULT 0
model	varchar
timestamp	timestamp
sender_id	varchar
sender_name	varchar
message_source	varchar
conversation_id	varchar
agent_session_id	varchar	Links to agent_sessions
user_id	integer
project_path	text
tenant_id	integer	Tenant scope
feishu_conversation_id	varchar	Feishu group chat
group_subject / is_group_chat	varchar / boolean	Group-chat context

Unique: (date, tool_name, message_id, host_name). 18 indexes covering query patterns.

agent_sessions

Authoritative Workspace session summary table (one row per session).

Column	Type	Notes
id	integer PK
session_id	text	UNIQUE
tenant_id	integer	DEFAULT 1; tenant-scoped session lookup and write boundary
tenant_version	integer	Tenant-move tracking
session_type	text	DEFAULT 'chat'
title	text
tool_name	text	NOT NULL
host_name	text	DEFAULT 'localhost'
user_id	integer
status	text	DEFAULT 'active'
total_tokens	integer	DEFAULT 0
total_input_tokens / total_output_tokens	integer	DEFAULT 0
total_cache_read_tokens / total_cache_write_tokens	integer	Cache token totals
message_count	integer	DEFAULT 0
request_count	integer	Distinct assistant responses (see the session data contract)
model	text
project_id	integer
project_path	varchar(500)
context / settings / tags	text	Session context, settings, tags
created_at / updated_at / completed_at / expires_at / paused_at	timestamp	Lifecycle timestamps
workspace_type	text	DEFAULT 'local'
remote_machine_id	text	Associated remote machine
cli_session_id	text	CLI-side session identifier
daily_usage_synced	boolean	Idempotent sync flag to daily_usage (Issue #2585)

session_messages

Authoritative transcript table within an agent session.

Column	Type	Notes
id	integer PK
session_id	text	FK → agent_sessions(session_id)
tenant_id	integer	DEFAULT 1; tenant-scoped message lookup
role	text	NOT NULL
content	text
tokens_used	integer	DEFAULT 0
model	text
timestamp	timestamp
metadata	text
milestone_id	varchar	Autonomous milestone linkage
source	varchar	Write-path source tag
source_timestamp	timestamp	Original event time
external_message_id	varchar	Provider message id; idempotency key with (session_id, role)
content_blocks	text	Structured content replay

Session indexes include idx_agent_sessions_tenant_user, idx_agent_sessions_tenant_updated, idx_session_messages_tenant_session, and idx_session_messages_tenant_session_timestamp.

Statistics

daily_stats

Aggregated daily statistics per tool/host/sender.

Column	Type	Notes
date	varchar(10)	NOT NULL
tool_name	varchar(50)	NOT NULL
host_name	varchar(100)	DEFAULT 'localhost'
sender_name	varchar(100)
total_tokens	bigint	NOT NULL
total_input_tokens	bigint	NOT NULL
total_output_tokens	bigint	NOT NULL
message_count	integer	NOT NULL
project_id	integer
project_path	varchar(500)
user_id	integer
tenant_id	integer	Tenant scope

Unique: (date, tool_name, host_name, sender_name)

hourly_stats

Hourly breakdown of usage.

Column	Type	Notes
date	varchar(10)	NOT NULL
hour	integer	NOT NULL
tool_name	varchar(50)	NOT NULL
host_name	varchar(100)	DEFAULT 'localhost'
total_tokens	bigint	NOT NULL
total_input_tokens	bigint	NOT NULL
total_output_tokens	bigint	NOT NULL
message_count	integer	NOT NULL
tenant_id	integer	Tenant scope

Unique: (date, hour, tool_name, host_name)

daily_usage

Daily usage with cache token tracking.

Column	Type	Notes
id	integer PK
tenant_id	integer	DEFAULT 1; tenant-scoped usage aggregation key
date	date	NOT NULL
tool_name	varchar	NOT NULL
host_name	varchar	DEFAULT 'localhost'
tokens_used	integer	DEFAULT 0
input_tokens	integer	DEFAULT 0
output_tokens	integer	DEFAULT 0
cache_tokens	integer	DEFAULT 0
request_count	integer	DEFAULT 0
models_used	text

Unique: (tenant_id, date, tool_name, host_name)

Indexes: idx_usage_date, idx_usage_date_tool_host(tenant_id, date, tool_name, host_name), idx_usage_tenant_date

usage_summary

Overall summary per tool/host for dashboard.

Column	Type	Notes
tool_name	varchar(50)	NOT NULL
host_name	varchar(100)
days_count	integer	NOT NULL
total_tokens	bigint	NOT NULL
avg_tokens	bigint	NOT NULL
total_requests	integer	NOT NULL
total_input_tokens	bigint	NOT NULL
total_output_tokens	bigint	NOT NULL
first_date	varchar(10)
last_date	varchar(10)

Unique: (tool_name, host_name)

session_stats (Materialized View)

Aggregated from daily_messages where agent_session_id IS NOT NULL. Provides session-level token counts, message counts, and timestamp ranges. This is the schema's single materialized view.

Multi-Tenant

tenants

Column	Type	Notes
id	integer PK
name	text	NOT NULL
slug	text	UNIQUE
status	text	CHECK IN ('active','suspended','trial','inactive')
plan	text	CHECK IN ('free','standard','premium','enterprise')
contact_email / contact_phone / contact_name	text	Contact details
quota / settings	text	JSON blobs
user_count	integer	DEFAULT 0
total_tokens_used	integer	DEFAULT 0
total_requests_made	integer	DEFAULT 0
billing_day / billing_cycle_type / billing_cycle_start / billing_cycle_end	varied	Billing cycle
current_cycle_tokens	bigint	Tokens in the current cycle
over_limit_strategy / over_limit_price_per_token	text/numeric	Over-limit handling
usage_alert_threshold / usage_critical_threshold / alert_silence_hours	numeric	Usage alerting
trial_ends_at / subscription_ends_at	timestamp	Lifecycle
deleted_at	timestamp	Soft delete

tenant_settings (1:1 with tenants)

Column	Type	Notes
tenant_id	integer	UNIQUE FK → tenants(id) ON DELETE CASCADE
content_filter_enabled	boolean	DEFAULT true
audit_log_enabled	boolean	DEFAULT true
audit_log_retention_days	integer	DEFAULT 90
data_retention_days	integer	DEFAULT 365
sso_enabled	boolean	DEFAULT false
sso_provider	text	Default provider name
custom_branding / branding_name / branding_logo_url	varied	Branding
auto_provision_users	text	Auto-provisioning policy
block_sensitive_keyword / sensitive_keyword_match_mode	boolean/text	Sensitive keyword policy
allowed_tools / roi_assumptions	text	JSON config (Issue #2788)

tenant_quotas (1:1 with tenants)

Column	Type	Notes
tenant_id	integer	UNIQUE FK → tenants(id) ON DELETE CASCADE
daily_token_limit	bigint	DEFAULT 1,000,000 (actual token count, Issue #1259)
monthly_token_limit	bigint	DEFAULT 30,000,000 (actual token count, Issue #1259)
daily_request_limit	bigint	DEFAULT 10,000 (Issue #3018)
monthly_request_limit	bigint	DEFAULT 300,000 (Issue #3018)
max_users	integer	DEFAULT 100
max_sessions_per_user	integer	DEFAULT 5

tenant_usage

Column	Type	Notes
id	integer PK
tenant_id	integer	FK → tenants(id) ON DELETE CASCADE
date	date	NOT NULL
tokens_used	integer	DEFAULT 0
requests_made	integer	DEFAULT 0
active_users	integer	DEFAULT 0
new_users	integer	DEFAULT 0

Unique: (tenant_id, date)

SSO

sso_providers

Column	Type	Notes
id	integer PK
name	text	UNIQUE
provider_type	text	NOT NULL (oauth2/oidc/saml)
config	text	NOT NULL (JSON; stores encrypted client_secret_encrypted instead of plaintext client_secret)
tenant_id	integer	FK → tenants(id)
is_active	boolean	DEFAULT true

sso_identities

Column	Type	Notes
id	integer PK
user_id	integer	FK → users(id)
provider_name	text	NOT NULL
provider_user_id	text	NOT NULL
provider_data	text	Raw provider claims

Unique: (provider_name, provider_user_id)

sso_sessions

Column	Type	Notes
id	integer PK
session_token	text	UNIQUE
user_id	integer	FK → users(id)
provider_name	text	NOT NULL
access_token	text
refresh_token	text
expires_at	timestamp

Governance & Compliance

audit_logs

Column	Type	Notes
id	integer PK
timestamp	timestamp	DEFAULT CURRENT_TIMESTAMP
user_id	integer
tenant_id	integer	Resolved from the actor when available for tenant-scoped audit queries
username	text
action	text	NOT NULL
severity	text	DEFAULT 'info'
resource_type	text
resource_id	text
details	text
ip_address	text
user_agent	text
session_id	text
success	boolean	DEFAULT true
error_message	text

Indexes: idx_audit_timestamp, idx_audit_user_id, idx_audit_tenant_id, idx_audit_action, idx_audit_severity

content_filter_rules

Column	Type	Notes
id	integer PK
pattern	text	NOT NULL
type	text	DEFAULT 'keyword'
severity	text	DEFAULT 'medium'
action	text	DEFAULT 'warn'
is_enabled	boolean	DEFAULT true
description	text

security_settings

Key-value store for security configuration.

Column	Type	Notes
id	integer PK
setting_key	varchar(100)	UNIQUE
setting_value	text
description	text

anomaly_status

Anomaly status tracking.

Column	Type	Notes
id	integer PK	Auto-increment
anomaly_id	text	Stable anomaly identity (Issue #2749)
anomaly_type	varchar	Anomaly type
affected_users_hash	varchar	Affected users hash
tenant_id	integer	Tenant scope
status	varchar	Processing status
processed_by	integer	Processor ID
processed_at	timestamp	Processing time
created_at	timestamp	Creation time

insights_reports

AI-generated usage insight reports.

Column	Type	Notes
id	integer PK	Auto-increment
user_id	integer	User ID
start_date	varchar	Start date
end_date	varchar	End date
overall_score	integer	Overall score
overall_assessment	text	Overall assessment
strengths	text	Strengths (JSON)
areas_for_improvement	text	Areas for improvement (JSON)
suggestions	text	Suggestions (JSON)
usage_summary	text	Usage summary (JSON)
model	varchar	Model used
raw_response	text	Raw AI response
created_at	timestamp	Creation time

Security & Permissions

login_attempts

Failed login attempt tracking for account lockout.

Column	Type	Notes
username	varchar	NOT NULL, lookup key
attempt_count	integer	DEFAULT 0
locked_until	timestamp	Lock expiry time

user_permissions

User-level permission overrides.

Column	Type	Notes
id	integer PK	Auto-increment
user_id	integer	NOT NULL
permission	text	NOT NULL
granted_by	integer	Grantor
granted_at	timestamp	DEFAULT CURRENT_TIMESTAMP

Indexes: idx_user_permissions_user, idx_user_permissions_permission

role_permissions

Role permission templates.

Column	Type	Notes
id	integer PK	Auto-increment
role	text	NOT NULL
permission	text	NOT NULL

Indexes: idx_role_permissions_role, idx_role_permissions_permission

Alerts & Quotas

alerts

Column	Type	Notes
id	integer PK
alert_id	text	UNIQUE
alert_type	text	NOT NULL
severity	text	NOT NULL
title	text	NOT NULL
message	text
user_id	integer
username	text
tool_name	text
metadata	text
read	boolean	DEFAULT false
action_url / action_text	text	Inline action link

quota_usage

Column	Type	Notes
id	integer PK
user_id	integer	FK → users(id) ON DELETE CASCADE
date	date	NOT NULL
period	text	DEFAULT 'daily'
tool_name	text	Per-tool quota dimension
tokens_used	integer	DEFAULT 0
requests_used	integer	DEFAULT 0

Unique: (user_id, date, period, tool_name)

quota_alerts

Column	Type	Notes
id	integer PK
user_id	integer	FK → users(id) ON DELETE CASCADE
alert_type	text	NOT NULL
quota_type	text	NOT NULL
period	text	Quota period
threshold	real	NOT NULL
current_usage	integer	NOT NULL
quota_limit	integer	NOT NULL
percentage	real	NOT NULL
message	text
acknowledged	boolean	DEFAULT false
acknowledged_at / acknowledged_by	timestamp/integer	Acknowledgment tracking

notification_preferences

Column	Type	Notes
user_id	integer	PK
email_enabled	boolean	DEFAULT true
push_enabled	boolean	DEFAULT true
webhook_url	text	Personal webhook
alert_types	text	Subscribed alert types
min_severity	text	DEFAULT 'warning'
notification_email	text	Dedicated notification address
email_verified	boolean	Address verification
dingtalk_webhook_secret	text	Encrypted DingTalk secret

Workspace & Projects

projects

Column	Type	Notes
id	integer PK
tenant_id	integer	DEFAULT 1; used for tenant-scoped project lookup and uniqueness
path	varchar(500)	UNIQUE per active (tenant_id, path)
name	varchar(200)
description	text
created_by	integer
is_active	boolean	DEFAULT true
is_shared	boolean	DEFAULT false
permission_status	text	Shared-project permission setup status (Issue #2746)
permission_task_id	text	Associated permission_tasks task

Indexes: idx_projects_created_by, idx_projects_is_active, idx_projects_path(tenant_id, path), idx_projects_tenant_created_by

user_projects

Column	Type	Notes
id	integer PK
user_id	integer	NOT NULL
project_id	integer	NOT NULL
first_access_at / last_access_at	timestamp	Access window
total_sessions	integer	DEFAULT 0
total_tokens	bigint	DEFAULT 0
total_requests	integer	DEFAULT 0
total_duration_seconds	integer	DEFAULT 0

Unique: (user_id, project_id)

prompt_templates

Column	Type	Notes
id	integer PK
name	text	NOT NULL, UNIQUE (Issue #2577)
description	text
category	text	DEFAULT 'general'
content	text	NOT NULL
variables	text
tags	text
author_id	integer
author_name	text
is_public	boolean	DEFAULT false
is_featured	boolean	DEFAULT false
use_count	integer	DEFAULT 0

Remote Workspace

remote_machines

Remote workspace machine registry.

Column	Type	Notes
id	integer PK	Auto-increment
machine_id	text	Machine unique identifier
machine_name	text	Machine name
hostname	text	Hostname
os_type	text	OS type
os_version	text	OS version
ip_address	text	IP address
status	text	Status
agent_version	text	Agent version
capabilities	text	Capabilities (JSON)
cli_path	text	CLI path
work_dir	text	Working directory
tenant_id	integer	Tenant ID
created_by	integer	Creator ID
created_at / updated_at	timestamp	Timestamps
last_heartbeat	timestamp	Last heartbeat time
legacy_mode	boolean	Legacy protocol flag
token_revoke_timeout	integer	Delayed revocation window (Issue #2499)

remote_runtime_commands

Persistent HTTP-polling command queue for remote agents.

Column	Type	Notes
id	integer PK
command_id	varchar(64)	UNIQUE; request_id for command-response calls
machine_id	text	Target remote machine
session_id	text	Optional remote session
command_type	text	Command name
payload	text	JSON command payload
status	varchar(32)	pending, delivered, or responded
response_payload	text	JSON response payload for synchronous commands
created_at	timestamp	Creation time
delivered_at	timestamp	Agent poll claim time
responded_at	timestamp	Response arrival time
expires_at	timestamp	Retention cutoff

Indexes: idx_remote_runtime_commands_machine_status, idx_remote_runtime_commands_expires

remote_runtime_outputs

Persistent SSE replay buffer for remote session output.

Column	Type	Notes
id	integer PK
session_id	text	Remote session ID
event_index	integer	Monotonic per-session output index
stream	text	stdout, stderr, system, permission, request_state
payload	text	JSON output payload
created_at	timestamp	Creation time
expires_at	timestamp	Retention cutoff

Unique: (session_id, event_index)

Indexes: idx_remote_runtime_outputs_session_index, idx_remote_runtime_outputs_expires

machine_assignments

User-to-remote-machine assignment relationships.

Column	Type	Notes
id	integer PK	Auto-increment
machine_id	text	Machine ID
user_id	integer	User ID
permission	text	Permission level
granted_by	integer	Grantor ID
granted_at	timestamp	Grant time

api_key_store

Encrypted API key storage.

Column	Type	Notes
id	integer PK	Auto-increment
tenant_id	integer	Tenant ID
provider	text	AI service provider
key_name	text	Key name
encrypted_key	text	Encrypted key
key_hash	text	Key hash
base_url	text	API base URL
is_active	boolean	DEFAULT true
scope / priority / weight	text/integer	Routing attributes
resolved_ips / resolved_at	text/timestamp	Pinned egress IPs (Issue #1894)
created_by	integer	Creator ID
created_at / updated_at	timestamp	Timestamps
cli_tools	text	CLI tools configuration
cli_settings	text	CLI settings

Collaboration

teams

Column	Type	Notes
id	integer PK
team_id	text	UNIQUE
name	text	NOT NULL
description	text
owner_id	integer
settings	text	Team settings (JSON)

team_members

Column	Type	Notes
id	integer PK
team_id	text	NOT NULL
user_id	integer	NOT NULL
username	text	Denormalized member name
role	text	DEFAULT 'member'
joined_at	timestamp

Unique: (team_id, user_id)

shared_sessions

Column	Type	Notes
id	integer PK
share_id	text	UNIQUE
session_id	text	NOT NULL
shared_by	integer
shared_by_name	text	Denormalized sharer name
permission	text	DEFAULT 'view'
share_type / target_id / target_name	text	Share target (user/team/link)
allow_comments	boolean	DEFAULT true
allow_copy	boolean	DEFAULT true
expires_at	timestamp
access_count	integer	DEFAULT 0
last_accessed	timestamp

knowledge_base

Column	Type	Notes
id	integer PK
entry_id	text	UNIQUE
team_id	text
title	text	NOT NULL
content	text
category	text	DEFAULT 'general'
tags	text
author_id	integer
author_name	text
is_published	boolean	DEFAULT false
view_count	integer	DEFAULT 0

annotations

Column	Type	Notes
id	integer PK
annotation_id	text	UNIQUE
session_id	text	NOT NULL
message_id	text
user_id	integer
username	text	Denormalized author name
content	text
annotation_type	text	DEFAULT 'comment'
parent_id	text	Threading

Sync & Retention Snapshots

sync_events

Column	Type	Notes
id	integer PK
event_id	text	UNIQUE
event_type	text	NOT NULL
source	text
session_id	text
user_id	integer
tool_name	text
data	text
metadata	text

retention_history

Column	Type	Notes
id	integer PK
timestamp	timestamp	DEFAULT CURRENT_TIMESTAMP
report_data	text	NOT NULL

Foreign Key Summary

Child Table	Column	Parent Table	On Delete
users	tenant_id	tenants	SET NULL
sessions	user_id	users	CASCADE
web_user_auth_sessions	user_id	users	—
user_tool_accounts	user_id	users	CASCADE
user_daily_stats	user_id	users	CASCADE
session_messages	session_id	agent_sessions	—
quota_usage	user_id	users	CASCADE
quota_alerts	user_id	users	CASCADE
sso_identities	user_id	users	—
sso_sessions	user_id	users	—
sso_providers	tenant_id	tenants	—
tenant_quotas	tenant_id	tenants	CASCADE
tenant_settings	tenant_id	tenants	CASCADE
tenant_usage	tenant_id	tenants	CASCADE
anomaly_status	processed_by	users	—
insights_reports	user_id	users	—

Cross-Database Compatibility

See DATABASE_CONVENTIONS.md for naming conventions. The adapt_sql() function in app/repositories/database.py handles placeholder conversion (? → %s) automatically.

Related documentation

• schema/schema-postgres.sql — authoritative column-level reference (104 tables + 1 materialized view)
• ../contracts/WORKSPACE_SESSION_DATA_CONTRACT.md — data boundary contract for agent_sessions, session_messages, daily_messages
• SCHEMA_MIGRATION_GUIDE.md — how schema changes ship
• DATABASE_CONVENTIONS.md — field naming and type conventions