-- 账号、档案与关注各自拥有用户归属;时间以 UTC 写入,API 返回 ISO 8601。 CREATE TABLE app_user ( id VARCHAR(36) PRIMARY KEY, username VARCHAR(64) NOT NULL UNIQUE, password_hash VARCHAR(100) NOT NULL, created_at TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ); CREATE TABLE company_profile ( user_id VARCHAR(36) PRIMARY KEY, FOREIGN KEY (user_id) REFERENCES app_user(id), document MEDIUMTEXT NOT NULL, version BIGINT NOT NULL, completed BOOLEAN NOT NULL, updated_at TIMESTAMP(6) NOT NULL ); -- 公告是公共事实;关注记录是个人操作,不用关注状态覆盖公告阶段。 CREATE TABLE project ( id VARCHAR(64) PRIMARY KEY, source_id VARCHAR(128) NOT NULL UNIQUE, document MEDIUMTEXT NOT NULL, created_at TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ); CREATE TABLE watch_record ( id VARCHAR(36) PRIMARY KEY, user_id VARCHAR(36) NOT NULL, FOREIGN KEY (user_id) REFERENCES app_user(id), project_id VARCHAR(64) NOT NULL, FOREIGN KEY (project_id) REFERENCES project(id), status VARCHAR(32) NOT NULL, note TEXT NOT NULL, version BIGINT NOT NULL DEFAULT 1, created_at TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6), UNIQUE(user_id, project_id) ); CREATE TABLE watch_history ( id VARCHAR(36) PRIMARY KEY, watch_id VARCHAR(36) NOT NULL, FOREIGN KEY (watch_id) REFERENCES watch_record(id) ON DELETE CASCADE, status VARCHAR(32) NOT NULL, note TEXT NOT NULL, actor_id VARCHAR(36) NOT NULL, FOREIGN KEY (actor_id) REFERENCES app_user(id), created_at TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ); CREATE TABLE tag ( id VARCHAR(36) PRIMARY KEY, user_id VARCHAR(36) NOT NULL, FOREIGN KEY (user_id) REFERENCES app_user(id), name VARCHAR(64) NOT NULL, UNIQUE(user_id, name) ); CREATE TABLE watch_tag ( watch_id VARCHAR(36) NOT NULL, FOREIGN KEY (watch_id) REFERENCES watch_record(id) ON DELETE CASCADE, tag_id VARCHAR(36) NOT NULL, FOREIGN KEY (tag_id) REFERENCES tag(id) ON DELETE CASCADE, PRIMARY KEY(watch_id, tag_id) );