spb/drive Public

1-- ─────────────────────────────────────────────2--  SPB Drive — Personal Cloud Drive3-- ─────────────────────────────────────────────4--  Author  : Simon-Pierre Boucher5--  Contact : contact@spboucher.ai6--  File    : src/db/schema.sql7--  Purpose : Full SQLite schema — nodes tree, blobs, shares, sessions, FTS58--  License : MIT © Simon-Pierre Boucher9-- ─────────────────────────────────────────────1011PRAGMA journal_mode = WAL;1213CREATE TABLE IF NOT EXISTS meta (14  key   TEXT PRIMARY KEY,15  value TEXT NOT NULL16);1718-- Content-addressed blob registry. Path on disk: files/<sha[0:2]>/<sha[2:4]>/<sha>19CREATE TABLE IF NOT EXISTS blobs (20  sha      TEXT PRIMARY KEY,21  size     INTEGER NOT NULL,22  refcount INTEGER NOT NULL DEFAULT 0,23  created  INTEGER NOT NULL24);2526-- Virtual folder tree. Root is id=1 (parent_id NULL, type 'folder').27CREATE TABLE IF NOT EXISTS nodes (28  id          INTEGER PRIMARY KEY AUTOINCREMENT,29  parent_id   INTEGER REFERENCES nodes(id) ON DELETE CASCADE,30  name        TEXT NOT NULL,31  type        TEXT NOT NULL CHECK (type IN ('file', 'folder')),32  blob_sha    TEXT REFERENCES blobs(sha),33  size        INTEGER NOT NULL DEFAULT 0,34  mime        TEXT,35  created     INTEGER NOT NULL,36  modified    INTEGER NOT NULL,37  starred     INTEGER NOT NULL DEFAULT 0,38  color       TEXT,39  emoji       TEXT,40  trashed_at  INTEGER,41  trash_orig_parent INTEGER42);4344CREATE INDEX IF NOT EXISTS idx_nodes_parent  ON nodes(parent_id, trashed_at);45CREATE INDEX IF NOT EXISTS idx_nodes_blob    ON nodes(blob_sha);46CREATE INDEX IF NOT EXISTS idx_nodes_trashed ON nodes(trashed_at) WHERE trashed_at IS NOT NULL;47CREATE INDEX IF NOT EXISTS idx_nodes_starred ON nodes(starred) WHERE starred = 1;48CREATE INDEX IF NOT EXISTS idx_nodes_recent  ON nodes(modified DESC);4950CREATE TABLE IF NOT EXISTS shares (51  id             INTEGER PRIMARY KEY AUTOINCREMENT,52  token          TEXT NOT NULL UNIQUE,53  node_id        INTEGER NOT NULL REFERENCES nodes(id) ON DELETE CASCADE,54  created        INTEGER NOT NULL,55  expires_at     INTEGER,56  password_hash  TEXT,57  max_downloads  INTEGER,58  downloads      INTEGER NOT NULL DEFAULT 0,59  visits         INTEGER NOT NULL DEFAULT 0,60  allow_download INTEGER NOT NULL DEFAULT 1,61  label          TEXT,62  revoked_at     INTEGER63);6465CREATE INDEX IF NOT EXISTS idx_shares_node ON shares(node_id);6667CREATE TABLE IF NOT EXISTS share_events (68  id       INTEGER PRIMARY KEY AUTOINCREMENT,69  share_id INTEGER NOT NULL REFERENCES shares(id) ON DELETE CASCADE,70  ts       INTEGER NOT NULL,71  kind     TEXT NOT NULL CHECK (kind IN ('visit', 'download')),72  ip       TEXT,73  ua       TEXT74);7576CREATE INDEX IF NOT EXISTS idx_share_events_share ON share_events(share_id, ts DESC);7778CREATE TABLE IF NOT EXISTS tags (79  id    INTEGER PRIMARY KEY AUTOINCREMENT,80  name  TEXT NOT NULL UNIQUE,81  color TEXT NOT NULL DEFAULT '#4f8cff'82);8384CREATE TABLE IF NOT EXISTS node_tags (85  node_id INTEGER NOT NULL REFERENCES nodes(id) ON DELETE CASCADE,86  tag_id  INTEGER NOT NULL REFERENCES tags(id) ON DELETE CASCADE,87  PRIMARY KEY (node_id, tag_id)88);8990-- Session ids are stored hashed (sha256) — a DB leak never leaks live cookies.91CREATE TABLE IF NOT EXISTS sessions (92  id         INTEGER PRIMARY KEY AUTOINCREMENT,93  token_hash TEXT NOT NULL UNIQUE,94  created    INTEGER NOT NULL,95  last_seen  INTEGER NOT NULL,96  expires_at INTEGER NOT NULL,97  remember   INTEGER NOT NULL DEFAULT 0,98  ip         TEXT,99  ua         TEXT100);101102CREATE TABLE IF NOT EXISTS api_tokens (103  id         INTEGER PRIMARY KEY AUTOINCREMENT,104  name       TEXT NOT NULL,105  token_hash TEXT NOT NULL UNIQUE,106  created    INTEGER NOT NULL,107  last_used  INTEGER108);109110CREATE TABLE IF NOT EXISTS activity (111  id      INTEGER PRIMARY KEY AUTOINCREMENT,112  ts      INTEGER NOT NULL,113  kind    TEXT NOT NULL,114  node_id INTEGER,115  detail  TEXT,116  ip      TEXT117);118119CREATE INDEX IF NOT EXISTS idx_activity_ts ON activity(ts DESC);120121CREATE TABLE IF NOT EXISTS login_attempts (122  ip           TEXT PRIMARY KEY,123  fail_count   INTEGER NOT NULL DEFAULT 0,124  last_fail    INTEGER,125  locked_until INTEGER126);127128-- Resumable upload sessions survive server restarts.129CREATE TABLE IF NOT EXISTS uploads (130  id         TEXT PRIMARY KEY,131  parent_id  INTEGER NOT NULL,132  name       TEXT NOT NULL,133  size       INTEGER NOT NULL,134  chunk_size INTEGER NOT NULL,135  n_chunks   INTEGER NOT NULL,136  created    INTEGER NOT NULL137);138139-- Version history: superseded blobs kept when a file is replaced or edited.140-- Each row holds one refcount on its blob (released when the row is pruned).141CREATE TABLE IF NOT EXISTS node_versions (142  id          INTEGER PRIMARY KEY AUTOINCREMENT,143  node_id     INTEGER NOT NULL REFERENCES nodes(id) ON DELETE CASCADE,144  blob_sha    TEXT NOT NULL REFERENCES blobs(sha),145  size        INTEGER NOT NULL,146  mime        TEXT,147  created     INTEGER NOT NULL,148  replaced_at INTEGER NOT NULL,149  origin      TEXT NOT NULL DEFAULT 'replace' CHECK (origin IN ('replace', 'edit', 'restore'))150);151152CREATE INDEX IF NOT EXISTS idx_versions_node ON node_versions(node_id, replaced_at DESC);153154-- File requests: public links that let anyone upload INTO a chosen folder.155CREATE TABLE IF NOT EXISTS file_requests (156  id         INTEGER PRIMARY KEY AUTOINCREMENT,157  token      TEXT NOT NULL UNIQUE,158  folder_id  INTEGER NOT NULL REFERENCES nodes(id) ON DELETE CASCADE,159  label      TEXT,160  created    INTEGER NOT NULL,161  expires_at INTEGER,162  max_files  INTEGER,163  received   INTEGER NOT NULL DEFAULT 0,164  closed_at  INTEGER165);166167-- Full-text search over name, tags and extracted text content.168CREATE VIRTUAL TABLE IF NOT EXISTS fts USING fts5(169  name, tags, content,170  tokenize = 'unicode61 remove_diacritics 2'171);172173-- Map fts rowid <-> node id explicitly (fts rowid == node id).174CREATE TABLE IF NOT EXISTS fts_state (175  node_id     INTEGER PRIMARY KEY,176  content_sha TEXT177);178