schema.sql

-- 由 Alembic 迁移自动生成，请勿手工编辑。
-- 生成方式：cd backend && .venv/bin/python -m app.db.schema_export schema.sql
-- Alembic head: 71e23123e4e0

PRAGMA foreign_keys=OFF;

CREATE TABLE admin_users (
	id INTEGER NOT NULL, 
	username VARCHAR(64) NOT NULL, 
	password_hash VARCHAR(255) NOT NULL, 
	real_name VARCHAR(128) NOT NULL, 
	is_active BOOLEAN NOT NULL, 
	created_at DATETIME DEFAULT (CURRENT_TIMESTAMP) NOT NULL, 
	updated_at DATETIME DEFAULT (CURRENT_TIMESTAMP) NOT NULL, 
	CONSTRAINT pk_admin_users PRIMARY KEY (id)
);
CREATE TABLE alembic_version (
	version_num VARCHAR(32) NOT NULL, 
	CONSTRAINT alembic_version_pkc PRIMARY KEY (version_num)
);
CREATE TABLE assets (
	id INTEGER NOT NULL, 
	file_name VARCHAR(255) NOT NULL, 
	file_type VARCHAR(64) NOT NULL, 
	storage_path TEXT NOT NULL, 
	sha256 VARCHAR(64) NOT NULL, 
	metadata JSON NOT NULL, 
	created_at DATETIME DEFAULT (CURRENT_TIMESTAMP) NOT NULL, 
	updated_at DATETIME DEFAULT (CURRENT_TIMESTAMP) NOT NULL, 
	CONSTRAINT pk_assets PRIMARY KEY (id)
);
CREATE TABLE audit_logs (
	id INTEGER NOT NULL, 
	actor_username VARCHAR(64) NOT NULL, 
	action VARCHAR(128) NOT NULL, 
	resource_type VARCHAR(128) NOT NULL, 
	resource_id VARCHAR(64) NOT NULL, 
	payload JSON NOT NULL, 
	message TEXT NOT NULL, 
	created_at DATETIME DEFAULT (CURRENT_TIMESTAMP) NOT NULL, 
	updated_at DATETIME DEFAULT (CURRENT_TIMESTAMP) NOT NULL, 
	CONSTRAINT pk_audit_logs PRIMARY KEY (id)
);
CREATE TABLE email_account_credentials (
	id INTEGER NOT NULL, 
	email_account_id INTEGER NOT NULL, 
	credential_type VARCHAR(64) NOT NULL, 
	secret_encrypted TEXT NOT NULL, 
	metadata JSON NOT NULL, 
	created_at DATETIME DEFAULT (CURRENT_TIMESTAMP) NOT NULL, 
	updated_at DATETIME DEFAULT (CURRENT_TIMESTAMP) NOT NULL, 
	CONSTRAINT pk_email_account_credentials PRIMARY KEY (id), 
	CONSTRAINT fk_email_account_credentials_email_account_id_email_accounts FOREIGN KEY(email_account_id) REFERENCES email_accounts (id)
);
CREATE TABLE email_accounts (
	id INTEGER NOT NULL, 
	channel_id INTEGER, 
	proxy_id INTEGER, 
	email VARCHAR(255) NOT NULL, 
	password_encrypted TEXT NOT NULL, 
	status VARCHAR(32) NOT NULL, 
	source_type VARCHAR(32) NOT NULL, 
	tags JSON NOT NULL, 
	notes TEXT NOT NULL, 
	created_at DATETIME DEFAULT (CURRENT_TIMESTAMP) NOT NULL, 
	updated_at DATETIME DEFAULT (CURRENT_TIMESTAMP) NOT NULL, 
	CONSTRAINT pk_email_accounts PRIMARY KEY (id), 
	CONSTRAINT fk_email_accounts_channel_id_email_channels FOREIGN KEY(channel_id) REFERENCES email_channels (id), 
	CONSTRAINT fk_email_accounts_proxy_id_proxy_endpoints FOREIGN KEY(proxy_id) REFERENCES proxy_endpoints (id)
);
CREATE TABLE email_channels (
	id INTEGER NOT NULL, 
	name VARCHAR(128) NOT NULL, 
	type VARCHAR(64) NOT NULL, 
	enabled BOOLEAN NOT NULL, 
	config JSON NOT NULL, 
	encrypted_config JSON NOT NULL, 
	description TEXT NOT NULL, 
	created_at DATETIME DEFAULT (CURRENT_TIMESTAMP) NOT NULL, 
	updated_at DATETIME DEFAULT (CURRENT_TIMESTAMP) NOT NULL, 
	CONSTRAINT pk_email_channels PRIMARY KEY (id)
);
CREATE TABLE openai_accounts (
	id INTEGER NOT NULL, 
	email_account_id INTEGER, 
	openai_email VARCHAR(255) NOT NULL, 
	chatgpt_account_id VARCHAR(128) NOT NULL, 
	chatgpt_user_id VARCHAR(128) NOT NULL, 
	plan_type VARCHAR(64) NOT NULL, 
	workspace_id VARCHAR(128) NOT NULL, 
	organization_id VARCHAR(128) NOT NULL, 
	status VARCHAR(32) NOT NULL, 
	metadata JSON NOT NULL, 
	created_at DATETIME DEFAULT (CURRENT_TIMESTAMP) NOT NULL, 
	updated_at DATETIME DEFAULT (CURRENT_TIMESTAMP) NOT NULL, 
	CONSTRAINT pk_openai_accounts PRIMARY KEY (id), 
	CONSTRAINT fk_openai_accounts_email_account_id_email_accounts FOREIGN KEY(email_account_id) REFERENCES email_accounts (id)
);
CREATE TABLE openai_authorizations (
	id INTEGER NOT NULL, 
	openai_account_id INTEGER NOT NULL, 
	access_token_encrypted TEXT NOT NULL, 
	refresh_token_encrypted TEXT NOT NULL, 
	id_token_encrypted TEXT NOT NULL, 
	expires_at DATETIME, 
	auth_source VARCHAR(64) NOT NULL, 
	metadata JSON NOT NULL, 
	created_at DATETIME DEFAULT (CURRENT_TIMESTAMP) NOT NULL, 
	updated_at DATETIME DEFAULT (CURRENT_TIMESTAMP) NOT NULL, 
	CONSTRAINT pk_openai_authorizations PRIMARY KEY (id), 
	CONSTRAINT fk_openai_authorizations_openai_account_id_openai_accounts FOREIGN KEY(openai_account_id) REFERENCES openai_accounts (id)
);
CREATE TABLE proxy_endpoints (
	id INTEGER NOT NULL, 
	name VARCHAR(128) NOT NULL, 
	proxy_url_encrypted TEXT NOT NULL, 
	protocol VARCHAR(32) NOT NULL, 
	region VARCHAR(64) NOT NULL, 
	enabled BOOLEAN NOT NULL, 
	health_status VARCHAR(32) NOT NULL, 
	created_at DATETIME DEFAULT (CURRENT_TIMESTAMP) NOT NULL, 
	updated_at DATETIME DEFAULT (CURRENT_TIMESTAMP) NOT NULL, 
	CONSTRAINT pk_proxy_endpoints PRIMARY KEY (id), 
	CONSTRAINT uq_proxy_endpoints_name UNIQUE (name)
);
CREATE TABLE sync_jobs (
	id INTEGER NOT NULL, 
	target_id INTEGER, 
	source_type VARCHAR(64) NOT NULL, 
	status VARCHAR(32) NOT NULL, 
	payload JSON NOT NULL, 
	result JSON NOT NULL, 
	created_at DATETIME DEFAULT (CURRENT_TIMESTAMP) NOT NULL, 
	updated_at DATETIME DEFAULT (CURRENT_TIMESTAMP) NOT NULL, 
	CONSTRAINT pk_sync_jobs PRIMARY KEY (id), 
	CONSTRAINT fk_sync_jobs_target_id_sync_targets FOREIGN KEY(target_id) REFERENCES sync_targets (id)
);
CREATE TABLE sync_records (
	id INTEGER NOT NULL, 
	sync_job_id INTEGER, 
	source_openai_account_id INTEGER, 
	target_id INTEGER, 
	action VARCHAR(32) NOT NULL, 
	status VARCHAR(32) NOT NULL, 
	remote_identity VARCHAR(128) NOT NULL, 
	message TEXT NOT NULL, 
	raw_response JSON NOT NULL, 
	created_at DATETIME DEFAULT (CURRENT_TIMESTAMP) NOT NULL, 
	updated_at DATETIME DEFAULT (CURRENT_TIMESTAMP) NOT NULL, 
	CONSTRAINT pk_sync_records PRIMARY KEY (id), 
	CONSTRAINT fk_sync_records_source_openai_account_id_openai_accounts FOREIGN KEY(source_openai_account_id) REFERENCES openai_accounts (id), 
	CONSTRAINT fk_sync_records_sync_job_id_sync_jobs FOREIGN KEY(sync_job_id) REFERENCES sync_jobs (id), 
	CONSTRAINT fk_sync_records_target_id_sync_targets FOREIGN KEY(target_id) REFERENCES sync_targets (id)
);
CREATE TABLE sync_targets (
	id INTEGER NOT NULL, 
	name VARCHAR(128) NOT NULL, 
	type VARCHAR(64) NOT NULL, 
	enabled BOOLEAN NOT NULL, 
	config JSON NOT NULL, 
	encrypted_config JSON NOT NULL, 
	description TEXT NOT NULL, 
	created_at DATETIME DEFAULT (CURRENT_TIMESTAMP) NOT NULL, 
	updated_at DATETIME DEFAULT (CURRENT_TIMESTAMP) NOT NULL, 
	CONSTRAINT pk_sync_targets PRIMARY KEY (id)
);
CREATE TABLE system_settings (
	id INTEGER NOT NULL, 
	"key" VARCHAR(128) NOT NULL, 
	value JSON NOT NULL, 
	description TEXT NOT NULL, 
	created_at DATETIME DEFAULT (CURRENT_TIMESTAMP) NOT NULL, 
	updated_at DATETIME DEFAULT (CURRENT_TIMESTAMP) NOT NULL, 
	CONSTRAINT pk_system_settings PRIMARY KEY (id)
);
CREATE TABLE task_logs (
	id INTEGER NOT NULL, 
	task_id INTEGER NOT NULL, 
	level VARCHAR(32) NOT NULL, 
	message TEXT NOT NULL, 
	data JSON NOT NULL, 
	created_at DATETIME DEFAULT (CURRENT_TIMESTAMP) NOT NULL, 
	updated_at DATETIME DEFAULT (CURRENT_TIMESTAMP) NOT NULL, 
	CONSTRAINT pk_task_logs PRIMARY KEY (id), 
	CONSTRAINT fk_task_logs_task_id_tasks FOREIGN KEY(task_id) REFERENCES tasks (id)
);
CREATE TABLE task_steps (
	id INTEGER NOT NULL, 
	task_id INTEGER NOT NULL, 
	step_name VARCHAR(128) NOT NULL, 
	step_status VARCHAR(32) NOT NULL, 
	message TEXT NOT NULL, 
	data JSON NOT NULL, 
	created_at DATETIME DEFAULT (CURRENT_TIMESTAMP) NOT NULL, 
	updated_at DATETIME DEFAULT (CURRENT_TIMESTAMP) NOT NULL, 
	CONSTRAINT pk_task_steps PRIMARY KEY (id), 
	CONSTRAINT fk_task_steps_task_id_tasks FOREIGN KEY(task_id) REFERENCES tasks (id)
);
CREATE TABLE tasks (
	id INTEGER NOT NULL, 
	task_type VARCHAR(64) NOT NULL, 
	status VARCHAR(32) NOT NULL, 
	priority INTEGER NOT NULL, 
	payload JSON NOT NULL, 
	result JSON NOT NULL, 
	error_message TEXT NOT NULL, 
	created_by VARCHAR(64) NOT NULL, 
	created_at DATETIME DEFAULT (CURRENT_TIMESTAMP) NOT NULL, 
	updated_at DATETIME DEFAULT (CURRENT_TIMESTAMP) NOT NULL, 
	CONSTRAINT pk_tasks PRIMARY KEY (id)
);
CREATE UNIQUE INDEX ix_admin_users_username ON admin_users (username);
CREATE INDEX ix_assets_sha256 ON assets (sha256);
CREATE INDEX ix_audit_logs_action ON audit_logs (action);
CREATE INDEX ix_audit_logs_actor_username ON audit_logs (actor_username);
CREATE INDEX ix_audit_logs_resource_type ON audit_logs (resource_type);
CREATE INDEX ix_email_account_credentials_credential_type ON email_account_credentials (credential_type);
CREATE INDEX ix_email_account_credentials_email_account_id ON email_account_credentials (email_account_id);
CREATE UNIQUE INDEX ix_email_accounts_email ON email_accounts (email);
CREATE INDEX ix_email_accounts_status ON email_accounts (status);
CREATE UNIQUE INDEX ix_email_channels_name ON email_channels (name);
CREATE INDEX ix_email_channels_type ON email_channels (type);
CREATE INDEX ix_openai_accounts_chatgpt_account_id ON openai_accounts (chatgpt_account_id);
CREATE INDEX ix_openai_accounts_email_account_id ON openai_accounts (email_account_id);
CREATE INDEX ix_openai_accounts_openai_email ON openai_accounts (openai_email);
CREATE INDEX ix_openai_accounts_status ON openai_accounts (status);
CREATE INDEX ix_openai_authorizations_openai_account_id ON openai_authorizations (openai_account_id);
CREATE INDEX ix_sync_jobs_source_type ON sync_jobs (source_type);
CREATE INDEX ix_sync_jobs_status ON sync_jobs (status);
CREATE INDEX ix_sync_jobs_target_id ON sync_jobs (target_id);
CREATE INDEX ix_sync_records_source_openai_account_id ON sync_records (source_openai_account_id);
CREATE INDEX ix_sync_records_status ON sync_records (status);
CREATE INDEX ix_sync_records_sync_job_id ON sync_records (sync_job_id);
CREATE INDEX ix_sync_records_target_id ON sync_records (target_id);
CREATE UNIQUE INDEX ix_sync_targets_name ON sync_targets (name);
CREATE INDEX ix_sync_targets_type ON sync_targets (type);
CREATE UNIQUE INDEX ix_system_settings_key ON system_settings ("key");
CREATE INDEX ix_task_logs_task_id ON task_logs (task_id);
CREATE INDEX ix_task_steps_step_name ON task_steps (step_name);
CREATE INDEX ix_task_steps_task_id ON task_steps (task_id);
CREATE INDEX ix_tasks_status ON tasks (status);
CREATE INDEX ix_tasks_task_type ON tasks (task_type);
INSERT INTO alembic_version (version_num) VALUES ('71e23123e4e0');

PRAGMA foreign_keys=ON;