Repository Public
1export const id = '001_init';23export const up = `4create extension if not exists citext;5create extension if not exists pg_trgm;6create extension if not exists unaccent;78create table users (9 id uuid primary key default gen_random_uuid(),10 email citext not null unique,11 display_name text not null,12 role text not null check (role in ('admin', 'user')),13 locale text not null default 'fr',14 webauthn_handle bytea not null unique,15 totp_secret bytea,16 totp_enabled_at timestamptz,17 totp_last_step bigint,18 disabled_at timestamptz,19 created_at timestamptz not null default now());2021create table quotas (22 user_id uuid primary key references users on delete cascade,23 limit_bytes bigint not null check (limit_bytes >= 0),24 updated_at timestamptz not null default now());2526create table passkeys (27 id uuid primary key default gen_random_uuid(),28 user_id uuid not null references users on delete cascade,29 credential_id text not null unique,30 public_key text not null,31 counter bigint not null default 0,32 transports jsonb not null default '[]',33 device_type text not null default '',34 backed_up boolean not null default false,35 label text not null default '',36 created_at timestamptz not null default now(),37 last_used_at timestamptz);38create index passkeys_user on passkeys (user_id);3940create table sessions (41 id uuid primary key default gen_random_uuid(),42 user_id uuid not null references users on delete cascade,43 token_hash text not null unique,44 prev_token_hash text,45 prev_valid_until timestamptz,46 rotated_at timestamptz not null default now(),47 method text not null check (method in ('passkey', 'recovery')),48 created_at timestamptz not null default now(),49 last_seen_at timestamptz not null default now(),50 expires_at timestamptz not null,51 reauth_at timestamptz,52 ip text not null default '',53 user_agent text not null default '',54 revoked_at timestamptz);55create index sessions_user on sessions (user_id);56create index sessions_prev on sessions (prev_token_hash) where prev_token_hash is not null;5758create table auth_challenges (59 id uuid primary key,60 kind text not null,61 challenge text not null,62 user_id uuid references users on delete cascade,63 data jsonb not null default '{}',64 expires_at timestamptz not null);6566create table recovery_codes (67 id uuid primary key default gen_random_uuid(),68 user_id uuid not null references users on delete cascade,69 code_hash text not null unique,70 used_at timestamptz,71 created_at timestamptz not null default now());72create index recovery_codes_user on recovery_codes (user_id);7374create table invites (75 id uuid primary key default gen_random_uuid(),76 token_hash text not null unique,77 email citext not null,78 role text not null check (role in ('admin', 'user')),79 quota_bytes bigint not null,80 created_by uuid references users on delete set null,81 expires_at timestamptz not null,82 used_at timestamptz,83 created_at timestamptz not null default now());8485-- The tree. Every user has exactly one root folder (parent_id null). name_key = NFC lower-case name.86create table folders (87 id uuid primary key default gen_random_uuid(),88 owner_id uuid not null references users on delete restrict,89 parent_id uuid references folders on delete cascade,90 name text not null,91 name_key text not null,92 starred_at timestamptz,93 trashed_at timestamptz,94 trash_root uuid,95 created_at timestamptz not null default now(),96 updated_at timestamptz not null default now(),97 check ((trashed_at is null) = (trash_root is null)));98create unique index folders_one_root on folders (owner_id) where parent_id is null;99create unique index folders_live_name on folders (parent_id, name_key) where trashed_at is null and parent_id is not null;100create index folders_parent on folders (parent_id);101create index folders_owner_trash on folders (owner_id, trash_root) where trashed_at is not null;102create index folders_name_trgm on folders using gin (name_key gin_trgm_ops);103104create table storage_objects (105 id uuid primary key default gen_random_uuid(),106 owner_id uuid not null references users on delete restrict,107 sha256 text not null,108 size bigint not null check (size >= 0),109 storage_key text not null unique,110 wrapped_key bytea not null,111 scan_status text not null default 'pending' check (scan_status in ('pending', 'clean', 'infected', 'error')),112 scan_detail text,113 scanned_at timestamptz,114 created_at timestamptz not null default now(),115 unique (owner_id, sha256, size));116117create table files (118 id uuid primary key default gen_random_uuid(),119 owner_id uuid not null references users on delete restrict,120 folder_id uuid not null references folders on delete cascade,121 name text not null,122 name_key text not null,123 current_version_id uuid,124 size bigint not null default 0,125 sha256 text not null default '',126 origin text not null default 'upload' check (origin in ('upload', 'request', 'copy')),127 starred_at timestamptz,128 trashed_at timestamptz,129 trash_root uuid,130 created_at timestamptz not null default now(),131 updated_at timestamptz not null default now(),132 check ((trashed_at is null) = (trash_root is null)));133create unique index files_live_name on files (folder_id, name_key) where trashed_at is null;134create index files_owner_updated on files (owner_id, updated_at desc) where trashed_at is null;135create index files_owner_sha on files (owner_id, sha256) where trashed_at is null;136create index files_owner_trash on files (owner_id, trash_root) where trashed_at is not null;137create index files_name_trgm on files using gin (name_key gin_trgm_ops);138139create table file_versions (140 id uuid primary key default gen_random_uuid(),141 file_id uuid not null references files on delete cascade,142 seq int not null,143 object_id uuid not null references storage_objects on delete restrict,144 size bigint not null,145 sha256 text not null,146 created_by uuid references users on delete set null,147 note text not null default '',148 created_at timestamptz not null default now(),149 unique (file_id, seq));150create index file_versions_object on file_versions (object_id);151alter table files add constraint files_current_version foreign key (current_version_id) references file_versions deferrable initially deferred;152153-- Derived previews of an object (thumbnail, PDF rendition, JSON inventory…), stored encrypted like objects.154create table previews (155 object_id uuid not null references storage_objects on delete cascade,156 kind text not null check (kind in ('thumb', 'image', 'pdf', 'sheet', 'archive')),157 status text not null check (status in ('pending', 'ready', 'failed', 'unsupported')),158 storage_key text,159 wrapped_key bytea,160 size bigint,161 error text,162 updated_at timestamptz not null default now(),163 primary key (object_id, kind));164165create table tags (166 id uuid primary key default gen_random_uuid(),167 owner_id uuid not null references users on delete cascade,168 name text not null,169 name_key text not null,170 color text not null default 'slate',171 created_at timestamptz not null default now(),172 unique (owner_id, name_key));173create table file_tags (174 file_id uuid not null references files on delete cascade,175 tag_id uuid not null references tags on delete cascade,176 primary key (file_id, tag_id));177create index file_tags_tag on file_tags (tag_id);178179create table shares (180 id uuid primary key default gen_random_uuid(),181 owner_id uuid not null references users on delete cascade,182 file_id uuid references files on delete cascade,183 folder_id uuid references folders on delete cascade,184 token_hash text not null unique,185 label text not null default '',186 can_download boolean not null default true,187 password_hash text,188 expires_at timestamptz,189 max_views int check (max_views > 0),190 view_count int not null default 0,191 max_downloads int check (max_downloads > 0),192 download_count int not null default 0,193 single_use boolean not null default false,194 revoked_at timestamptz,195 created_at timestamptz not null default now(),196 last_access_at timestamptz,197 check ((file_id is null) <> (folder_id is null)));198create index shares_owner on shares (owner_id);199200create table share_access_events (201 id bigserial primary key,202 share_id uuid not null references shares on delete cascade,203 at timestamptz not null default now(),204 kind text not null,205 ok boolean not null,206 ip text not null default '',207 user_agent text not null default '',208 file_id uuid,209 detail text not null default '');210create index share_access_events_share on share_access_events (share_id, at desc);211212-- Deposit portals ("file requests").213create table upload_requests (214 id uuid primary key default gen_random_uuid(),215 owner_id uuid not null references users on delete cascade,216 folder_id uuid not null references folders on delete cascade,217 token_hash text not null unique,218 title text not null,219 description text not null default '',220 instructions text not null default '',221 confirmation_message text not null default '',222 brand_name text not null default '',223 brand_color text not null default '#2563eb',224 password_hash text,225 expires_at timestamptz,226 active boolean not null default true,227 revoked_at timestamptz,228 max_file_bytes bigint not null check (max_file_bytes > 0),229 max_total_bytes bigint not null check (max_total_bytes > 0),230 max_files_per_submission int not null check (max_files_per_submission > 0),231 max_submissions int check (max_submissions > 0),232 submissions_per_hour int not null default 20 check (submissions_per_hour > 0),233 allowed_extensions text[] not null default '{}',234 fields jsonb not null,235 require_consent boolean not null default false,236 consent_text text not null default '',237 retention_note text not null default '',238 subfolder_per_submission boolean not null default true,239 notify_owner boolean not null default true,240 bytes_received bigint not null default 0,241 created_at timestamptz not null default now(),242 updated_at timestamptz not null default now());243create index upload_requests_owner on upload_requests (owner_id);244245create table upload_submissions (246 id uuid primary key default gen_random_uuid(),247 request_id uuid not null references upload_requests on delete cascade,248 public_ref text not null unique,249 token_hash text not null unique,250 status text not null check (status in ('open', 'processing', 'completed', 'rejected', 'expired')),251 submitter_name text not null default '',252 submitter_email text not null default '',253 reference text not null default '',254 meta jsonb not null default '{}',255 consent_at timestamptz,256 declared_bytes bigint not null,257 ip text not null default '',258 user_agent text not null default '',259 folder_id uuid references folders on delete set null,260 created_at timestamptz not null default now(),261 finalized_at timestamptz,262 completed_at timestamptz);263create index upload_submissions_request on upload_submissions (request_id, created_at desc);264265create table upload_items (266 id uuid primary key default gen_random_uuid(),267 submission_id uuid not null references upload_submissions on delete cascade,268 position int not null default 0,269 original_name text not null,270 safe_name text not null,271 declared_size bigint not null,272 size bigint,273 sha256 text,274 detected_mime text,275 status text not null check (status in ('uploading', 'quarantined', 'placed', 'rejected')),276 reject_code text,277 quarantine_path text,278 file_id uuid references files on delete set null,279 created_at timestamptz not null default now(),280 updated_at timestamptz not null default now());281create index upload_items_submission on upload_items (submission_id);282283-- Resumable uploads (signed-in users and portal items).284create table uploads (285 id uuid primary key default gen_random_uuid(),286 owner_id uuid not null references users on delete cascade,287 actor_user_id uuid references users on delete cascade,288 item_id uuid unique references upload_items on delete cascade,289 folder_id uuid references folders on delete cascade,290 name text not null,291 size bigint not null check (size >= 0),292 received bigint not null default 0,293 on_conflict text not null default 'rename' check (on_conflict in ('fail', 'rename', 'version')),294 status text not null default 'active' check (status in ('active', 'completing', 'done', 'aborted')),295 file_id uuid,296 created_at timestamptz not null default now(),297 updated_at timestamptz not null default now(),298 expires_at timestamptz not null,299 check ((actor_user_id is null) <> (item_id is null)));300create index uploads_owner_active on uploads (owner_id) where status in ('active', 'completing');301302create table audit_events (303 id bigserial primary key,304 at timestamptz not null default now(),305 actor_kind text not null check (actor_kind in ('user', 'anonymous', 'system')),306 actor_user_id uuid,307 owner_id uuid,308 action text not null,309 target_type text not null default '',310 target_id text not null default '',311 ip text not null default '',312 user_agent text not null default '',313 meta jsonb not null default '{}');314create index audit_events_owner on audit_events (owner_id, at desc);315create index audit_events_target on audit_events (target_type, target_id, at);316317create table jobs (318 id bigserial primary key,319 kind text not null,320 payload jsonb not null default '{}',321 dedupe_key text,322 status text not null default 'queued' check (status in ('queued', 'running', 'done', 'failed')),323 attempts int not null default 0,324 max_attempts int not null default 3,325 run_at timestamptz not null default now(),326 locked_at timestamptz,327 last_error text,328 created_at timestamptz not null default now(),329 finished_at timestamptz);330create index jobs_ready on jobs (run_at) where status = 'queued';331create unique index jobs_dedupe on jobs (dedupe_key) where status in ('queued', 'running') and dedupe_key is not null;332333create table notifications (334 id uuid primary key default gen_random_uuid(),335 user_id uuid not null references users on delete cascade,336 kind text not null,337 data jsonb not null default '{}',338 read_at timestamptz,339 created_at timestamptz not null default now());340create index notifications_user on notifications (user_id, created_at desc);341`;342343export const down = `344drop table if exists notifications, jobs, audit_events, uploads, upload_items, upload_submissions, upload_requests,345 share_access_events, shares, file_tags, tags, previews, file_versions, files, storage_objects, folders, invites,346 recovery_codes, auth_challenges, sessions, passkeys, quotas, users cascade;347`;348