xz
1708 lines
74 KiB
SQL
1708 lines
74 KiB
SQL
PRAGMA foreign_keys = ON;
|
|
|
|
CREATE TABLE IF NOT EXISTS users (
|
|
id TEXT PRIMARY KEY,
|
|
display_name TEXT NOT NULL,
|
|
email TEXT NOT NULL UNIQUE,
|
|
avatar_color TEXT NOT NULL DEFAULT '#20252b',
|
|
status TEXT NOT NULL DEFAULT 'active' CHECK (status IN ('active', 'invited', 'suspended')),
|
|
created_at TEXT NOT NULL,
|
|
updated_at TEXT NOT NULL
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS system_admins (
|
|
user_id TEXT PRIMARY KEY REFERENCES users(id) ON DELETE CASCADE,
|
|
role_key TEXT NOT NULL DEFAULT 'system_admin',
|
|
status TEXT NOT NULL DEFAULT 'active' CHECK (status IN ('active', 'suspended')),
|
|
created_at TEXT NOT NULL,
|
|
updated_at TEXT NOT NULL
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS user_credentials (
|
|
user_id TEXT PRIMARY KEY REFERENCES users(id) ON DELETE CASCADE,
|
|
password_salt TEXT NOT NULL,
|
|
password_hash TEXT NOT NULL,
|
|
last_login_at TEXT,
|
|
failed_attempts INTEGER NOT NULL DEFAULT 0,
|
|
locked_until TEXT,
|
|
created_at TEXT NOT NULL,
|
|
updated_at TEXT NOT NULL
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS auth_devices (
|
|
id TEXT PRIMARY KEY,
|
|
user_id TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
|
device_key_hash TEXT NOT NULL,
|
|
fingerprint_hash TEXT NOT NULL DEFAULT '',
|
|
label TEXT NOT NULL DEFAULT '浏览器设备',
|
|
first_seen_at TEXT NOT NULL,
|
|
last_seen_at TEXT NOT NULL,
|
|
last_ip_address TEXT NOT NULL DEFAULT '',
|
|
last_user_agent TEXT NOT NULL DEFAULT '',
|
|
trusted_at TEXT,
|
|
revoked_at TEXT,
|
|
created_at TEXT NOT NULL,
|
|
updated_at TEXT NOT NULL,
|
|
UNIQUE (user_id, device_key_hash)
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS auth_sessions (
|
|
id TEXT PRIMARY KEY,
|
|
token_hash TEXT NOT NULL UNIQUE,
|
|
user_id TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
|
expires_at TEXT NOT NULL,
|
|
ip_address TEXT NOT NULL DEFAULT '',
|
|
user_agent TEXT NOT NULL DEFAULT '',
|
|
created_at TEXT NOT NULL,
|
|
last_seen_at TEXT NOT NULL,
|
|
revoked_at TEXT,
|
|
device_id TEXT REFERENCES auth_devices(id) ON DELETE SET NULL,
|
|
risk_level TEXT NOT NULL DEFAULT 'medium',
|
|
risk_score INTEGER NOT NULL DEFAULT 50
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_auth_devices_user_last_seen ON auth_devices(user_id, last_seen_at DESC);
|
|
CREATE INDEX IF NOT EXISTS idx_auth_devices_key_hash ON auth_devices(user_id, device_key_hash);
|
|
|
|
CREATE TABLE IF NOT EXISTS user_mfa_methods (
|
|
id TEXT PRIMARY KEY,
|
|
user_id TEXT NOT NULL UNIQUE REFERENCES users(id) ON DELETE CASCADE,
|
|
method_type TEXT NOT NULL DEFAULT 'totp' CHECK (method_type IN ('totp')),
|
|
label TEXT NOT NULL DEFAULT '身份验证器',
|
|
secret_ciphertext TEXT NOT NULL,
|
|
enabled INTEGER NOT NULL DEFAULT 0,
|
|
setup_expires_at TEXT,
|
|
last_used_at TEXT,
|
|
created_at TEXT NOT NULL,
|
|
updated_at TEXT NOT NULL
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS auth_mfa_challenges (
|
|
id TEXT PRIMARY KEY,
|
|
challenge_hash TEXT NOT NULL UNIQUE,
|
|
user_id TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
|
expires_at TEXT NOT NULL,
|
|
attempts INTEGER NOT NULL DEFAULT 0,
|
|
created_at TEXT NOT NULL,
|
|
consumed_at TEXT
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS auth_mfa_enrollment_challenges (
|
|
id TEXT PRIMARY KEY,
|
|
challenge_hash TEXT NOT NULL UNIQUE,
|
|
user_id TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
|
expires_at TEXT NOT NULL,
|
|
created_at TEXT NOT NULL,
|
|
consumed_at TEXT
|
|
);
|
|
|
|
-- Authentication security events are separate from organization audit logs.
|
|
-- They can contain a nullable user_id for failed logins against unknown emails.
|
|
CREATE TABLE IF NOT EXISTS auth_security_events (
|
|
id TEXT PRIMARY KEY,
|
|
user_id TEXT REFERENCES users(id) ON DELETE SET NULL,
|
|
event_type TEXT NOT NULL,
|
|
result TEXT NOT NULL DEFAULT 'success',
|
|
ip_address TEXT NOT NULL DEFAULT '',
|
|
user_agent TEXT NOT NULL DEFAULT '',
|
|
metadata_json TEXT NOT NULL DEFAULT '{}',
|
|
created_at TEXT NOT NULL
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_auth_security_events_user_created ON auth_security_events(user_id, created_at DESC);
|
|
CREATE INDEX IF NOT EXISTS idx_auth_security_events_type_created ON auth_security_events(event_type, created_at DESC);
|
|
|
|
-- Enterprise identity governance. Secrets are never stored here; providers and
|
|
-- directory connectors reference environment variables or one-way token hashes.
|
|
CREATE TABLE IF NOT EXISTS identity_policies (
|
|
id TEXT PRIMARY KEY,
|
|
password_login_enabled INTEGER NOT NULL DEFAULT 1,
|
|
mfa_required_for_admins INTEGER NOT NULL DEFAULT 0,
|
|
mfa_required_for_all INTEGER NOT NULL DEFAULT 0,
|
|
sso_enabled INTEGER NOT NULL DEFAULT 0,
|
|
local_login_fallback INTEGER NOT NULL DEFAULT 1,
|
|
session_ttl_hours INTEGER NOT NULL DEFAULT 12,
|
|
max_sessions_per_user INTEGER NOT NULL DEFAULT 10,
|
|
updated_by TEXT REFERENCES users(id),
|
|
created_at TEXT NOT NULL,
|
|
updated_at TEXT NOT NULL
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS identity_providers (
|
|
id TEXT PRIMARY KEY,
|
|
name TEXT NOT NULL,
|
|
kind TEXT NOT NULL DEFAULT 'oidc' CHECK (kind IN ('oidc', 'saml')),
|
|
organization_id TEXT REFERENCES organizations(id) ON DELETE CASCADE,
|
|
workspace_id TEXT REFERENCES workspaces(id) ON DELETE SET NULL,
|
|
issuer_url TEXT NOT NULL DEFAULT '',
|
|
authorization_url TEXT NOT NULL DEFAULT '',
|
|
token_url TEXT NOT NULL DEFAULT '',
|
|
userinfo_url TEXT NOT NULL DEFAULT '',
|
|
jwks_url TEXT NOT NULL DEFAULT '',
|
|
entry_point TEXT NOT NULL DEFAULT '',
|
|
idp_cert_ref TEXT NOT NULL DEFAULT '',
|
|
sp_issuer TEXT NOT NULL DEFAULT '',
|
|
audience TEXT NOT NULL DEFAULT '',
|
|
saml_name_id_format TEXT NOT NULL DEFAULT 'urn:oasis:names:tc:SAML:1.1:nameid-format:emailAddress',
|
|
want_assertions_signed INTEGER NOT NULL DEFAULT 1,
|
|
want_authn_response_signed INTEGER NOT NULL DEFAULT 1,
|
|
validate_in_response_to TEXT NOT NULL DEFAULT 'ifPresent',
|
|
client_id TEXT NOT NULL DEFAULT '',
|
|
client_secret_ref TEXT NOT NULL DEFAULT '',
|
|
scopes_json TEXT NOT NULL DEFAULT '["openid", "profile", "email"]',
|
|
claim_mapping_json TEXT NOT NULL DEFAULT '{"email":"email","displayName":"name","externalId":"sub"}',
|
|
auto_provision INTEGER NOT NULL DEFAULT 1,
|
|
default_role_key TEXT NOT NULL DEFAULT 'org_member',
|
|
default_workspace_role_key TEXT NOT NULL DEFAULT 'writer',
|
|
enabled INTEGER NOT NULL DEFAULT 0,
|
|
status TEXT NOT NULL DEFAULT 'not-configured',
|
|
last_probe_at TEXT,
|
|
error_message TEXT NOT NULL DEFAULT '',
|
|
created_by TEXT REFERENCES users(id),
|
|
created_at TEXT NOT NULL,
|
|
updated_at TEXT NOT NULL
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS oidc_login_states (
|
|
id TEXT PRIMARY KEY,
|
|
state_hash TEXT NOT NULL UNIQUE,
|
|
provider_id TEXT NOT NULL REFERENCES identity_providers(id) ON DELETE CASCADE,
|
|
nonce_hash TEXT NOT NULL,
|
|
code_verifier_ciphertext TEXT NOT NULL,
|
|
redirect_uri TEXT NOT NULL,
|
|
return_to TEXT NOT NULL DEFAULT '/',
|
|
selection_json TEXT NOT NULL DEFAULT '{}',
|
|
ip_address TEXT NOT NULL DEFAULT '',
|
|
user_agent TEXT NOT NULL DEFAULT '',
|
|
expires_at TEXT NOT NULL,
|
|
created_at TEXT NOT NULL,
|
|
consumed_at TEXT
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS saml_login_states (
|
|
id TEXT PRIMARY KEY,
|
|
relay_state_hash TEXT NOT NULL UNIQUE,
|
|
request_id TEXT UNIQUE,
|
|
request_issue_instant TEXT,
|
|
provider_id TEXT NOT NULL REFERENCES identity_providers(id) ON DELETE CASCADE,
|
|
return_to TEXT NOT NULL DEFAULT '/',
|
|
selection_json TEXT NOT NULL DEFAULT '{}',
|
|
ip_address TEXT NOT NULL DEFAULT '',
|
|
user_agent TEXT NOT NULL DEFAULT '',
|
|
expires_at TEXT NOT NULL,
|
|
created_at TEXT NOT NULL,
|
|
consumed_at TEXT
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS external_identities (
|
|
id TEXT PRIMARY KEY,
|
|
provider_id TEXT NOT NULL REFERENCES identity_providers(id) ON DELETE CASCADE,
|
|
user_id TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
|
subject TEXT NOT NULL,
|
|
issuer TEXT NOT NULL DEFAULT '',
|
|
email_at_login TEXT NOT NULL DEFAULT '',
|
|
created_at TEXT NOT NULL,
|
|
updated_at TEXT NOT NULL,
|
|
UNIQUE (provider_id, subject)
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS auth_sso_tickets (
|
|
id TEXT PRIMARY KEY,
|
|
ticket_hash TEXT NOT NULL UNIQUE,
|
|
user_id TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
|
selection_json TEXT NOT NULL DEFAULT '{}',
|
|
ip_address TEXT NOT NULL DEFAULT '',
|
|
user_agent TEXT NOT NULL DEFAULT '',
|
|
expires_at TEXT NOT NULL,
|
|
created_at TEXT NOT NULL,
|
|
consumed_at TEXT
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS directory_syncs (
|
|
id TEXT PRIMARY KEY,
|
|
name TEXT NOT NULL,
|
|
kind TEXT NOT NULL DEFAULT 'scim',
|
|
organization_id TEXT REFERENCES organizations(id) ON DELETE CASCADE,
|
|
provider_id TEXT REFERENCES identity_providers(id) ON DELETE SET NULL,
|
|
endpoint TEXT NOT NULL DEFAULT '',
|
|
token_hint TEXT NOT NULL DEFAULT '',
|
|
enabled INTEGER NOT NULL DEFAULT 0,
|
|
sync_mode TEXT NOT NULL DEFAULT 'provision-and-deprovision',
|
|
schedule TEXT NOT NULL DEFAULT 'manual',
|
|
last_sync_at TEXT,
|
|
last_status TEXT NOT NULL DEFAULT 'never-run',
|
|
last_synced_count INTEGER NOT NULL DEFAULT 0,
|
|
error_message TEXT NOT NULL DEFAULT '',
|
|
created_by TEXT REFERENCES users(id),
|
|
created_at TEXT NOT NULL,
|
|
updated_at TEXT NOT NULL
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS directory_sync_tokens (
|
|
id TEXT PRIMARY KEY,
|
|
directory_sync_id TEXT NOT NULL REFERENCES directory_syncs(id) ON DELETE CASCADE,
|
|
token_hash TEXT NOT NULL UNIQUE,
|
|
token_hint TEXT NOT NULL,
|
|
created_by TEXT REFERENCES users(id),
|
|
last_used_at TEXT,
|
|
created_at TEXT NOT NULL,
|
|
revoked_at TEXT
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS organizations (
|
|
id TEXT PRIMARY KEY,
|
|
name TEXT NOT NULL,
|
|
slug TEXT NOT NULL UNIQUE,
|
|
owner_user_id TEXT NOT NULL REFERENCES users(id),
|
|
deployment_mode TEXT NOT NULL DEFAULT 'private-local',
|
|
status TEXT NOT NULL DEFAULT 'active',
|
|
created_at TEXT NOT NULL,
|
|
updated_at TEXT NOT NULL
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS organization_members (
|
|
id TEXT PRIMARY KEY,
|
|
organization_id TEXT NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
|
|
user_id TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
|
role_key TEXT NOT NULL,
|
|
status TEXT NOT NULL DEFAULT 'active' CHECK (status IN ('active', 'invited', 'suspended')),
|
|
invited_at TEXT,
|
|
joined_at TEXT,
|
|
created_at TEXT NOT NULL,
|
|
updated_at TEXT NOT NULL,
|
|
UNIQUE (organization_id, user_id)
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS workspaces (
|
|
id TEXT PRIMARY KEY,
|
|
organization_id TEXT NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
|
|
name TEXT NOT NULL,
|
|
slug TEXT NOT NULL,
|
|
description TEXT NOT NULL DEFAULT '',
|
|
status TEXT NOT NULL DEFAULT 'active',
|
|
created_at TEXT NOT NULL,
|
|
updated_at TEXT NOT NULL,
|
|
UNIQUE (organization_id, slug)
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS workspace_members (
|
|
id TEXT PRIMARY KEY,
|
|
workspace_id TEXT NOT NULL REFERENCES workspaces(id) ON DELETE CASCADE,
|
|
user_id TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
|
role_key TEXT NOT NULL,
|
|
access_mode TEXT NOT NULL DEFAULT 'all',
|
|
status TEXT NOT NULL DEFAULT 'active' CHECK (status IN ('active', 'invited', 'suspended')),
|
|
created_at TEXT NOT NULL,
|
|
updated_at TEXT NOT NULL,
|
|
UNIQUE (workspace_id, user_id)
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS projects (
|
|
id TEXT PRIMARY KEY,
|
|
workspace_id TEXT NOT NULL REFERENCES workspaces(id) ON DELETE CASCADE,
|
|
name TEXT NOT NULL,
|
|
type TEXT NOT NULL DEFAULT 'AI 漫剧',
|
|
template_id TEXT NOT NULL DEFAULT 'ai-manhua-drama',
|
|
status TEXT NOT NULL DEFAULT 'production',
|
|
owner_user_id TEXT NOT NULL REFERENCES users(id),
|
|
visibility TEXT NOT NULL DEFAULT 'workspace',
|
|
readiness INTEGER NOT NULL DEFAULT 0 CHECK (readiness BETWEEN 0 AND 100),
|
|
risk TEXT NOT NULL DEFAULT 'low',
|
|
archived_at TEXT,
|
|
archived_by TEXT REFERENCES users(id) ON DELETE SET NULL,
|
|
archived_from_status TEXT,
|
|
created_at TEXT NOT NULL,
|
|
updated_at TEXT NOT NULL
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS project_members (
|
|
id TEXT PRIMARY KEY,
|
|
project_id TEXT NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
|
|
user_id TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
|
role_key TEXT NOT NULL,
|
|
status TEXT NOT NULL DEFAULT 'active',
|
|
created_at TEXT NOT NULL,
|
|
updated_at TEXT NOT NULL,
|
|
UNIQUE (project_id, user_id)
|
|
);
|
|
|
|
-- Persistent collaboration tasks are separate from derived work-items. A
|
|
-- task has an owner, lifecycle, due date and audit trail inside one project.
|
|
CREATE TABLE IF NOT EXISTS project_tasks (
|
|
id TEXT PRIMARY KEY,
|
|
organization_id TEXT NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
|
|
workspace_id TEXT NOT NULL REFERENCES workspaces(id) ON DELETE CASCADE,
|
|
project_id TEXT NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
|
|
title TEXT NOT NULL,
|
|
description TEXT NOT NULL DEFAULT '',
|
|
kind TEXT NOT NULL DEFAULT 'production',
|
|
status TEXT NOT NULL DEFAULT 'open' CHECK (status IN ('open', 'in_progress', 'blocked', 'done', 'cancelled')),
|
|
priority TEXT NOT NULL DEFAULT 'medium' CHECK (priority IN ('high', 'medium', 'low')),
|
|
assignee_user_id TEXT REFERENCES users(id) ON DELETE SET NULL,
|
|
target_tab TEXT NOT NULL DEFAULT 'creator-home',
|
|
target_id TEXT NOT NULL DEFAULT '',
|
|
due_at TEXT,
|
|
completed_at TEXT,
|
|
created_by TEXT NOT NULL REFERENCES users(id),
|
|
created_at TEXT NOT NULL,
|
|
updated_at TEXT NOT NULL
|
|
);
|
|
|
|
-- Task collaboration records stay inside the same tenant and project scope.
|
|
-- Links point to existing production objects; file links are local path
|
|
-- references only and never imply an external/cloud upload.
|
|
CREATE TABLE IF NOT EXISTS task_comments (
|
|
id TEXT PRIMARY KEY,
|
|
organization_id TEXT NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
|
|
workspace_id TEXT NOT NULL REFERENCES workspaces(id) ON DELETE CASCADE,
|
|
project_id TEXT NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
|
|
task_id TEXT NOT NULL REFERENCES project_tasks(id) ON DELETE CASCADE,
|
|
parent_comment_id TEXT REFERENCES task_comments(id) ON DELETE CASCADE,
|
|
author_user_id TEXT NOT NULL REFERENCES users(id),
|
|
body TEXT NOT NULL,
|
|
mentions_json TEXT NOT NULL DEFAULT '[]',
|
|
created_at TEXT NOT NULL,
|
|
updated_at TEXT NOT NULL
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS task_links (
|
|
id TEXT PRIMARY KEY,
|
|
organization_id TEXT NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
|
|
workspace_id TEXT NOT NULL REFERENCES workspaces(id) ON DELETE CASCADE,
|
|
project_id TEXT NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
|
|
task_id TEXT NOT NULL REFERENCES project_tasks(id) ON DELETE CASCADE,
|
|
link_type TEXT NOT NULL,
|
|
target_id TEXT NOT NULL,
|
|
label TEXT NOT NULL DEFAULT '',
|
|
metadata_json TEXT NOT NULL DEFAULT '{}',
|
|
created_by TEXT NOT NULL REFERENCES users(id),
|
|
created_at TEXT NOT NULL,
|
|
UNIQUE (task_id, link_type, target_id)
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS invitations (
|
|
id TEXT PRIMARY KEY,
|
|
organization_id TEXT NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
|
|
workspace_id TEXT REFERENCES workspaces(id) ON DELETE CASCADE,
|
|
project_id TEXT REFERENCES projects(id) ON DELETE CASCADE,
|
|
email TEXT NOT NULL,
|
|
role_key TEXT NOT NULL,
|
|
invited_by TEXT NOT NULL REFERENCES users(id),
|
|
status TEXT NOT NULL DEFAULT 'pending' CHECK (status IN ('pending', 'accepted', 'expired', 'revoked')),
|
|
expires_at TEXT NOT NULL,
|
|
created_at TEXT NOT NULL,
|
|
token_hash TEXT,
|
|
token_hint TEXT,
|
|
accepted_user_id TEXT REFERENCES users(id),
|
|
accepted_at TEXT,
|
|
revoked_at TEXT
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS roles (
|
|
key TEXT PRIMARY KEY,
|
|
scope TEXT NOT NULL CHECK (scope IN ('organization', 'workspace', 'project')),
|
|
name TEXT NOT NULL,
|
|
description TEXT NOT NULL DEFAULT ''
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS permissions (
|
|
key TEXT PRIMARY KEY,
|
|
description TEXT NOT NULL DEFAULT ''
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS role_permissions (
|
|
role_key TEXT NOT NULL REFERENCES roles(key) ON DELETE CASCADE,
|
|
permission_key TEXT NOT NULL REFERENCES permissions(key) ON DELETE CASCADE,
|
|
PRIMARY KEY (role_key, permission_key)
|
|
);
|
|
|
|
-- Organization-scoped policy overrides keep the built-in role catalog stable
|
|
-- while allowing each tenant to grant or revoke non-system permissions.
|
|
CREATE TABLE IF NOT EXISTS organization_role_permissions (
|
|
organization_id TEXT NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
|
|
role_key TEXT NOT NULL REFERENCES roles(key) ON DELETE CASCADE,
|
|
permission_key TEXT NOT NULL REFERENCES permissions(key) ON DELETE CASCADE,
|
|
effect TEXT NOT NULL CHECK (effect IN ('grant', 'revoke')),
|
|
updated_by TEXT REFERENCES users(id),
|
|
created_at TEXT NOT NULL,
|
|
updated_at TEXT NOT NULL,
|
|
PRIMARY KEY (organization_id, role_key, permission_key)
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS billing_accounts (
|
|
id TEXT PRIMARY KEY,
|
|
organization_id TEXT NOT NULL UNIQUE REFERENCES organizations(id) ON DELETE CASCADE,
|
|
plan_name TEXT NOT NULL,
|
|
billing_cycle TEXT NOT NULL DEFAULT 'monthly' CHECK (billing_cycle IN ('monthly', 'quarterly', 'annual')),
|
|
currency TEXT NOT NULL DEFAULT 'CNY',
|
|
base_fee REAL NOT NULL DEFAULT 0,
|
|
seat_unit_price REAL NOT NULL DEFAULT 0,
|
|
storage_unit_price REAL NOT NULL DEFAULT 0,
|
|
clip_unit_price REAL NOT NULL DEFAULT 0,
|
|
seat_limit INTEGER NOT NULL DEFAULT 5,
|
|
storage_gb INTEGER NOT NULL DEFAULT 100,
|
|
monthly_clip_quota INTEGER NOT NULL DEFAULT 100,
|
|
quota_warning_percent INTEGER NOT NULL DEFAULT 80,
|
|
local_runner_only INTEGER NOT NULL DEFAULT 1,
|
|
cloud_connectors_require_approval INTEGER NOT NULL DEFAULT 1,
|
|
current_period_start TEXT,
|
|
current_period_end TEXT,
|
|
next_invoice_at TEXT,
|
|
created_at TEXT NOT NULL,
|
|
updated_at TEXT NOT NULL
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS billing_account_events (
|
|
id TEXT PRIMARY KEY,
|
|
organization_id TEXT NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
|
|
billing_account_id TEXT NOT NULL REFERENCES billing_accounts(id) ON DELETE CASCADE,
|
|
event_type TEXT NOT NULL,
|
|
previous_json TEXT NOT NULL DEFAULT '{}',
|
|
next_json TEXT NOT NULL DEFAULT '{}',
|
|
actor_user_id TEXT REFERENCES users(id),
|
|
created_at TEXT NOT NULL
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS subscription_plan_templates (
|
|
id TEXT PRIMARY KEY,
|
|
tier_key TEXT NOT NULL UNIQUE,
|
|
name TEXT NOT NULL,
|
|
description TEXT NOT NULL DEFAULT '',
|
|
billing_cycle TEXT NOT NULL DEFAULT 'monthly' CHECK (billing_cycle IN ('monthly', 'quarterly', 'annual')),
|
|
currency TEXT NOT NULL DEFAULT 'CNY',
|
|
base_fee REAL NOT NULL DEFAULT 0,
|
|
seat_limit INTEGER NOT NULL DEFAULT 1,
|
|
storage_gb INTEGER NOT NULL DEFAULT 1,
|
|
monthly_clip_quota INTEGER NOT NULL DEFAULT 1,
|
|
limits_json TEXT NOT NULL DEFAULT '{}',
|
|
features_json TEXT NOT NULL DEFAULT '{}',
|
|
connector_policy_json TEXT NOT NULL DEFAULT '{}',
|
|
support_sla TEXT NOT NULL DEFAULT '',
|
|
status TEXT NOT NULL DEFAULT 'active' CHECK (status IN ('active', 'archived')),
|
|
created_at TEXT NOT NULL,
|
|
updated_at TEXT NOT NULL
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS organization_entitlements (
|
|
id TEXT PRIMARY KEY,
|
|
organization_id TEXT NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
|
|
entitlement_key TEXT NOT NULL,
|
|
label TEXT NOT NULL,
|
|
category TEXT NOT NULL DEFAULT 'general',
|
|
limit_value REAL NOT NULL DEFAULT 0,
|
|
unit TEXT NOT NULL DEFAULT '项',
|
|
enabled INTEGER NOT NULL DEFAULT 1,
|
|
enforcement TEXT NOT NULL DEFAULT 'block' CHECK (enforcement IN ('block', 'warn', 'off')),
|
|
source TEXT NOT NULL DEFAULT 'plan' CHECK (source IN ('plan', 'override')),
|
|
override_reason TEXT NOT NULL DEFAULT '',
|
|
metadata_json TEXT NOT NULL DEFAULT '{}',
|
|
created_at TEXT NOT NULL,
|
|
updated_at TEXT NOT NULL,
|
|
UNIQUE (organization_id, entitlement_key)
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS organization_policies (
|
|
id TEXT PRIMARY KEY,
|
|
organization_id TEXT NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
|
|
policy_key TEXT NOT NULL,
|
|
category TEXT NOT NULL DEFAULT 'production',
|
|
label TEXT NOT NULL,
|
|
description TEXT NOT NULL DEFAULT '',
|
|
status TEXT NOT NULL DEFAULT 'enforced' CHECK (status IN ('draft', 'enforced', 'monitor', 'disabled')),
|
|
enforcement TEXT NOT NULL DEFAULT 'block' CHECK (enforcement IN ('block', 'approval', 'warn', 'off')),
|
|
severity TEXT NOT NULL DEFAULT 'high' CHECK (severity IN ('critical', 'high', 'medium', 'low')),
|
|
value_json TEXT NOT NULL DEFAULT '{}',
|
|
applies_to_json TEXT NOT NULL DEFAULT '[]',
|
|
approval_required INTEGER NOT NULL DEFAULT 0,
|
|
updated_by TEXT REFERENCES users(id),
|
|
created_at TEXT NOT NULL,
|
|
updated_at TEXT NOT NULL,
|
|
UNIQUE (organization_id, policy_key)
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS organization_policy_evaluations (
|
|
id TEXT PRIMARY KEY,
|
|
organization_id TEXT NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
|
|
workspace_id TEXT REFERENCES workspaces(id) ON DELETE SET NULL,
|
|
project_id TEXT REFERENCES projects(id) ON DELETE SET NULL,
|
|
policy_key TEXT NOT NULL,
|
|
subject_type TEXT NOT NULL DEFAULT 'generation_request',
|
|
subject_id TEXT NOT NULL DEFAULT '',
|
|
result TEXT NOT NULL DEFAULT 'pass' CHECK (result IN ('pass', 'warn', 'approval_required', 'block')),
|
|
reason TEXT NOT NULL DEFAULT '',
|
|
evidence_json TEXT NOT NULL DEFAULT '{}',
|
|
created_by TEXT REFERENCES users(id),
|
|
created_at TEXT NOT NULL
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS commercial_approval_requests (
|
|
id TEXT PRIMARY KEY,
|
|
organization_id TEXT NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
|
|
workspace_id TEXT REFERENCES workspaces(id) ON DELETE SET NULL,
|
|
project_id TEXT REFERENCES projects(id) ON DELETE SET NULL,
|
|
request_type TEXT NOT NULL CHECK (request_type IN ('entitlement_overage', 'feature_enablement', 'budget_increase', 'external_connector', 'compliance_review', 'delivery_exception', 'storage_retention')),
|
|
title TEXT NOT NULL,
|
|
status TEXT NOT NULL DEFAULT 'submitted' CHECK (status IN ('submitted', 'approved', 'rejected', 'cancelled')),
|
|
priority TEXT NOT NULL DEFAULT 'medium' CHECK (priority IN ('low', 'medium', 'high', 'urgent')),
|
|
target_key TEXT NOT NULL DEFAULT '',
|
|
current_value REAL NOT NULL DEFAULT 0,
|
|
requested_value REAL NOT NULL DEFAULT 0,
|
|
unit TEXT NOT NULL DEFAULT '',
|
|
business_reason TEXT NOT NULL DEFAULT '',
|
|
risk_assessment_json TEXT NOT NULL DEFAULT '{}',
|
|
evidence_json TEXT NOT NULL DEFAULT '{}',
|
|
decision_note TEXT NOT NULL DEFAULT '',
|
|
effect_json TEXT NOT NULL DEFAULT '{}',
|
|
requester_user_id TEXT NOT NULL REFERENCES users(id),
|
|
reviewer_user_id TEXT REFERENCES users(id),
|
|
reviewed_at TEXT,
|
|
expires_at TEXT,
|
|
created_at TEXT NOT NULL,
|
|
updated_at TEXT NOT NULL
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS customers (
|
|
id TEXT PRIMARY KEY,
|
|
organization_id TEXT NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
|
|
name TEXT NOT NULL,
|
|
legal_name TEXT NOT NULL DEFAULT '',
|
|
code TEXT NOT NULL,
|
|
customer_type TEXT NOT NULL DEFAULT 'platform' CHECK (customer_type IN ('platform', 'brand', 'agency', 'distributor', 'internal')),
|
|
status TEXT NOT NULL DEFAULT 'active' CHECK (status IN ('prospect', 'active', 'paused', 'archived')),
|
|
industry TEXT NOT NULL DEFAULT '',
|
|
region TEXT NOT NULL DEFAULT '',
|
|
billing_email TEXT NOT NULL DEFAULT '',
|
|
tax_id TEXT NOT NULL DEFAULT '',
|
|
notes TEXT NOT NULL DEFAULT '',
|
|
tags_json TEXT NOT NULL DEFAULT '[]',
|
|
metadata_json TEXT NOT NULL DEFAULT '{}',
|
|
created_by TEXT NOT NULL REFERENCES users(id),
|
|
created_at TEXT NOT NULL,
|
|
updated_at TEXT NOT NULL,
|
|
UNIQUE (organization_id, code)
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS customer_contacts (
|
|
id TEXT PRIMARY KEY,
|
|
organization_id TEXT NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
|
|
customer_id TEXT NOT NULL REFERENCES customers(id) ON DELETE CASCADE,
|
|
name TEXT NOT NULL,
|
|
role TEXT NOT NULL DEFAULT '',
|
|
email TEXT NOT NULL DEFAULT '',
|
|
phone TEXT NOT NULL DEFAULT '',
|
|
is_primary INTEGER NOT NULL DEFAULT 0,
|
|
status TEXT NOT NULL DEFAULT 'active' CHECK (status IN ('active', 'inactive')),
|
|
created_at TEXT NOT NULL,
|
|
updated_at TEXT NOT NULL
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS production_contracts (
|
|
id TEXT PRIMARY KEY,
|
|
organization_id TEXT NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
|
|
customer_id TEXT NOT NULL REFERENCES customers(id) ON DELETE RESTRICT,
|
|
contract_number TEXT NOT NULL,
|
|
title TEXT NOT NULL,
|
|
contract_type TEXT NOT NULL DEFAULT 'production_license' CHECK (contract_type IN ('production_license', 'distribution', 'work_for_hire', 'revenue_share', 'internal')),
|
|
status TEXT NOT NULL DEFAULT 'draft' CHECK (status IN ('draft', 'submitted', 'active', 'expiring', 'expired', 'suspended', 'terminated')),
|
|
approval_status TEXT NOT NULL DEFAULT 'draft' CHECK (approval_status IN ('draft', 'legal_review', 'approved', 'blocked')),
|
|
effective_at TEXT,
|
|
expires_at TEXT,
|
|
signed_at TEXT,
|
|
currency TEXT NOT NULL DEFAULT 'CNY',
|
|
amount REAL NOT NULL DEFAULT 0,
|
|
license_scope_json TEXT NOT NULL DEFAULT '{}',
|
|
rights_json TEXT NOT NULL DEFAULT '{}',
|
|
delivery_terms_json TEXT NOT NULL DEFAULT '{}',
|
|
evidence_json TEXT NOT NULL DEFAULT '{}',
|
|
risk_json TEXT NOT NULL DEFAULT '{}',
|
|
approval_note TEXT NOT NULL DEFAULT '',
|
|
created_by TEXT NOT NULL REFERENCES users(id),
|
|
updated_by TEXT REFERENCES users(id),
|
|
created_at TEXT NOT NULL,
|
|
updated_at TEXT NOT NULL,
|
|
UNIQUE (organization_id, contract_number)
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS contract_project_bindings (
|
|
id TEXT PRIMARY KEY,
|
|
organization_id TEXT NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
|
|
workspace_id TEXT NOT NULL REFERENCES workspaces(id) ON DELETE CASCADE,
|
|
project_id TEXT NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
|
|
contract_id TEXT NOT NULL REFERENCES production_contracts(id) ON DELETE CASCADE,
|
|
customer_id TEXT NOT NULL REFERENCES customers(id) ON DELETE RESTRICT,
|
|
usage_mode TEXT NOT NULL DEFAULT 'primary-license' CHECK (usage_mode IN ('primary-license', 'supplemental-rights', 'delivery-only', 'internal-review')),
|
|
status TEXT NOT NULL DEFAULT 'active' CHECK (status IN ('active', 'review', 'blocked', 'archived')),
|
|
notes TEXT NOT NULL DEFAULT '',
|
|
created_by TEXT NOT NULL REFERENCES users(id),
|
|
created_at TEXT NOT NULL,
|
|
updated_at TEXT NOT NULL,
|
|
UNIQUE (organization_id, project_id, contract_id)
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS contract_license_events (
|
|
id TEXT PRIMARY KEY,
|
|
organization_id TEXT NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
|
|
contract_id TEXT REFERENCES production_contracts(id) ON DELETE CASCADE,
|
|
customer_id TEXT REFERENCES customers(id) ON DELETE SET NULL,
|
|
workspace_id TEXT REFERENCES workspaces(id) ON DELETE SET NULL,
|
|
project_id TEXT REFERENCES projects(id) ON DELETE SET NULL,
|
|
event_type TEXT NOT NULL,
|
|
status TEXT NOT NULL DEFAULT 'recorded',
|
|
actor_user_id TEXT REFERENCES users(id),
|
|
metadata_json TEXT NOT NULL DEFAULT '{}',
|
|
created_at TEXT NOT NULL
|
|
);
|
|
|
|
-- Local invoice ledger. This is intentionally payment-provider agnostic: the
|
|
-- platform records billing snapshots and lifecycle state, while an external
|
|
-- accounting or payment system can be connected later through an adapter.
|
|
CREATE TABLE IF NOT EXISTS organization_invoices (
|
|
id TEXT PRIMARY KEY,
|
|
organization_id TEXT NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
|
|
billing_account_id TEXT NOT NULL REFERENCES billing_accounts(id) ON DELETE CASCADE,
|
|
invoice_number TEXT NOT NULL UNIQUE,
|
|
status TEXT NOT NULL DEFAULT 'draft' CHECK (status IN ('draft', 'issued', 'paid', 'overdue', 'void')),
|
|
currency TEXT NOT NULL DEFAULT 'CNY',
|
|
billing_cycle TEXT NOT NULL DEFAULT 'monthly' CHECK (billing_cycle IN ('monthly', 'quarterly', 'annual')),
|
|
period_start TEXT NOT NULL,
|
|
period_end TEXT NOT NULL,
|
|
issued_at TEXT,
|
|
due_at TEXT,
|
|
paid_at TEXT,
|
|
voided_at TEXT,
|
|
subtotal REAL NOT NULL DEFAULT 0,
|
|
tax_rate REAL NOT NULL DEFAULT 0,
|
|
tax_amount REAL NOT NULL DEFAULT 0,
|
|
total_amount REAL NOT NULL DEFAULT 0,
|
|
snapshot_json TEXT NOT NULL DEFAULT '{}',
|
|
created_by TEXT REFERENCES users(id),
|
|
created_at TEXT NOT NULL,
|
|
updated_at TEXT NOT NULL,
|
|
UNIQUE (organization_id, period_start, period_end)
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS invoice_lines (
|
|
id TEXT PRIMARY KEY,
|
|
invoice_id TEXT NOT NULL REFERENCES organization_invoices(id) ON DELETE CASCADE,
|
|
line_type TEXT NOT NULL,
|
|
description TEXT NOT NULL,
|
|
quantity REAL NOT NULL DEFAULT 0,
|
|
unit_name TEXT NOT NULL DEFAULT '项',
|
|
unit_price REAL NOT NULL DEFAULT 0,
|
|
amount REAL NOT NULL DEFAULT 0,
|
|
metadata_json TEXT NOT NULL DEFAULT '{}',
|
|
sort_order INTEGER NOT NULL DEFAULT 0,
|
|
created_at TEXT NOT NULL
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_organization_invoices_org_period ON organization_invoices(organization_id, period_end DESC, created_at DESC);
|
|
CREATE INDEX IF NOT EXISTS idx_organization_invoices_org_status ON organization_invoices(organization_id, status, updated_at DESC);
|
|
CREATE INDEX IF NOT EXISTS idx_invoice_lines_invoice_sort ON invoice_lines(invoice_id, sort_order, created_at);
|
|
CREATE INDEX IF NOT EXISTS idx_subscription_plan_templates_status ON subscription_plan_templates(status, tier_key);
|
|
CREATE INDEX IF NOT EXISTS idx_organization_entitlements_org_category ON organization_entitlements(organization_id, category, entitlement_key);
|
|
CREATE INDEX IF NOT EXISTS idx_organization_policies_org_category ON organization_policies(organization_id, category, policy_key);
|
|
CREATE INDEX IF NOT EXISTS idx_organization_policy_evaluations_scope ON organization_policy_evaluations(organization_id, workspace_id, project_id, result, created_at DESC);
|
|
CREATE INDEX IF NOT EXISTS idx_commercial_approvals_org_status ON commercial_approval_requests(organization_id, status, updated_at DESC);
|
|
CREATE INDEX IF NOT EXISTS idx_commercial_approvals_requester ON commercial_approval_requests(requester_user_id, status, created_at DESC);
|
|
CREATE INDEX IF NOT EXISTS idx_commercial_approvals_target ON commercial_approval_requests(organization_id, request_type, target_key, status);
|
|
|
|
CREATE TABLE IF NOT EXISTS cost_centers (
|
|
id TEXT PRIMARY KEY,
|
|
organization_id TEXT NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
|
|
code TEXT NOT NULL,
|
|
name TEXT NOT NULL,
|
|
description TEXT NOT NULL DEFAULT '',
|
|
monthly_budget REAL NOT NULL DEFAULT 0,
|
|
currency TEXT NOT NULL DEFAULT 'CNY',
|
|
status TEXT NOT NULL DEFAULT 'active' CHECK (status IN ('active', 'archived')),
|
|
created_at TEXT NOT NULL,
|
|
updated_at TEXT NOT NULL,
|
|
UNIQUE (organization_id, code)
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS quota_allocations (
|
|
id TEXT PRIMARY KEY,
|
|
organization_id TEXT NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
|
|
workspace_id TEXT REFERENCES workspaces(id) ON DELETE CASCADE,
|
|
metric TEXT NOT NULL,
|
|
limit_value REAL NOT NULL,
|
|
used_value REAL NOT NULL DEFAULT 0,
|
|
unit TEXT NOT NULL,
|
|
period_start TEXT NOT NULL,
|
|
period_end TEXT NOT NULL,
|
|
created_at TEXT NOT NULL,
|
|
updated_at TEXT NOT NULL,
|
|
UNIQUE (organization_id, workspace_id, metric, period_start)
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS usage_events (
|
|
id TEXT PRIMARY KEY,
|
|
organization_id TEXT NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
|
|
workspace_id TEXT REFERENCES workspaces(id) ON DELETE CASCADE,
|
|
project_id TEXT REFERENCES projects(id) ON DELETE CASCADE,
|
|
user_id TEXT REFERENCES users(id),
|
|
kind TEXT NOT NULL,
|
|
units REAL NOT NULL DEFAULT 1,
|
|
unit_name TEXT NOT NULL DEFAULT 'event',
|
|
estimated_cost REAL NOT NULL DEFAULT 0,
|
|
metadata_json TEXT NOT NULL DEFAULT '{}',
|
|
created_at TEXT NOT NULL
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS model_connectors (
|
|
id TEXT PRIMARY KEY,
|
|
organization_id TEXT NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
|
|
workspace_id TEXT REFERENCES workspaces(id) ON DELETE CASCADE,
|
|
label TEXT NOT NULL,
|
|
kind TEXT NOT NULL,
|
|
capabilities_json TEXT NOT NULL DEFAULT '[]',
|
|
endpoint TEXT NOT NULL,
|
|
status TEXT NOT NULL DEFAULT 'not-connected',
|
|
cost_mode TEXT NOT NULL DEFAULT 'local',
|
|
approval_required INTEGER NOT NULL DEFAULT 0,
|
|
protocol_json TEXT NOT NULL DEFAULT '{}',
|
|
auth_env TEXT NOT NULL DEFAULT '',
|
|
last_probe_at TEXT,
|
|
latency_ms INTEGER,
|
|
error_message TEXT NOT NULL DEFAULT '',
|
|
created_by TEXT NOT NULL REFERENCES users(id),
|
|
created_at TEXT NOT NULL,
|
|
updated_at TEXT NOT NULL
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS model_catalog_entries (
|
|
id TEXT PRIMARY KEY,
|
|
organization_id TEXT NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
|
|
workspace_id TEXT REFERENCES workspaces(id) ON DELETE CASCADE,
|
|
connector_id TEXT REFERENCES model_connectors(id) ON DELETE SET NULL,
|
|
model_key TEXT NOT NULL,
|
|
display_name TEXT NOT NULL,
|
|
family TEXT NOT NULL DEFAULT '',
|
|
capabilities_json TEXT NOT NULL DEFAULT '[]',
|
|
context_window INTEGER NOT NULL DEFAULT 0,
|
|
max_output_tokens INTEGER NOT NULL DEFAULT 0,
|
|
cost_json TEXT NOT NULL DEFAULT '{}',
|
|
status TEXT NOT NULL DEFAULT 'active',
|
|
approval_status TEXT NOT NULL DEFAULT 'approved',
|
|
metadata_json TEXT NOT NULL DEFAULT '{}',
|
|
created_by TEXT NOT NULL REFERENCES users(id),
|
|
created_at TEXT NOT NULL,
|
|
updated_at TEXT NOT NULL,
|
|
UNIQUE (organization_id, workspace_id, connector_id, model_key)
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS model_routing_policies (
|
|
id TEXT PRIMARY KEY,
|
|
organization_id TEXT NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
|
|
workspace_id TEXT REFERENCES workspaces(id) ON DELETE CASCADE,
|
|
name TEXT NOT NULL,
|
|
workflow_key TEXT NOT NULL,
|
|
operation_key TEXT NOT NULL,
|
|
primary_model_id TEXT REFERENCES model_catalog_entries(id) ON DELETE SET NULL,
|
|
fallback_model_id TEXT REFERENCES model_catalog_entries(id) ON DELETE SET NULL,
|
|
policy_mode TEXT NOT NULL DEFAULT 'prefer-local',
|
|
approval_mode TEXT NOT NULL DEFAULT 'follow-model',
|
|
budget_limit_cny REAL NOT NULL DEFAULT 0,
|
|
status TEXT NOT NULL DEFAULT 'active',
|
|
policy_json TEXT NOT NULL DEFAULT '{}',
|
|
created_by TEXT NOT NULL REFERENCES users(id),
|
|
created_at TEXT NOT NULL,
|
|
updated_at TEXT NOT NULL
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS model_route_approval_requests (
|
|
id TEXT PRIMARY KEY,
|
|
organization_id TEXT NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
|
|
workspace_id TEXT REFERENCES workspaces(id) ON DELETE CASCADE,
|
|
project_id TEXT REFERENCES projects(id) ON DELETE SET NULL,
|
|
job_id TEXT REFERENCES generation_jobs(id) ON DELETE SET NULL,
|
|
route_id TEXT REFERENCES model_routing_policies(id) ON DELETE SET NULL,
|
|
model_entry_id TEXT REFERENCES model_catalog_entries(id) ON DELETE SET NULL,
|
|
connector_id TEXT REFERENCES model_connectors(id) ON DELETE SET NULL,
|
|
requester_user_id TEXT NOT NULL REFERENCES users(id),
|
|
reviewer_user_id TEXT REFERENCES users(id),
|
|
status TEXT NOT NULL DEFAULT 'submitted' CHECK (status IN ('submitted', 'approved', 'rejected', 'cancelled', 'expired')),
|
|
approval_scope TEXT NOT NULL DEFAULT 'single-run' CHECK (approval_scope IN ('single-run', 'single-job', 'route-window', 'connector-window')),
|
|
reason TEXT NOT NULL DEFAULT '',
|
|
decision_note TEXT NOT NULL DEFAULT '',
|
|
request_json TEXT NOT NULL DEFAULT '{}',
|
|
resolution_json TEXT NOT NULL DEFAULT '{}',
|
|
guard_json TEXT NOT NULL DEFAULT '{}',
|
|
estimated_cost_cny REAL NOT NULL DEFAULT 0,
|
|
local_only INTEGER NOT NULL DEFAULT 0,
|
|
expires_at TEXT,
|
|
reviewed_at TEXT,
|
|
consumed_at TEXT,
|
|
created_at TEXT NOT NULL,
|
|
updated_at TEXT NOT NULL
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS style_kits (
|
|
id TEXT PRIMARY KEY,
|
|
organization_id TEXT NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
|
|
workspace_id TEXT REFERENCES workspaces(id) ON DELETE CASCADE,
|
|
project_id TEXT REFERENCES projects(id) ON DELETE CASCADE,
|
|
scope_mode TEXT NOT NULL DEFAULT 'workspace' CHECK (scope_mode IN ('organization', 'workspace', 'project')),
|
|
name TEXT NOT NULL,
|
|
description TEXT NOT NULL DEFAULT '',
|
|
status TEXT NOT NULL DEFAULT 'draft' CHECK (status IN ('draft', 'active', 'paused', 'archived')),
|
|
visual_profile_json TEXT NOT NULL DEFAULT '{}',
|
|
subtitle_preset_json TEXT NOT NULL DEFAULT '{}',
|
|
voice_policy_json TEXT NOT NULL DEFAULT '{}',
|
|
delivery_spec_json TEXT NOT NULL DEFAULT '{}',
|
|
prompt_rules_json TEXT NOT NULL DEFAULT '{}',
|
|
single_frame_policy_json TEXT NOT NULL DEFAULT '{}',
|
|
naming_rules_json TEXT NOT NULL DEFAULT '{}',
|
|
qa_policy_json TEXT NOT NULL DEFAULT '{}',
|
|
metadata_json TEXT NOT NULL DEFAULT '{}',
|
|
created_by TEXT NOT NULL REFERENCES users(id),
|
|
created_at TEXT NOT NULL,
|
|
updated_at TEXT NOT NULL
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS project_style_kit_bindings (
|
|
id TEXT PRIMARY KEY,
|
|
organization_id TEXT NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
|
|
workspace_id TEXT NOT NULL REFERENCES workspaces(id) ON DELETE CASCADE,
|
|
project_id TEXT NOT NULL UNIQUE REFERENCES projects(id) ON DELETE CASCADE,
|
|
style_kit_id TEXT NOT NULL REFERENCES style_kits(id) ON DELETE CASCADE,
|
|
enforcement TEXT NOT NULL DEFAULT 'locked' CHECK (enforcement IN ('advisory', 'locked', 'strict')),
|
|
notes TEXT NOT NULL DEFAULT '',
|
|
created_by TEXT NOT NULL REFERENCES users(id),
|
|
created_at TEXT NOT NULL,
|
|
updated_at TEXT NOT NULL
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_style_kits_scope ON style_kits(organization_id, workspace_id, project_id, scope_mode, status, updated_at DESC);
|
|
CREATE INDEX IF NOT EXISTS idx_style_kit_bindings_scope ON project_style_kit_bindings(organization_id, workspace_id, project_id, style_kit_id);
|
|
|
|
CREATE TABLE IF NOT EXISTS series (
|
|
id TEXT PRIMARY KEY,
|
|
project_id TEXT NOT NULL UNIQUE REFERENCES projects(id) ON DELETE CASCADE,
|
|
title TEXT NOT NULL,
|
|
logline TEXT NOT NULL DEFAULT '',
|
|
format TEXT NOT NULL DEFAULT 'vertical-9:16',
|
|
visual_style TEXT NOT NULL DEFAULT '',
|
|
continuity_rule TEXT NOT NULL DEFAULT '',
|
|
show_engine TEXT NOT NULL DEFAULT '',
|
|
created_at TEXT NOT NULL,
|
|
updated_at TEXT NOT NULL
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS seasons (
|
|
id TEXT PRIMARY KEY,
|
|
series_id TEXT NOT NULL REFERENCES series(id) ON DELETE CASCADE,
|
|
season_number INTEGER NOT NULL,
|
|
title TEXT NOT NULL,
|
|
created_at TEXT NOT NULL,
|
|
updated_at TEXT NOT NULL,
|
|
UNIQUE (series_id, season_number)
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS episodes (
|
|
id TEXT PRIMARY KEY,
|
|
season_id TEXT NOT NULL REFERENCES seasons(id) ON DELETE CASCADE,
|
|
episode_number INTEGER NOT NULL,
|
|
title TEXT NOT NULL,
|
|
status TEXT NOT NULL DEFAULT 'draft',
|
|
target_duration_sec REAL NOT NULL DEFAULT 0,
|
|
hook TEXT NOT NULL DEFAULT '',
|
|
cliffhanger TEXT NOT NULL DEFAULT '',
|
|
created_at TEXT NOT NULL,
|
|
updated_at TEXT NOT NULL,
|
|
UNIQUE (season_id, episode_number)
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS script_documents (
|
|
id TEXT PRIMARY KEY,
|
|
organization_id TEXT NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
|
|
workspace_id TEXT NOT NULL REFERENCES workspaces(id) ON DELETE CASCADE,
|
|
project_id TEXT NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
|
|
episode_id TEXT REFERENCES episodes(id) ON DELETE SET NULL,
|
|
version_number INTEGER NOT NULL,
|
|
title TEXT NOT NULL,
|
|
source_type TEXT NOT NULL DEFAULT '原创短剧剧本',
|
|
content TEXT NOT NULL DEFAULT '',
|
|
status TEXT NOT NULL DEFAULT 'draft',
|
|
analysis_json TEXT NOT NULL DEFAULT '{}',
|
|
metadata_json TEXT NOT NULL DEFAULT '{}',
|
|
created_by TEXT NOT NULL REFERENCES users(id),
|
|
created_at TEXT NOT NULL,
|
|
updated_at TEXT NOT NULL,
|
|
UNIQUE (project_id, version_number)
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS knowledge_documents (
|
|
id TEXT PRIMARY KEY,
|
|
organization_id TEXT NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
|
|
workspace_id TEXT NOT NULL REFERENCES workspaces(id) ON DELETE CASCADE,
|
|
project_id TEXT REFERENCES projects(id) ON DELETE CASCADE,
|
|
scope_mode TEXT NOT NULL DEFAULT 'workspace',
|
|
title TEXT NOT NULL,
|
|
source_type TEXT NOT NULL DEFAULT 'novel',
|
|
language TEXT NOT NULL DEFAULT 'zh-CN',
|
|
content TEXT NOT NULL DEFAULT '',
|
|
status TEXT NOT NULL DEFAULT 'ingested',
|
|
rights_status TEXT NOT NULL DEFAULT 'needs-evidence',
|
|
summary TEXT NOT NULL DEFAULT '',
|
|
analysis_json TEXT NOT NULL DEFAULT '{}',
|
|
provenance_json TEXT NOT NULL DEFAULT '{}',
|
|
tags_json TEXT NOT NULL DEFAULT '[]',
|
|
risk_json TEXT NOT NULL DEFAULT '{}',
|
|
metadata_json TEXT NOT NULL DEFAULT '{}',
|
|
current_version_number INTEGER NOT NULL DEFAULT 1,
|
|
created_by TEXT NOT NULL REFERENCES users(id),
|
|
created_at TEXT NOT NULL,
|
|
updated_at TEXT NOT NULL
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS knowledge_chunks (
|
|
id TEXT PRIMARY KEY,
|
|
document_id TEXT NOT NULL REFERENCES knowledge_documents(id) ON DELETE CASCADE,
|
|
chunk_index INTEGER NOT NULL,
|
|
chunk_type TEXT NOT NULL DEFAULT 'scene',
|
|
heading TEXT NOT NULL DEFAULT '',
|
|
content TEXT NOT NULL DEFAULT '',
|
|
token_estimate INTEGER NOT NULL DEFAULT 0,
|
|
keywords_json TEXT NOT NULL DEFAULT '[]',
|
|
entities_json TEXT NOT NULL DEFAULT '{}',
|
|
metadata_json TEXT NOT NULL DEFAULT '{}',
|
|
created_at TEXT NOT NULL,
|
|
UNIQUE (document_id, chunk_index)
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS knowledge_document_versions (
|
|
id TEXT PRIMARY KEY,
|
|
document_id TEXT NOT NULL REFERENCES knowledge_documents(id) ON DELETE CASCADE,
|
|
version_number INTEGER NOT NULL,
|
|
title TEXT NOT NULL,
|
|
source_type TEXT NOT NULL DEFAULT 'novel',
|
|
language TEXT NOT NULL DEFAULT 'zh-CN',
|
|
content TEXT NOT NULL DEFAULT '',
|
|
summary TEXT NOT NULL DEFAULT '',
|
|
analysis_json TEXT NOT NULL DEFAULT '{}',
|
|
provenance_json TEXT NOT NULL DEFAULT '{}',
|
|
tags_json TEXT NOT NULL DEFAULT '[]',
|
|
risk_json TEXT NOT NULL DEFAULT '{}',
|
|
metadata_json TEXT NOT NULL DEFAULT '{}',
|
|
created_by TEXT NOT NULL REFERENCES users(id),
|
|
created_at TEXT NOT NULL,
|
|
UNIQUE (document_id, version_number)
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS knowledge_governance_reviews (
|
|
id TEXT PRIMARY KEY,
|
|
document_id TEXT NOT NULL REFERENCES knowledge_documents(id) ON DELETE CASCADE,
|
|
organization_id TEXT NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
|
|
workspace_id TEXT NOT NULL REFERENCES workspaces(id) ON DELETE CASCADE,
|
|
project_id TEXT REFERENCES projects(id) ON DELETE CASCADE,
|
|
decision TEXT NOT NULL DEFAULT 'submitted',
|
|
rights_status TEXT NOT NULL DEFAULT 'needs-evidence',
|
|
risk_status TEXT NOT NULL DEFAULT 'review',
|
|
notes TEXT NOT NULL DEFAULT '',
|
|
evidence_ref TEXT NOT NULL DEFAULT '',
|
|
provenance_json TEXT NOT NULL DEFAULT '{}',
|
|
risk_json TEXT NOT NULL DEFAULT '{}',
|
|
reviewer_user_id TEXT NOT NULL REFERENCES users(id),
|
|
created_at TEXT NOT NULL
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS knowledge_context_packs (
|
|
id TEXT PRIMARY KEY,
|
|
organization_id TEXT NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
|
|
workspace_id TEXT NOT NULL REFERENCES workspaces(id) ON DELETE CASCADE,
|
|
project_id TEXT REFERENCES projects(id) ON DELETE CASCADE,
|
|
scope_mode TEXT NOT NULL DEFAULT 'workspace',
|
|
name TEXT NOT NULL,
|
|
query TEXT NOT NULL DEFAULT '',
|
|
source_type TEXT NOT NULL DEFAULT '',
|
|
max_tokens INTEGER NOT NULL DEFAULT 1600,
|
|
token_estimate INTEGER NOT NULL DEFAULT 0,
|
|
chunk_ids_json TEXT NOT NULL DEFAULT '[]',
|
|
citations_json TEXT NOT NULL DEFAULT '[]',
|
|
chunks_json TEXT NOT NULL DEFAULT '[]',
|
|
prompt_context TEXT NOT NULL DEFAULT '',
|
|
status TEXT NOT NULL DEFAULT 'active',
|
|
metadata_json TEXT NOT NULL DEFAULT '{}',
|
|
created_by TEXT NOT NULL REFERENCES users(id),
|
|
created_at TEXT NOT NULL,
|
|
updated_at TEXT NOT NULL
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS assets (
|
|
id TEXT PRIMARY KEY,
|
|
project_id TEXT NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
|
|
kind TEXT NOT NULL,
|
|
name TEXT NOT NULL,
|
|
lock_status TEXT NOT NULL DEFAULT 'draft',
|
|
current_version_id TEXT,
|
|
created_by TEXT NOT NULL REFERENCES users(id),
|
|
created_at TEXT NOT NULL,
|
|
updated_at TEXT NOT NULL
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS asset_versions (
|
|
id TEXT PRIMARY KEY,
|
|
asset_id TEXT NOT NULL REFERENCES assets(id) ON DELETE CASCADE,
|
|
version_number INTEGER NOT NULL,
|
|
storage_path TEXT NOT NULL,
|
|
file_name TEXT NOT NULL DEFAULT '',
|
|
mime_type TEXT NOT NULL DEFAULT 'application/octet-stream',
|
|
file_size INTEGER NOT NULL DEFAULT 0,
|
|
content_sha256 TEXT NOT NULL DEFAULT '',
|
|
rights_status TEXT NOT NULL DEFAULT 'needs-evidence',
|
|
provenance_json TEXT NOT NULL DEFAULT '{}',
|
|
risk_json TEXT NOT NULL DEFAULT '{}',
|
|
tags_json TEXT NOT NULL DEFAULT '[]',
|
|
license_scope TEXT NOT NULL DEFAULT '',
|
|
expires_at TEXT,
|
|
metadata_json TEXT NOT NULL DEFAULT '{}',
|
|
created_by TEXT NOT NULL REFERENCES users(id),
|
|
created_at TEXT NOT NULL,
|
|
UNIQUE (asset_id, version_number)
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS asset_governance_reviews (
|
|
id TEXT PRIMARY KEY,
|
|
organization_id TEXT NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
|
|
workspace_id TEXT NOT NULL REFERENCES workspaces(id) ON DELETE CASCADE,
|
|
project_id TEXT NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
|
|
asset_id TEXT NOT NULL REFERENCES assets(id) ON DELETE CASCADE,
|
|
version_id TEXT NOT NULL REFERENCES asset_versions(id) ON DELETE CASCADE,
|
|
reviewer_user_id TEXT NOT NULL REFERENCES users(id),
|
|
decision TEXT NOT NULL,
|
|
rights_status TEXT NOT NULL,
|
|
risk_status TEXT NOT NULL DEFAULT 'review',
|
|
notes TEXT NOT NULL DEFAULT '',
|
|
evidence_ref TEXT NOT NULL DEFAULT '',
|
|
provenance_json TEXT NOT NULL DEFAULT '{}',
|
|
risk_json TEXT NOT NULL DEFAULT '{}',
|
|
created_at TEXT NOT NULL
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS shots (
|
|
id TEXT PRIMARY KEY,
|
|
episode_id TEXT NOT NULL REFERENCES episodes(id) ON DELETE CASCADE,
|
|
shot_number INTEGER NOT NULL,
|
|
title TEXT NOT NULL,
|
|
status TEXT NOT NULL DEFAULT 'draft',
|
|
first_frame_path TEXT,
|
|
last_frame_path TEXT,
|
|
current_version_id TEXT,
|
|
continuity_json TEXT NOT NULL DEFAULT '{}',
|
|
created_at TEXT NOT NULL,
|
|
updated_at TEXT NOT NULL,
|
|
UNIQUE (episode_id, shot_number)
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS shot_versions (
|
|
id TEXT PRIMARY KEY,
|
|
shot_id TEXT NOT NULL REFERENCES shots(id) ON DELETE CASCADE,
|
|
version_number INTEGER NOT NULL,
|
|
payload_json TEXT NOT NULL DEFAULT '{}',
|
|
status TEXT NOT NULL DEFAULT 'draft',
|
|
created_by TEXT NOT NULL REFERENCES users(id),
|
|
created_at TEXT NOT NULL,
|
|
UNIQUE (shot_id, version_number)
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS voice_lines (
|
|
id TEXT PRIMARY KEY,
|
|
shot_id TEXT NOT NULL REFERENCES shots(id) ON DELETE CASCADE,
|
|
line_number INTEGER NOT NULL,
|
|
character_key TEXT NOT NULL,
|
|
text TEXT NOT NULL,
|
|
emotion TEXT NOT NULL DEFAULT '',
|
|
mouth_plan TEXT NOT NULL DEFAULT '侧脸/反应镜头',
|
|
target_duration_sec REAL NOT NULL DEFAULT 2,
|
|
voice_id TEXT NOT NULL DEFAULT '',
|
|
audio_path TEXT NOT NULL DEFAULT '',
|
|
status TEXT NOT NULL DEFAULT 'draft',
|
|
created_by TEXT NOT NULL REFERENCES users(id),
|
|
created_at TEXT NOT NULL,
|
|
updated_at TEXT NOT NULL,
|
|
UNIQUE (shot_id, line_number)
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS asset_bindings (
|
|
id TEXT PRIMARY KEY,
|
|
asset_id TEXT NOT NULL REFERENCES assets(id) ON DELETE CASCADE,
|
|
shot_id TEXT NOT NULL REFERENCES shots(id) ON DELETE CASCADE,
|
|
usage_role TEXT NOT NULL DEFAULT 'continuity',
|
|
created_by TEXT NOT NULL REFERENCES users(id),
|
|
created_at TEXT NOT NULL,
|
|
UNIQUE (asset_id, shot_id, usage_role)
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS generation_jobs (
|
|
id TEXT PRIMARY KEY,
|
|
organization_id TEXT NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
|
|
workspace_id TEXT NOT NULL REFERENCES workspaces(id) ON DELETE CASCADE,
|
|
project_id TEXT NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
|
|
episode_id TEXT REFERENCES episodes(id) ON DELETE SET NULL,
|
|
shot_id TEXT REFERENCES shots(id) ON DELETE SET NULL,
|
|
kind TEXT NOT NULL,
|
|
adapter_id TEXT NOT NULL,
|
|
status TEXT NOT NULL DEFAULT 'queued',
|
|
priority INTEGER NOT NULL DEFAULT 50,
|
|
cost_policy TEXT NOT NULL DEFAULT 'local-only',
|
|
output_path TEXT NOT NULL DEFAULT '',
|
|
qa_status TEXT NOT NULL DEFAULT 'wait',
|
|
request_json TEXT NOT NULL DEFAULT '{}',
|
|
result_json TEXT NOT NULL DEFAULT '{}',
|
|
error_message TEXT NOT NULL DEFAULT '',
|
|
max_attempts INTEGER NOT NULL DEFAULT 3,
|
|
model_route_approval_id TEXT REFERENCES model_route_approval_requests(id) ON DELETE SET NULL,
|
|
next_run_at TEXT,
|
|
leased_by TEXT,
|
|
leased_at TEXT,
|
|
started_at TEXT,
|
|
finished_at TEXT,
|
|
created_by TEXT NOT NULL REFERENCES users(id),
|
|
created_at TEXT NOT NULL,
|
|
updated_at TEXT NOT NULL
|
|
);
|
|
|
|
CREATE TRIGGER IF NOT EXISTS trg_policy_evaluations_after_job_delete
|
|
AFTER DELETE ON generation_jobs
|
|
BEGIN
|
|
DELETE FROM organization_policy_evaluations
|
|
WHERE subject_type = 'generation_job' AND subject_id = OLD.id;
|
|
END;
|
|
|
|
CREATE TABLE IF NOT EXISTS job_attempts (
|
|
id TEXT PRIMARY KEY,
|
|
job_id TEXT NOT NULL REFERENCES generation_jobs(id) ON DELETE CASCADE,
|
|
attempt_number INTEGER NOT NULL,
|
|
runner_id TEXT NOT NULL,
|
|
status TEXT NOT NULL,
|
|
error_message TEXT,
|
|
started_at TEXT,
|
|
finished_at TEXT,
|
|
created_at TEXT NOT NULL,
|
|
UNIQUE (job_id, attempt_number)
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS job_dependencies (
|
|
job_id TEXT NOT NULL REFERENCES generation_jobs(id) ON DELETE CASCADE,
|
|
depends_on_job_id TEXT NOT NULL REFERENCES generation_jobs(id) ON DELETE CASCADE,
|
|
dependency_type TEXT NOT NULL DEFAULT 'blocking',
|
|
created_at TEXT NOT NULL,
|
|
PRIMARY KEY (job_id, depends_on_job_id),
|
|
CHECK (job_id <> depends_on_job_id)
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS reviews (
|
|
id TEXT PRIMARY KEY,
|
|
organization_id TEXT NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
|
|
workspace_id TEXT NOT NULL REFERENCES workspaces(id) ON DELETE CASCADE,
|
|
project_id TEXT NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
|
|
shot_id TEXT REFERENCES shots(id) ON DELETE CASCADE,
|
|
lane TEXT NOT NULL,
|
|
status TEXT NOT NULL DEFAULT 'pending',
|
|
score REAL,
|
|
decision_by TEXT REFERENCES users(id),
|
|
decision_at TEXT,
|
|
evidence_json TEXT NOT NULL DEFAULT '{}',
|
|
created_at TEXT NOT NULL,
|
|
updated_at TEXT NOT NULL
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS review_comments (
|
|
id TEXT PRIMARY KEY,
|
|
review_id TEXT NOT NULL REFERENCES reviews(id) ON DELETE CASCADE,
|
|
author_user_id TEXT NOT NULL REFERENCES users(id),
|
|
body TEXT NOT NULL,
|
|
created_at TEXT NOT NULL
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS compliance_records (
|
|
id TEXT PRIMARY KEY,
|
|
organization_id TEXT NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
|
|
workspace_id TEXT REFERENCES workspaces(id) ON DELETE CASCADE,
|
|
project_id TEXT REFERENCES projects(id) ON DELETE CASCADE,
|
|
subject_type TEXT NOT NULL,
|
|
subject_id TEXT NOT NULL,
|
|
policy_key TEXT NOT NULL,
|
|
status TEXT NOT NULL DEFAULT 'needs-evidence',
|
|
evidence_json TEXT NOT NULL DEFAULT '{}',
|
|
reviewed_by TEXT REFERENCES users(id),
|
|
reviewed_at TEXT,
|
|
created_at TEXT NOT NULL,
|
|
updated_at TEXT NOT NULL
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS deliveries (
|
|
id TEXT PRIMARY KEY,
|
|
organization_id TEXT NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
|
|
workspace_id TEXT NOT NULL REFERENCES workspaces(id) ON DELETE CASCADE,
|
|
project_id TEXT NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
|
|
version TEXT NOT NULL,
|
|
manifest_path TEXT NOT NULL,
|
|
channel TEXT NOT NULL DEFAULT 'internal',
|
|
status TEXT NOT NULL DEFAULT 'draft',
|
|
active_batch_id TEXT,
|
|
approved_by TEXT REFERENCES users(id),
|
|
approved_at TEXT,
|
|
created_at TEXT NOT NULL,
|
|
updated_at TEXT NOT NULL
|
|
);
|
|
|
|
-- A delivery version can have several immutable preparation batches. The
|
|
-- active batch pointer makes rollback explicit without rewriting old manifests.
|
|
CREATE TABLE IF NOT EXISTS delivery_batches (
|
|
id TEXT PRIMARY KEY,
|
|
organization_id TEXT NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
|
|
workspace_id TEXT NOT NULL REFERENCES workspaces(id) ON DELETE CASCADE,
|
|
project_id TEXT NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
|
|
delivery_id TEXT NOT NULL REFERENCES deliveries(id) ON DELETE CASCADE,
|
|
batch_number INTEGER NOT NULL,
|
|
label TEXT NOT NULL DEFAULT '',
|
|
status TEXT NOT NULL DEFAULT 'prepared',
|
|
manifest_path TEXT NOT NULL DEFAULT '',
|
|
composition_id TEXT REFERENCES media_compositions(id) ON DELETE SET NULL,
|
|
source_json TEXT NOT NULL DEFAULT '{}',
|
|
result_json TEXT NOT NULL DEFAULT '{}',
|
|
rollback_of_batch_id TEXT REFERENCES delivery_batches(id) ON DELETE SET NULL,
|
|
created_by TEXT NOT NULL REFERENCES users(id),
|
|
created_at TEXT NOT NULL,
|
|
updated_at TEXT NOT NULL,
|
|
UNIQUE (delivery_id, batch_number)
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS delivery_batch_items (
|
|
id TEXT PRIMARY KEY,
|
|
batch_id TEXT NOT NULL REFERENCES delivery_batches(id) ON DELETE CASCADE,
|
|
shot_id TEXT REFERENCES shots(id) ON DELETE SET NULL,
|
|
sequence_number INTEGER NOT NULL,
|
|
source_path TEXT NOT NULL DEFAULT '',
|
|
actual_last_frame_path TEXT NOT NULL DEFAULT '',
|
|
source_sha256 TEXT NOT NULL DEFAULT '',
|
|
metadata_json TEXT NOT NULL DEFAULT '{}',
|
|
created_at TEXT NOT NULL,
|
|
UNIQUE (batch_id, sequence_number)
|
|
);
|
|
|
|
-- Delivery channels are tenant-scoped destinations. Cloud connectors are
|
|
-- deliberately excluded from this MVP; local-file and private-network
|
|
-- webhook are the only publish paths allowed by the server.
|
|
CREATE TABLE IF NOT EXISTS delivery_channels (
|
|
id TEXT PRIMARY KEY,
|
|
organization_id TEXT NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
|
|
workspace_id TEXT NOT NULL REFERENCES workspaces(id) ON DELETE CASCADE,
|
|
project_id TEXT REFERENCES projects(id) ON DELETE CASCADE,
|
|
name TEXT NOT NULL,
|
|
kind TEXT NOT NULL CHECK (kind IN ('local-file', 'local-webhook')),
|
|
enabled INTEGER NOT NULL DEFAULT 1,
|
|
endpoint TEXT NOT NULL DEFAULT '',
|
|
auth_env TEXT NOT NULL DEFAULT '',
|
|
require_approval INTEGER NOT NULL DEFAULT 1,
|
|
config_json TEXT NOT NULL DEFAULT '{}',
|
|
created_by TEXT NOT NULL REFERENCES users(id),
|
|
created_at TEXT NOT NULL,
|
|
updated_at TEXT NOT NULL
|
|
);
|
|
|
|
-- A release is an auditable publish request over an immutable delivery batch.
|
|
-- The state machine is enforced in server/production.mjs rather than by
|
|
-- allowing arbitrary status writes from the client.
|
|
CREATE TABLE IF NOT EXISTS delivery_releases (
|
|
id TEXT PRIMARY KEY,
|
|
organization_id TEXT NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
|
|
workspace_id TEXT NOT NULL REFERENCES workspaces(id) ON DELETE CASCADE,
|
|
project_id TEXT NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
|
|
delivery_id TEXT NOT NULL REFERENCES deliveries(id) ON DELETE CASCADE,
|
|
channel_id TEXT NOT NULL REFERENCES delivery_channels(id) ON DELETE RESTRICT,
|
|
status TEXT NOT NULL DEFAULT 'draft' CHECK (status IN ('draft', 'submitted', 'approved', 'rejected', 'published', 'failed')),
|
|
idempotency_key TEXT NOT NULL DEFAULT '',
|
|
requested_by TEXT REFERENCES users(id),
|
|
requested_at TEXT,
|
|
reviewed_by TEXT REFERENCES users(id),
|
|
reviewed_at TEXT,
|
|
decision_note TEXT NOT NULL DEFAULT '',
|
|
published_by TEXT REFERENCES users(id),
|
|
published_at TEXT,
|
|
output_path TEXT NOT NULL DEFAULT '',
|
|
preflight_json TEXT NOT NULL DEFAULT '{}',
|
|
result_json TEXT NOT NULL DEFAULT '{}',
|
|
created_at TEXT NOT NULL,
|
|
updated_at TEXT NOT NULL
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS delivery_clearance_reports (
|
|
id TEXT PRIMARY KEY,
|
|
organization_id TEXT NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
|
|
workspace_id TEXT NOT NULL REFERENCES workspaces(id) ON DELETE CASCADE,
|
|
project_id TEXT NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
|
|
delivery_id TEXT NOT NULL REFERENCES deliveries(id) ON DELETE CASCADE,
|
|
batch_id TEXT REFERENCES delivery_batches(id) ON DELETE SET NULL,
|
|
release_id TEXT REFERENCES delivery_releases(id) ON DELETE SET NULL,
|
|
status TEXT NOT NULL DEFAULT 'review' CHECK (status IN ('pass', 'review', 'blocked')),
|
|
blocker_count INTEGER NOT NULL DEFAULT 0,
|
|
review_count INTEGER NOT NULL DEFAULT 0,
|
|
certificate_path TEXT NOT NULL DEFAULT '',
|
|
report_json TEXT NOT NULL DEFAULT '{}',
|
|
created_by TEXT NOT NULL REFERENCES users(id),
|
|
created_at TEXT NOT NULL
|
|
);
|
|
|
|
-- External delivery portals are scoped to one published release. The bearer
|
|
-- token is never stored in plaintext; only its SHA-256 digest is persisted.
|
|
CREATE TABLE IF NOT EXISTS delivery_access_links (
|
|
id TEXT PRIMARY KEY,
|
|
organization_id TEXT NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
|
|
workspace_id TEXT NOT NULL REFERENCES workspaces(id) ON DELETE CASCADE,
|
|
project_id TEXT NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
|
|
release_id TEXT NOT NULL REFERENCES delivery_releases(id) ON DELETE CASCADE,
|
|
token_hash TEXT NOT NULL UNIQUE,
|
|
token_hint TEXT NOT NULL DEFAULT '',
|
|
recipient_name TEXT NOT NULL DEFAULT '',
|
|
recipient_email TEXT NOT NULL DEFAULT '',
|
|
status TEXT NOT NULL DEFAULT 'active' CHECK (status IN ('active', 'revoked', 'expired')),
|
|
expires_at TEXT NOT NULL,
|
|
max_downloads INTEGER NOT NULL DEFAULT 10 CHECK (max_downloads > 0),
|
|
download_count INTEGER NOT NULL DEFAULT 0 CHECK (download_count >= 0),
|
|
last_viewed_at TEXT,
|
|
last_downloaded_at TEXT,
|
|
created_by TEXT NOT NULL REFERENCES users(id),
|
|
revoked_by TEXT REFERENCES users(id) ON DELETE SET NULL,
|
|
revoked_at TEXT,
|
|
metadata_json TEXT NOT NULL DEFAULT '{}',
|
|
created_at TEXT NOT NULL,
|
|
updated_at TEXT NOT NULL
|
|
);
|
|
|
|
-- Public portal access is a separate audit stream so external activity can be
|
|
-- reviewed without exposing internal tenant audit records to the recipient.
|
|
CREATE TABLE IF NOT EXISTS delivery_access_events (
|
|
id TEXT PRIMARY KEY,
|
|
link_id TEXT REFERENCES delivery_access_links(id) ON DELETE SET NULL,
|
|
release_id TEXT REFERENCES delivery_releases(id) ON DELETE SET NULL,
|
|
organization_id TEXT REFERENCES organizations(id) ON DELETE SET NULL,
|
|
workspace_id TEXT REFERENCES workspaces(id) ON DELETE SET NULL,
|
|
project_id TEXT REFERENCES projects(id) ON DELETE SET NULL,
|
|
event_type TEXT NOT NULL CHECK (event_type IN ('view', 'download')),
|
|
result TEXT NOT NULL CHECK (result IN ('success', 'denied', 'missing')),
|
|
file_kind TEXT NOT NULL DEFAULT '',
|
|
token_fingerprint TEXT NOT NULL DEFAULT '',
|
|
ip_address TEXT NOT NULL DEFAULT '',
|
|
user_agent TEXT NOT NULL DEFAULT '',
|
|
metadata_json TEXT NOT NULL DEFAULT '{}',
|
|
created_at TEXT NOT NULL
|
|
);
|
|
|
|
-- External recipients can accept a published release or request changes from
|
|
-- the same scoped bearer portal. Feedback is append-only so the internal team
|
|
-- can see the complete client decision history without exposing tenant audit.
|
|
CREATE TABLE IF NOT EXISTS delivery_access_feedback (
|
|
id TEXT PRIMARY KEY,
|
|
link_id TEXT NOT NULL REFERENCES delivery_access_links(id) ON DELETE CASCADE,
|
|
release_id TEXT NOT NULL REFERENCES delivery_releases(id) ON DELETE CASCADE,
|
|
organization_id TEXT NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
|
|
workspace_id TEXT NOT NULL REFERENCES workspaces(id) ON DELETE CASCADE,
|
|
project_id TEXT NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
|
|
decision TEXT NOT NULL CHECK (decision IN ('approved', 'changes_requested')),
|
|
message TEXT NOT NULL DEFAULT '',
|
|
reviewer_name TEXT NOT NULL DEFAULT '',
|
|
reviewer_email TEXT NOT NULL DEFAULT '',
|
|
ip_address TEXT NOT NULL DEFAULT '',
|
|
user_agent TEXT NOT NULL DEFAULT '',
|
|
created_at TEXT NOT NULL,
|
|
updated_at TEXT NOT NULL
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS audit_logs (
|
|
id TEXT PRIMARY KEY,
|
|
organization_id TEXT REFERENCES organizations(id) ON DELETE CASCADE,
|
|
workspace_id TEXT REFERENCES workspaces(id) ON DELETE CASCADE,
|
|
project_id TEXT REFERENCES projects(id) ON DELETE CASCADE,
|
|
actor_user_id TEXT REFERENCES users(id),
|
|
action TEXT NOT NULL,
|
|
target_type TEXT NOT NULL,
|
|
target_id TEXT NOT NULL,
|
|
result TEXT NOT NULL DEFAULT 'ok',
|
|
metadata_json TEXT NOT NULL DEFAULT '{}',
|
|
created_at TEXT NOT NULL
|
|
);
|
|
|
|
-- Platform-wide administration data. These records are intentionally separate
|
|
-- from project content so deployment policy can be governed independently.
|
|
CREATE TABLE IF NOT EXISTS system_settings (
|
|
key TEXT PRIMARY KEY,
|
|
category TEXT NOT NULL DEFAULT 'general',
|
|
value_json TEXT NOT NULL DEFAULT 'null',
|
|
value_type TEXT NOT NULL DEFAULT 'string',
|
|
description TEXT NOT NULL DEFAULT '',
|
|
is_sensitive INTEGER NOT NULL DEFAULT 0,
|
|
updated_by TEXT REFERENCES users(id),
|
|
created_at TEXT NOT NULL,
|
|
updated_at TEXT NOT NULL
|
|
);
|
|
|
|
-- Workflow templates are versioned production contracts. Global templates can
|
|
-- be overridden at organization or workspace scope without changing project
|
|
-- content already in production.
|
|
CREATE TABLE IF NOT EXISTS workflow_templates (
|
|
id TEXT PRIMARY KEY,
|
|
organization_id TEXT REFERENCES organizations(id) ON DELETE CASCADE,
|
|
workspace_id TEXT REFERENCES workspaces(id) ON DELETE CASCADE,
|
|
template_key TEXT NOT NULL,
|
|
version_number INTEGER NOT NULL DEFAULT 1,
|
|
name TEXT NOT NULL,
|
|
category TEXT NOT NULL DEFAULT 'ai-manhua-drama',
|
|
status TEXT NOT NULL DEFAULT 'draft' CHECK (status IN ('draft', 'active', 'archived')),
|
|
description TEXT NOT NULL DEFAULT '',
|
|
steps_json TEXT NOT NULL DEFAULT '[]',
|
|
gates_json TEXT NOT NULL DEFAULT '[]',
|
|
default_adapter_id TEXT NOT NULL DEFAULT '',
|
|
created_by TEXT REFERENCES users(id),
|
|
created_at TEXT NOT NULL,
|
|
updated_at TEXT NOT NULL,
|
|
UNIQUE (organization_id, workspace_id, template_key, version_number)
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS workflow_runs (
|
|
id TEXT PRIMARY KEY,
|
|
organization_id TEXT NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
|
|
workspace_id TEXT NOT NULL REFERENCES workspaces(id) ON DELETE CASCADE,
|
|
project_id TEXT NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
|
|
episode_id TEXT REFERENCES episodes(id) ON DELETE SET NULL,
|
|
shot_id TEXT REFERENCES shots(id) ON DELETE SET NULL,
|
|
template_id TEXT NOT NULL REFERENCES workflow_templates(id),
|
|
status TEXT NOT NULL DEFAULT 'planned' CHECK (status IN ('planned', 'queued', 'running', 'blocked', 'completed', 'failed', 'cancelled')),
|
|
current_step_index INTEGER NOT NULL DEFAULT 0,
|
|
steps_json TEXT NOT NULL DEFAULT '[]',
|
|
job_ids_json TEXT NOT NULL DEFAULT '[]',
|
|
error_message TEXT NOT NULL DEFAULT '',
|
|
created_by TEXT NOT NULL REFERENCES users(id),
|
|
created_at TEXT NOT NULL,
|
|
updated_at TEXT NOT NULL
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS feature_flags (
|
|
key TEXT PRIMARY KEY,
|
|
label TEXT NOT NULL,
|
|
description TEXT NOT NULL DEFAULT '',
|
|
enabled INTEGER NOT NULL DEFAULT 0,
|
|
scope TEXT NOT NULL DEFAULT 'system',
|
|
updated_by TEXT REFERENCES users(id),
|
|
created_at TEXT NOT NULL,
|
|
updated_at TEXT NOT NULL
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS notification_channels (
|
|
id TEXT PRIMARY KEY,
|
|
name TEXT NOT NULL,
|
|
kind TEXT NOT NULL,
|
|
endpoint TEXT NOT NULL DEFAULT '',
|
|
enabled INTEGER NOT NULL DEFAULT 0,
|
|
events_json TEXT NOT NULL DEFAULT '[]',
|
|
secret_ref TEXT NOT NULL DEFAULT '',
|
|
created_by TEXT REFERENCES users(id),
|
|
created_at TEXT NOT NULL,
|
|
updated_at TEXT NOT NULL
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS notification_deliveries (
|
|
id TEXT PRIMARY KEY,
|
|
channel_id TEXT NOT NULL REFERENCES notification_channels(id) ON DELETE CASCADE,
|
|
event_key TEXT NOT NULL,
|
|
organization_id TEXT REFERENCES organizations(id) ON DELETE CASCADE,
|
|
workspace_id TEXT REFERENCES workspaces(id) ON DELETE CASCADE,
|
|
project_id TEXT REFERENCES projects(id) ON DELETE CASCADE,
|
|
status TEXT NOT NULL DEFAULT 'pending',
|
|
attempt_count INTEGER NOT NULL DEFAULT 0,
|
|
request_json TEXT NOT NULL DEFAULT '{}',
|
|
response_json TEXT NOT NULL DEFAULT '{}',
|
|
error_message TEXT NOT NULL DEFAULT '',
|
|
created_at TEXT NOT NULL,
|
|
delivered_at TEXT
|
|
);
|
|
|
|
-- In-app notifications are separate from administrator-managed delivery
|
|
-- channels. They are addressed to a user and remain inside the tenant scope
|
|
-- so the creator portal can expose unread state without leaking webhooks.
|
|
CREATE TABLE IF NOT EXISTS user_notifications (
|
|
id TEXT PRIMARY KEY,
|
|
user_id TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
|
organization_id TEXT NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
|
|
workspace_id TEXT REFERENCES workspaces(id) ON DELETE CASCADE,
|
|
project_id TEXT REFERENCES projects(id) ON DELETE CASCADE,
|
|
category TEXT NOT NULL DEFAULT 'system',
|
|
event_key TEXT NOT NULL,
|
|
severity TEXT NOT NULL DEFAULT 'info',
|
|
title TEXT NOT NULL,
|
|
body TEXT NOT NULL DEFAULT '',
|
|
target_tab TEXT NOT NULL DEFAULT 'creator-home',
|
|
target_id TEXT NOT NULL DEFAULT '',
|
|
metadata_json TEXT NOT NULL DEFAULT '{}',
|
|
created_at TEXT NOT NULL,
|
|
read_at TEXT,
|
|
expires_at TEXT
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS user_notification_preferences (
|
|
user_id TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
|
organization_id TEXT NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
|
|
category TEXT NOT NULL,
|
|
in_app_enabled INTEGER NOT NULL DEFAULT 1,
|
|
updated_at TEXT NOT NULL,
|
|
PRIMARY KEY (user_id, organization_id, category)
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS media_compositions (
|
|
id TEXT PRIMARY KEY,
|
|
organization_id TEXT NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
|
|
workspace_id TEXT NOT NULL REFERENCES workspaces(id) ON DELETE CASCADE,
|
|
project_id TEXT NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
|
|
episode_id TEXT REFERENCES episodes(id) ON DELETE SET NULL,
|
|
version TEXT NOT NULL,
|
|
status TEXT NOT NULL DEFAULT 'planned',
|
|
tool TEXT NOT NULL DEFAULT 'ffmpeg',
|
|
dry_run INTEGER NOT NULL DEFAULT 0,
|
|
output_path TEXT NOT NULL DEFAULT '',
|
|
manifest_path TEXT NOT NULL DEFAULT '',
|
|
source_json TEXT NOT NULL DEFAULT '{}',
|
|
result_json TEXT NOT NULL DEFAULT '{}',
|
|
error_message TEXT NOT NULL DEFAULT '',
|
|
created_by TEXT NOT NULL REFERENCES users(id),
|
|
created_at TEXT NOT NULL,
|
|
updated_at TEXT NOT NULL
|
|
);
|
|
|
|
-- Canonical file evidence for generated media and local compositions. A job
|
|
-- result is an API response; this table is the production record of the
|
|
-- actual file that can be inspected, delivered, or rolled back.
|
|
CREATE TABLE IF NOT EXISTS media_artifacts (
|
|
id TEXT PRIMARY KEY,
|
|
organization_id TEXT NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
|
|
workspace_id TEXT NOT NULL REFERENCES workspaces(id) ON DELETE CASCADE,
|
|
project_id TEXT NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
|
|
episode_id TEXT REFERENCES episodes(id) ON DELETE SET NULL,
|
|
shot_id TEXT REFERENCES shots(id) ON DELETE SET NULL,
|
|
job_id TEXT REFERENCES generation_jobs(id) ON DELETE SET NULL,
|
|
composition_id TEXT REFERENCES media_compositions(id) ON DELETE SET NULL,
|
|
kind TEXT NOT NULL,
|
|
role TEXT NOT NULL DEFAULT 'output',
|
|
status TEXT NOT NULL DEFAULT 'missing',
|
|
path TEXT NOT NULL,
|
|
mime_type TEXT NOT NULL DEFAULT '',
|
|
file_size INTEGER NOT NULL DEFAULT 0,
|
|
sha256 TEXT NOT NULL DEFAULT '',
|
|
duration_sec REAL NOT NULL DEFAULT 0,
|
|
width INTEGER NOT NULL DEFAULT 0,
|
|
height INTEGER NOT NULL DEFAULT 0,
|
|
has_video INTEGER NOT NULL DEFAULT 0,
|
|
has_audio INTEGER NOT NULL DEFAULT 0,
|
|
first_frame_path TEXT NOT NULL DEFAULT '',
|
|
last_frame_path TEXT NOT NULL DEFAULT '',
|
|
metadata_json TEXT NOT NULL DEFAULT '{}',
|
|
created_by TEXT REFERENCES users(id),
|
|
created_at TEXT NOT NULL,
|
|
updated_at TEXT NOT NULL,
|
|
UNIQUE (job_id, composition_id, path, role)
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_media_artifacts_scope ON media_artifacts(project_id, shot_id, kind, status, created_at);
|
|
CREATE INDEX IF NOT EXISTS idx_media_artifacts_job ON media_artifacts(job_id, created_at);
|
|
|
|
CREATE TABLE IF NOT EXISTS api_clients (
|
|
id TEXT PRIMARY KEY,
|
|
name TEXT NOT NULL,
|
|
client_key TEXT NOT NULL UNIQUE,
|
|
organization_id TEXT REFERENCES organizations(id) ON DELETE CASCADE,
|
|
workspace_id TEXT REFERENCES workspaces(id) ON DELETE CASCADE,
|
|
status TEXT NOT NULL DEFAULT 'active',
|
|
scopes_json TEXT NOT NULL DEFAULT '[]',
|
|
last_used_at TEXT,
|
|
created_by TEXT REFERENCES users(id),
|
|
created_at TEXT NOT NULL,
|
|
updated_at TEXT NOT NULL
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS service_health (
|
|
id TEXT PRIMARY KEY,
|
|
service_key TEXT NOT NULL UNIQUE,
|
|
label TEXT NOT NULL,
|
|
kind TEXT NOT NULL DEFAULT 'local-service',
|
|
status TEXT NOT NULL DEFAULT 'unknown',
|
|
endpoint TEXT NOT NULL DEFAULT '',
|
|
latency_ms INTEGER,
|
|
queue_depth INTEGER NOT NULL DEFAULT 0,
|
|
version TEXT NOT NULL DEFAULT '',
|
|
last_heartbeat TEXT,
|
|
metadata_json TEXT NOT NULL DEFAULT '{}',
|
|
updated_at TEXT NOT NULL
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_org_members_user ON organization_members(user_id, status);
|
|
CREATE INDEX IF NOT EXISTS idx_workspace_members_user ON workspace_members(user_id, status);
|
|
CREATE INDEX IF NOT EXISTS idx_projects_workspace ON projects(workspace_id, status);
|
|
CREATE INDEX IF NOT EXISTS idx_script_documents_project ON script_documents(project_id, version_number DESC);
|
|
CREATE INDEX IF NOT EXISTS idx_knowledge_documents_scope ON knowledge_documents(organization_id, workspace_id, project_id, scope_mode, updated_at DESC);
|
|
CREATE INDEX IF NOT EXISTS idx_knowledge_chunks_document ON knowledge_chunks(document_id, chunk_index);
|
|
CREATE INDEX IF NOT EXISTS idx_knowledge_document_versions_document ON knowledge_document_versions(document_id, version_number DESC);
|
|
CREATE INDEX IF NOT EXISTS idx_knowledge_governance_reviews_document ON knowledge_governance_reviews(document_id, created_at DESC);
|
|
CREATE INDEX IF NOT EXISTS idx_knowledge_context_packs_scope ON knowledge_context_packs(organization_id, workspace_id, project_id, status, updated_at DESC);
|
|
CREATE INDEX IF NOT EXISTS idx_shot_versions_shot ON shot_versions(shot_id, version_number DESC);
|
|
CREATE INDEX IF NOT EXISTS idx_voice_lines_shot ON voice_lines(shot_id, line_number);
|
|
CREATE INDEX IF NOT EXISTS idx_project_members_user ON project_members(user_id, status);
|
|
CREATE INDEX IF NOT EXISTS idx_org_role_permissions_scope ON organization_role_permissions(organization_id, role_key, permission_key);
|
|
CREATE INDEX IF NOT EXISTS idx_model_catalog_entries_scope ON model_catalog_entries(organization_id, workspace_id, connector_id, status, updated_at DESC);
|
|
CREATE INDEX IF NOT EXISTS idx_model_routing_policies_scope ON model_routing_policies(organization_id, workspace_id, workflow_key, operation_key, status, updated_at DESC);
|
|
CREATE INDEX IF NOT EXISTS idx_asset_bindings_shot ON asset_bindings(shot_id, usage_role);
|
|
CREATE INDEX IF NOT EXISTS idx_asset_governance_reviews_asset ON asset_governance_reviews(asset_id, created_at DESC);
|
|
CREATE INDEX IF NOT EXISTS idx_asset_governance_reviews_scope ON asset_governance_reviews(organization_id, workspace_id, project_id, risk_status, created_at DESC);
|
|
CREATE INDEX IF NOT EXISTS idx_jobs_scope ON generation_jobs(organization_id, workspace_id, project_id, status);
|
|
CREATE INDEX IF NOT EXISTS idx_usage_scope ON usage_events(organization_id, workspace_id, project_id, created_at);
|
|
CREATE INDEX IF NOT EXISTS idx_audit_scope ON audit_logs(organization_id, workspace_id, project_id, created_at);
|
|
CREATE INDEX IF NOT EXISTS idx_invites_scope ON invitations(organization_id, status, created_at);
|
|
CREATE INDEX IF NOT EXISTS idx_job_dependencies_dependency ON job_dependencies(depends_on_job_id, job_id);
|
|
CREATE INDEX IF NOT EXISTS idx_system_settings_category ON system_settings(category, updated_at);
|
|
CREATE INDEX IF NOT EXISTS idx_workflow_templates_scope ON workflow_templates(organization_id, workspace_id, status, template_key, version_number DESC);
|
|
CREATE INDEX IF NOT EXISTS idx_workflow_runs_scope ON workflow_runs(organization_id, workspace_id, project_id, status, created_at DESC);
|
|
CREATE INDEX IF NOT EXISTS idx_feature_flags_scope ON feature_flags(scope, enabled);
|
|
CREATE INDEX IF NOT EXISTS idx_notification_channels_enabled ON notification_channels(enabled, kind);
|
|
CREATE INDEX IF NOT EXISTS idx_notification_deliveries_scope ON notification_deliveries(organization_id, created_at);
|
|
CREATE INDEX IF NOT EXISTS idx_user_notifications_inbox ON user_notifications(user_id, organization_id, workspace_id, created_at DESC);
|
|
CREATE INDEX IF NOT EXISTS idx_user_notifications_unread ON user_notifications(user_id, organization_id, workspace_id, read_at, created_at DESC);
|
|
CREATE INDEX IF NOT EXISTS idx_user_notification_preferences_scope ON user_notification_preferences(user_id, organization_id, category);
|
|
CREATE INDEX IF NOT EXISTS idx_project_tasks_scope ON project_tasks(organization_id, workspace_id, project_id, status, updated_at DESC);
|
|
CREATE INDEX IF NOT EXISTS idx_project_tasks_assignee ON project_tasks(assignee_user_id, status, due_at);
|
|
CREATE INDEX IF NOT EXISTS idx_task_comments_scope ON task_comments(organization_id, workspace_id, project_id, task_id, created_at);
|
|
CREATE INDEX IF NOT EXISTS idx_task_comments_author ON task_comments(author_user_id, created_at DESC);
|
|
CREATE INDEX IF NOT EXISTS idx_task_links_scope ON task_links(organization_id, workspace_id, project_id, task_id, created_at);
|
|
CREATE INDEX IF NOT EXISTS idx_media_compositions_scope ON media_compositions(organization_id, workspace_id, project_id, created_at);
|
|
CREATE INDEX IF NOT EXISTS idx_delivery_batches_scope ON delivery_batches(organization_id, workspace_id, project_id, delivery_id, created_at);
|
|
CREATE INDEX IF NOT EXISTS idx_delivery_batch_items_batch ON delivery_batch_items(batch_id, sequence_number);
|
|
CREATE INDEX IF NOT EXISTS idx_delivery_channels_scope ON delivery_channels(organization_id, workspace_id, project_id, enabled, created_at);
|
|
CREATE INDEX IF NOT EXISTS idx_delivery_releases_scope ON delivery_releases(organization_id, workspace_id, project_id, delivery_id, status, created_at DESC);
|
|
CREATE INDEX IF NOT EXISTS idx_delivery_releases_idempotency ON delivery_releases(organization_id, project_id, idempotency_key);
|
|
CREATE INDEX IF NOT EXISTS idx_delivery_clearance_reports_scope ON delivery_clearance_reports(organization_id, workspace_id, project_id, delivery_id, created_at DESC);
|
|
CREATE INDEX IF NOT EXISTS idx_delivery_clearance_reports_release ON delivery_clearance_reports(release_id, created_at DESC);
|
|
CREATE INDEX IF NOT EXISTS idx_delivery_access_links_token ON delivery_access_links(token_hash, status, expires_at);
|
|
CREATE INDEX IF NOT EXISTS idx_delivery_access_links_scope ON delivery_access_links(organization_id, workspace_id, project_id, release_id, status, created_at DESC);
|
|
CREATE INDEX IF NOT EXISTS idx_delivery_access_events_link ON delivery_access_events(link_id, created_at DESC);
|
|
CREATE INDEX IF NOT EXISTS idx_delivery_access_events_release ON delivery_access_events(release_id, created_at DESC);
|
|
CREATE INDEX IF NOT EXISTS idx_delivery_access_feedback_link ON delivery_access_feedback(link_id, created_at DESC);
|
|
CREATE INDEX IF NOT EXISTS idx_delivery_access_feedback_release ON delivery_access_feedback(release_id, created_at DESC);
|
|
CREATE INDEX IF NOT EXISTS idx_customers_org_status ON customers(organization_id, status, updated_at DESC);
|
|
CREATE INDEX IF NOT EXISTS idx_customer_contacts_customer ON customer_contacts(customer_id, is_primary DESC, status);
|
|
CREATE INDEX IF NOT EXISTS idx_production_contracts_org_status ON production_contracts(organization_id, status, approval_status, updated_at DESC);
|
|
CREATE INDEX IF NOT EXISTS idx_production_contracts_customer ON production_contracts(customer_id, status, updated_at DESC);
|
|
CREATE INDEX IF NOT EXISTS idx_contract_project_bindings_project ON contract_project_bindings(organization_id, workspace_id, project_id, status);
|
|
CREATE INDEX IF NOT EXISTS idx_contract_project_bindings_contract ON contract_project_bindings(contract_id, status, updated_at DESC);
|
|
CREATE INDEX IF NOT EXISTS idx_contract_license_events_scope ON contract_license_events(organization_id, contract_id, project_id, created_at DESC);
|
|
CREATE INDEX IF NOT EXISTS idx_service_health_status ON service_health(status, updated_at);
|
|
CREATE INDEX IF NOT EXISTS idx_auth_sessions_user ON auth_sessions(user_id, expires_at, revoked_at);
|
|
CREATE INDEX IF NOT EXISTS idx_mfa_challenges_hash ON auth_mfa_challenges(challenge_hash, expires_at, consumed_at);
|
|
CREATE INDEX IF NOT EXISTS idx_mfa_enrollment_challenges_hash ON auth_mfa_enrollment_challenges(challenge_hash, expires_at, consumed_at);
|
|
CREATE INDEX IF NOT EXISTS idx_identity_providers_enabled ON identity_providers(enabled, status);
|
|
CREATE INDEX IF NOT EXISTS idx_oidc_login_states_hash ON oidc_login_states(state_hash, expires_at, consumed_at);
|
|
CREATE INDEX IF NOT EXISTS idx_saml_login_states_relay ON saml_login_states(relay_state_hash, expires_at, consumed_at);
|
|
CREATE INDEX IF NOT EXISTS idx_saml_login_states_request ON saml_login_states(request_id, expires_at, consumed_at);
|
|
CREATE INDEX IF NOT EXISTS idx_external_identities_user ON external_identities(user_id, provider_id);
|
|
CREATE INDEX IF NOT EXISTS idx_auth_sso_tickets_hash ON auth_sso_tickets(ticket_hash, expires_at, consumed_at);
|
|
CREATE INDEX IF NOT EXISTS idx_directory_syncs_org ON directory_syncs(organization_id, enabled, created_at);
|
|
CREATE INDEX IF NOT EXISTS idx_directory_sync_tokens_hash ON directory_sync_tokens(token_hash, revoked_at);
|