Files

324 lines
14 KiB
SQL

-- +goose Up
-- =====================================================================
-- NOYAU POC : tables et index
-- =====================================================================
-- Cible : SQLite >= 3.37 (tables STRICT), JSON1 intégré (>= 3.38)
-- Connexion : les PRAGMAs sont appliqués par le DSN (voir database.go) :
-- foreign_keys = ON, journal_mode = WAL,
-- synchronous = NORMAL, busy_timeout = 5000
-- Un seul écrivain (WriteDB), plusieurs lecteurs (DB).
-- Conventions
-- - Identifiants : UUIDv7 en BLOB de 16 octets (sauf amorçages).
-- - Dates : entier, millisecondes Unix UTC.
-- - JSON : TEXT validé par CHECK (json_valid(...)).
-- - Hash : BLOB multihash (BLAKE3).
-- - Suppression : deleted_at (corbeille), jamais de purge immédiate.
-- =====================================================================
-- ---------------------------------------------------------------------
-- 1. IDENTITÉS ET GROUPES
-- ---------------------------------------------------------------------
CREATE TABLE principal (
id BLOB PRIMARY KEY CHECK (length(id) = 16),
kind TEXT NOT NULL CHECK (kind IN ('cluster','user','guest','group','system')),
name TEXT NOT NULL CHECK (length(name) > 0),
public_key BLOB,
status TEXT NOT NULL DEFAULT 'active'
CHECK (status IN ('active','disabled','revoked')),
successor_id BLOB REFERENCES principal(id),
created_at INTEGER NOT NULL,
disabled_at INTEGER,
CHECK (kind <> 'cluster' OR public_key IS NOT NULL),
CHECK (successor_id IS NULL OR successor_id <> id)
) STRICT;
CREATE UNIQUE INDEX principal_name ON principal(kind, name) WHERE status <> 'revoked';
CREATE UNIQUE INDEX principal_single_cluster ON principal(kind) WHERE kind = 'cluster';
CREATE INDEX principal_successor ON principal(successor_id) WHERE successor_id IS NOT NULL;
CREATE TABLE membership (
group_id BLOB NOT NULL CHECK (length(group_id) = 16) REFERENCES principal(id) ON DELETE CASCADE,
member_id BLOB NOT NULL CHECK (length(member_id) = 16) REFERENCES principal(id) ON DELETE CASCADE,
role TEXT NOT NULL DEFAULT 'member' CHECK (role IN ('member','admin')),
created_at INTEGER NOT NULL,
PRIMARY KEY (group_id, member_id)
) STRICT, WITHOUT ROWID;
CREATE INDEX membership_member ON membership(member_id);
-- ---------------------------------------------------------------------
-- 2. COMPTES ET SESSIONS
-- ---------------------------------------------------------------------
CREATE TABLE account (
principal_id BLOB PRIMARY KEY CHECK (length(principal_id) = 16)
REFERENCES principal(id) ON DELETE CASCADE,
login TEXT NOT NULL CHECK (length(login) > 0),
login_key TEXT NOT NULL CHECK (length(login_key) > 0),
password_hash TEXT,
password_changed_at INTEGER,
must_change_password INTEGER NOT NULL DEFAULT 0 CHECK (must_change_password IN (0,1)),
state TEXT NOT NULL DEFAULT 'active' CHECK (state IN ('active','locked','disabled')),
failed_attempts INTEGER NOT NULL DEFAULT 0 CHECK (failed_attempts >= 0),
locked_until INTEGER,
last_login_at INTEGER,
created_at INTEGER NOT NULL
) STRICT;
CREATE UNIQUE INDEX account_login_key ON account(login_key);
CREATE TABLE session (
id BLOB PRIMARY KEY CHECK (length(id) = 16),
token_hash BLOB NOT NULL UNIQUE CHECK (length(token_hash) = 32),
principal_id BLOB NOT NULL CHECK (length(principal_id) = 16)
REFERENCES principal(id) ON DELETE CASCADE,
kind TEXT NOT NULL DEFAULT 'web' CHECK (kind IN ('web','api')),
label TEXT,
user_agent TEXT,
ip_prefix TEXT,
created_at INTEGER NOT NULL,
last_seen_at INTEGER NOT NULL,
expires_at INTEGER NOT NULL,
idle_expires_at INTEGER NOT NULL,
revoked_at INTEGER
) STRICT;
CREATE INDEX session_principal ON session(principal_id) WHERE revoked_at IS NULL;
CREATE INDEX session_expiry ON session(expires_at);
-- ---------------------------------------------------------------------
-- 3. TYPES, POLITIQUES, MAGASIN DE BLOBS
-- ---------------------------------------------------------------------
CREATE TABLE document_kind (
kind TEXT PRIMARY KEY,
provider TEXT NOT NULL DEFAULT 'core',
has_content INTEGER NOT NULL CHECK (has_content IN (0,1)),
hidden INTEGER NOT NULL DEFAULT 0 CHECK (hidden IN (0,1)),
metadata_schema TEXT CHECK (metadata_schema IS NULL OR json_valid(metadata_schema))
) STRICT;
CREATE TABLE storage_policy (
id BLOB PRIMARY KEY CHECK (length(id) = 16),
name TEXT NOT NULL UNIQUE,
mode TEXT NOT NULL CHECK (mode IN ('cloud','sync')),
min_remote_replicas INTEGER NOT NULL DEFAULT 0 CHECK (min_remote_replicas >= 0),
replicate_history INTEGER NOT NULL DEFAULT 0 CHECK (replicate_history IN (0,1)),
cache_ttl_s INTEGER CHECK (cache_ttl_s IS NULL OR cache_ttl_s >= 0),
cache_max_bytes INTEGER CHECK (cache_max_bytes IS NULL OR cache_max_bytes >= 0),
version_retention TEXT NOT NULL
DEFAULT '{"keep_all_days":30,"keep_daily_days":0,"keep_monthly":0}'
CHECK (json_valid(version_retention)),
created_at INTEGER NOT NULL
) STRICT;
CREATE TABLE storage_backend (
id BLOB PRIMARY KEY CHECK (length(id) = 16),
kind TEXT NOT NULL CHECK (kind IN ('hub_fs','s3','peer')),
name TEXT NOT NULL UNIQUE,
config TEXT NOT NULL CHECK (json_valid(config)),
secret_ref TEXT,
enabled INTEGER NOT NULL DEFAULT 1 CHECK (enabled IN (0,1)),
capacity_bytes INTEGER CHECK (capacity_bytes IS NULL OR capacity_bytes >= 0),
state TEXT NOT NULL DEFAULT 'unknown'
CHECK (state IN ('unknown','ok','degraded','failing')),
last_ok_at INTEGER,
last_error TEXT,
created_at INTEGER NOT NULL
) STRICT;
CREATE TABLE blob (
root BLOB PRIMARY KEY CHECK (length(root) > 0),
size INTEGER NOT NULL CHECK (size >= 0),
state TEXT NOT NULL CHECK (state IN ('partial','present','corrupt')),
role TEXT NOT NULL DEFAULT 'authoritative' CHECK (role IN ('authoritative','cache')),
unit_size INTEGER NOT NULL DEFAULT 1048576 CHECK (unit_size > 0),
have BLOB,
tree_state TEXT NOT NULL DEFAULT 'none' CHECK (tree_state IN ('none','building','ready')),
encryption TEXT NOT NULL DEFAULT 'none' CHECK (encryption IN ('none','client_v1')),
last_verified_at INTEGER,
verify_error TEXT,
last_access_at INTEGER,
created_at INTEGER NOT NULL
) STRICT;
CREATE INDEX blob_state ON blob(state) WHERE state <> 'present';
CREATE INDEX blob_cache ON blob(last_access_at) WHERE role = 'cache';
CREATE INDEX blob_verify ON blob(last_verified_at) WHERE state = 'present';
CREATE TABLE blob_location (
root BLOB NOT NULL REFERENCES blob(root) ON DELETE CASCADE,
backend_id BLOB NOT NULL CHECK (length(backend_id) = 16) REFERENCES storage_backend(id),
locator TEXT NOT NULL CHECK (length(locator) > 0),
state TEXT NOT NULL CHECK (state IN ('present','pending_delete','missing')),
created_at INTEGER NOT NULL,
PRIMARY KEY (root, backend_id),
UNIQUE (backend_id, locator)
) STRICT, WITHOUT ROWID;
CREATE INDEX blob_location_backend ON blob_location(backend_id);
CREATE TABLE staging_item (
id BLOB PRIMARY KEY CHECK (length(id) = 16),
origin TEXT NOT NULL CHECK (origin IN ('import','upload','relay_cache')),
job_ref TEXT,
locator TEXT NOT NULL CHECK (length(locator) > 0),
size INTEGER CHECK (size IS NULL OR size >= 0),
state TEXT NOT NULL CHECK (state IN ('staged','hashing','committed','discarded')),
created_at INTEGER NOT NULL,
updated_at INTEGER NOT NULL
) STRICT;
CREATE INDEX staging_state ON staging_item(state, updated_at);
-- ---------------------------------------------------------------------
-- 4. DOCUMENTS ET VERSIONS
-- ---------------------------------------------------------------------
CREATE TABLE document (
id BLOB PRIMARY KEY CHECK (length(id) = 16),
kind TEXT NOT NULL REFERENCES document_kind(kind),
owner_id BLOB NOT NULL REFERENCES principal(id),
created_by BLOB NOT NULL REFERENCES principal(id),
parent_id BLOB REFERENCES document(id),
name TEXT NOT NULL CHECK (length(name) > 0 AND instr(name, '/') = 0),
name_key TEXT NOT NULL CHECK (length(name_key) > 0),
path TEXT NOT NULL CHECK (path = lower(path)),
depth INTEGER NOT NULL CHECK (depth >= 0),
position TEXT,
head_version_id BLOB REFERENCES document_version(id) DEFERRABLE INITIALLY DEFERRED,
metadata TEXT NOT NULL DEFAULT '{}' CHECK (json_valid(metadata)),
policy_id BLOB REFERENCES storage_policy(id),
visibility TEXT CHECK (visibility IN ('PRIVATE','CLUSTER','FEDERATED','PUBLIC')),
acl_inherit INTEGER NOT NULL DEFAULT 1 CHECK (acl_inherit IN (0,1)),
rev INTEGER NOT NULL DEFAULT 1 CHECK (rev > 0),
created_at INTEGER NOT NULL,
updated_at INTEGER NOT NULL,
deleted_at INTEGER,
deleted_by BLOB REFERENCES principal(id),
trash_batch BLOB,
CHECK ((parent_id IS NULL) = (depth = 0)),
CHECK (path LIKE '%' || lower(hex(id)) || '/'),
CHECK (parent_id IS NOT NULL OR path = lower(hex(id)) || '/'),
CHECK (parent_id IS NOT NULL OR kind = 'library'),
CHECK (parent_id IS NOT NULL OR (visibility IS NOT NULL AND policy_id IS NOT NULL)),
CHECK (parent_id IS NULL OR parent_id <> id)
) STRICT;
CREATE TABLE document_version (
id BLOB PRIMARY KEY CHECK (length(id) = 16),
document_id BLOB NOT NULL REFERENCES document(id),
seq INTEGER NOT NULL CHECK (seq > 0),
content_root BLOB NOT NULL CHECK (length(content_root) > 0),
size INTEGER NOT NULL CHECK (size >= 0),
mime TEXT,
author_id BLOB NOT NULL REFERENCES principal(id),
device_id BLOB,
origin TEXT NOT NULL CHECK (origin IN ('upload','import','merge','restore','system')),
message TEXT,
pinned INTEGER NOT NULL DEFAULT 0 CHECK (pinned IN (0,1)),
content_purged_at INTEGER,
created_at INTEGER NOT NULL,
UNIQUE (document_id, seq)
) STRICT;
CREATE UNIQUE INDEX doc_sibling_name
ON document(ifnull(parent_id, zeroblob(16)), name_key) WHERE deleted_at IS NULL;
CREATE INDEX doc_path ON document(path);
CREATE INDEX doc_parent ON document(parent_id, position, name_key) WHERE deleted_at IS NULL;
CREATE INDEX doc_owner ON document(owner_id);
CREATE INDEX doc_kind ON document(kind);
CREATE INDEX doc_trash ON document(trash_batch) WHERE trash_batch IS NOT NULL;
CREATE INDEX doc_head ON document(head_version_id);
CREATE INDEX doc_created_by ON document(created_by);
CREATE INDEX version_doc ON document_version(document_id, seq DESC);
CREATE INDEX version_blob ON document_version(content_root);
CREATE INDEX version_author ON document_version(author_id);
-- ---------------------------------------------------------------------
-- 5. DROITS
-- ---------------------------------------------------------------------
CREATE TABLE acl_entry (
id BLOB PRIMARY KEY CHECK (length(id) = 16),
document_id BLOB NOT NULL REFERENCES document(id),
subject_id BLOB NOT NULL REFERENCES principal(id),
permissions INTEGER NOT NULL CHECK (permissions BETWEEN 1 AND 127),
propagate INTEGER NOT NULL DEFAULT 1 CHECK (propagate IN (0,1)),
expires_at INTEGER,
granted_by BLOB NOT NULL REFERENCES principal(id),
created_at INTEGER NOT NULL,
revoked_at INTEGER,
UNIQUE (document_id, subject_id)
) STRICT;
CREATE INDEX acl_doc ON acl_entry(document_id) WHERE revoked_at IS NULL;
CREATE INDEX acl_subject ON acl_entry(subject_id) WHERE revoked_at IS NULL;
CREATE TABLE library_config (
document_id BLOB PRIMARY KEY REFERENCES document(id),
protection TEXT NOT NULL DEFAULT 'server' CHECK (protection IN ('server','vault')),
admin_visible INTEGER NOT NULL DEFAULT 0 CHECK (admin_visible IN (0,1)),
created_at INTEGER NOT NULL
) STRICT;
-- ---------------------------------------------------------------------
-- 6. EXPORT VERS JELLYFIN
-- ---------------------------------------------------------------------
CREATE TABLE export_target (
id BLOB PRIMARY KEY CHECK (length(id) = 16),
library_id BLOB NOT NULL REFERENCES document(id),
root_path TEXT NOT NULL CHECK (length(root_path) > 0),
mode TEXT NOT NULL DEFAULT 'hardlink' CHECK (mode IN ('hardlink','symlink','copy')),
enabled INTEGER NOT NULL DEFAULT 1 CHECK (enabled IN (0,1)),
check_state TEXT NOT NULL DEFAULT 'unchecked' CHECK (check_state IN ('unchecked','ok','failed')),
last_check_at INTEGER,
last_error TEXT,
consent_by BLOB REFERENCES principal(id),
UNIQUE (library_id, root_path)
) STRICT;
CREATE INDEX export_target_library ON export_target(library_id);
CREATE TABLE export_link (
target_id BLOB NOT NULL REFERENCES export_target(id) ON DELETE CASCADE,
document_id BLOB NOT NULL REFERENCES document(id),
relpath TEXT NOT NULL CHECK (
length(relpath) > 0
AND substr(relpath, 1, 1) <> '/'
AND instr('/' || relpath || '/', '/../') = 0
AND instr('/' || relpath || '/', '/./') = 0
),
blob_root BLOB NOT NULL CHECK (length(blob_root) > 0),
state TEXT NOT NULL CHECK (state IN ('ok','pending','stale','error')),
updated_at INTEGER NOT NULL,
PRIMARY KEY (target_id, document_id),
UNIQUE (target_id, relpath)
) STRICT;
CREATE INDEX export_link_document ON export_link(document_id);
-- +goose Down
PRAGMA defer_foreign_keys = ON;
DROP TABLE IF EXISTS export_link;
DROP TABLE IF EXISTS export_target;
DROP TABLE IF EXISTS library_config;
DROP TABLE IF EXISTS acl_entry;
DROP TABLE IF EXISTS document;
DROP TABLE IF EXISTS document_version;
DROP TABLE IF EXISTS staging_item;
DROP TABLE IF EXISTS blob_location;
DROP TABLE IF EXISTS blob;
DROP TABLE IF EXISTS storage_backend;
DROP TABLE IF EXISTS storage_policy;
DROP TABLE IF EXISTS session;
DROP TABLE IF EXISTS account;
DROP TABLE IF EXISTS membership;
DROP TABLE IF EXISTS principal;
DROP TABLE IF EXISTS document_kind;