82 lines
2.4 KiB
SQL
82 lines
2.4 KiB
SQL
-- cfw-attachment 初始 schema：附件、分类、标签、业务关联、审计
|
||
CREATE TABLE attachments (
|
||
id TEXT PRIMARY KEY,
|
||
user_id TEXT NOT NULL,
|
||
status TEXT NOT NULL,
|
||
filename TEXT NOT NULL,
|
||
content_type TEXT NOT NULL,
|
||
byte_size INTEGER,
|
||
checksum_sha256 TEXT,
|
||
visibility TEXT NOT NULL DEFAULT 'private',
|
||
category_id TEXT,
|
||
description TEXT,
|
||
object_key TEXT,
|
||
etag TEXT,
|
||
created_by_user_id TEXT NOT NULL,
|
||
updated_by_user_id TEXT NOT NULL,
|
||
deleted_by_user_id TEXT,
|
||
created_at TEXT NOT NULL,
|
||
updated_at TEXT NOT NULL,
|
||
deleted_at TEXT
|
||
);
|
||
|
||
CREATE TABLE attachment_categories (
|
||
id TEXT PRIMARY KEY,
|
||
user_id TEXT NOT NULL,
|
||
name TEXT NOT NULL,
|
||
slug TEXT NOT NULL,
|
||
created_at TEXT NOT NULL,
|
||
updated_at TEXT NOT NULL,
|
||
UNIQUE(user_id, slug)
|
||
);
|
||
|
||
CREATE TABLE attachment_tags (
|
||
id TEXT PRIMARY KEY,
|
||
user_id TEXT NOT NULL,
|
||
name TEXT NOT NULL,
|
||
slug TEXT NOT NULL,
|
||
created_at TEXT NOT NULL,
|
||
updated_at TEXT NOT NULL,
|
||
UNIQUE(user_id, slug)
|
||
);
|
||
|
||
CREATE TABLE attachment_tag_links (
|
||
attachment_id TEXT NOT NULL,
|
||
tag_id TEXT NOT NULL,
|
||
user_id TEXT NOT NULL,
|
||
created_at TEXT NOT NULL,
|
||
PRIMARY KEY (attachment_id, tag_id),
|
||
FOREIGN KEY (attachment_id) REFERENCES attachments(id),
|
||
FOREIGN KEY (tag_id) REFERENCES attachment_tags(id)
|
||
);
|
||
|
||
CREATE TABLE attachment_links (
|
||
id TEXT PRIMARY KEY,
|
||
user_id TEXT NOT NULL,
|
||
attachment_id TEXT NOT NULL,
|
||
owner_type TEXT NOT NULL,
|
||
owner_id TEXT NOT NULL,
|
||
purpose TEXT NOT NULL,
|
||
sort_order INTEGER NOT NULL DEFAULT 0,
|
||
created_by_user_id TEXT NOT NULL,
|
||
created_at TEXT NOT NULL,
|
||
UNIQUE(user_id, owner_type, owner_id, purpose, attachment_id)
|
||
);
|
||
|
||
CREATE TABLE attachment_audit_events (
|
||
id TEXT PRIMARY KEY,
|
||
user_id TEXT NOT NULL,
|
||
attachment_id TEXT,
|
||
actor_user_id TEXT NOT NULL,
|
||
action TEXT NOT NULL,
|
||
detail_json TEXT NOT NULL,
|
||
created_at TEXT NOT NULL
|
||
);
|
||
|
||
CREATE INDEX idx_attachments_user_status ON attachments(user_id, status, created_at);
|
||
CREATE INDEX idx_attachments_user_category ON attachments(user_id, category_id, created_at);
|
||
CREATE INDEX idx_attachment_tags_user_slug ON attachment_tags(user_id, slug);
|
||
CREATE INDEX idx_attachment_tag_links_tag ON attachment_tag_links(user_id, tag_id);
|
||
CREATE INDEX idx_attachment_links_owner ON attachment_links(user_id, owner_type, owner_id, purpose);
|
||
CREATE INDEX idx_attachment_audit_user_attachment ON attachment_audit_events(user_id, attachment_id, created_at);
|