apps/backend/src/infrastructure/database/migrations/0033_sprint_p_completion.sqlSQL
-- Sprint P completion — file scanning, authorization, import execution and notification throttling.
alter table file_assets
add column if not exists scan_status text not null default 'pending',
add column if not exists detected_mime_type text,
add column if not exists verified_checksum_sha256 text,
add column if not exists scan_completed_at timestamptz,
add column if not exists retention_until timestamptz,
add constraint file_asset_scan_status_check
check(scan_status in ('pending','clean','infected','failed','not_required'));
create table if not exists file_access_audit (
id uuid primary key default gen_random_uuid(),
organization_id uuid not null references organizations(id) on delete cascade,
file_asset_id uuid not null references file_assets(id) on delete cascade,
user_id uuid references user_accounts(id) on delete set null,
access_type text not null,
allowed boolean not null,
reason text,
occurred_at timestamptz not null default now(),
constraint file_access_type_check check(access_type in ('metadata','download','archive'))
);
create index if not exists idx_file_access_audit_asset_time
on file_access_audit(file_asset_id,occurred_at desc);
create table if not exists notification_throttle_state (
organization_id uuid not null references organizations(id) on delete cascade,
user_id uuid not null references user_accounts(id) on delete cascade,
notification_type text not null,
throttle_key text not null,
last_created_at timestamptz not null,
suppressed_count integer not null default 0,
primary key(organization_id,user_id,notification_type,throttle_key)
);
create table if not exists notification_devices (
id uuid primary key default gen_random_uuid(),
organization_id uuid not null references organizations(id) on delete cascade,
user_id uuid not null references user_accounts(id) on delete cascade,
platform text not null,
device_token text not null,
enabled boolean not null default true,
last_seen_at timestamptz not null default now(),
created_at timestamptz not null default now(),
constraint notification_device_unique unique(organization_id,user_id,device_token)
);
create index if not exists idx_notification_devices_user_enabled
on notification_devices(user_id,enabled);