apps/backend/src/infrastructure/database/migrations/0032_sprint_p_files_import_export_notifications.sqlSQL
-- Sprint P — secure files, imports/exports and notifications.
create table if not exists file_assets (
id uuid primary key default gen_random_uuid(),
organization_id uuid not null references organizations(id) on delete cascade,
location_id uuid references locations(id) on delete set null,
owner_entity_type text,
owner_entity_id uuid,
purpose text not null,
storage_key text not null,
original_file_name text not null,
mime_type text not null,
size_bytes bigint not null,
checksum_sha256 text,
status text not null default 'uploading',
created_by_user_id uuid references user_accounts(id) on delete set null,
rejection_reason text,
created_at timestamptz not null default now(),
updated_at timestamptz not null default now(),
archived_at timestamptz,
deleted_at timestamptz,
constraint file_asset_status_check check(
status in ('uploading','processing','available','rejected','archived','deleted')
),
constraint file_asset_purpose_check check(
purpose in (
'recipe_media','training_media','task_evidence','incident_evidence',
'safety_evidence','employee_document','supplier_document','invoice_document',
'import_source','export_result','other'
)
),
constraint file_asset_size_positive check(size_bytes >= 0)
);
create unique index if not exists idx_file_assets_storage_key
on file_assets(storage_key);
create index if not exists idx_file_assets_owner
on file_assets(organization_id,owner_entity_type,owner_entity_id);
create table if not exists file_upload_sessions (
id uuid primary key default gen_random_uuid(),
organization_id uuid not null references organizations(id) on delete cascade,
location_id uuid references locations(id) on delete set null,
file_asset_id uuid not null references file_assets(id) on delete cascade,
expected_size_bytes bigint not null,
expected_mime_type text not null,
expected_checksum_sha256 text,
expires_at timestamptz not null,
completed_at timestamptz,
created_at timestamptz not null default now()
);
create index if not exists idx_file_upload_sessions_expiry
on file_upload_sessions(expires_at);
create table if not exists import_jobs (
id uuid primary key default gen_random_uuid(),
organization_id uuid not null references organizations(id) on delete cascade,
location_id uuid references locations(id) on delete set null,
import_type text not null,
file_asset_id uuid not null references file_assets(id) on delete restrict,
status text not null default 'uploaded',
mapping_json jsonb not null default '{}'::jsonb,
options_json jsonb not null default '{}'::jsonb,
created_by_user_id uuid references user_accounts(id) on delete set null,
created_at timestamptz not null default now(),
started_at timestamptz,
completed_at timestamptz,
summary_json jsonb,
version bigint not null default 1,
constraint import_status_check check(
status in ('uploaded','validating','preview_ready','importing','completed','failed','cancelled')
),
constraint import_type_check check(
import_type in (
'products','ingredients','recipes_basic','employees','guests','suppliers',
'supplier_products','inventory_opening_balance'
)
)
);
create index if not exists idx_import_jobs_org_status
on import_jobs(organization_id,status,created_at desc);
create table if not exists import_row_errors (
id uuid primary key default gen_random_uuid(),
import_job_id uuid not null references import_jobs(id) on delete cascade,
row_number integer not null,
field_name text,
error_code text not null,
message text not null,
raw_value_preview text,
severity text not null default 'error',
created_at timestamptz not null default now(),
constraint import_error_severity_check check(severity in ('warning','error'))
);
create index if not exists idx_import_row_errors_job_row
on import_row_errors(import_job_id,row_number);
create table if not exists import_preview_rows (
id uuid primary key default gen_random_uuid(),
import_job_id uuid not null references import_jobs(id) on delete cascade,
row_number integer not null,
normalized_json jsonb not null,
valid boolean not null,
deterministic_row_key text not null,
created_at timestamptz not null default now(),
constraint import_preview_row_unique unique(import_job_id,row_number),
constraint import_preview_key_unique unique(import_job_id,deterministic_row_key)
);
create table if not exists import_applied_rows (
id uuid primary key default gen_random_uuid(),
import_job_id uuid not null references import_jobs(id) on delete cascade,
deterministic_row_key text not null,
target_entity_type text not null,
target_entity_id uuid,
applied_at timestamptz not null default now(),
constraint import_applied_row_unique unique(import_job_id,deterministic_row_key)
);
create table if not exists export_jobs (
id uuid primary key default gen_random_uuid(),
organization_id uuid not null references organizations(id) on delete cascade,
location_id uuid references locations(id) on delete set null,
export_type text not null,
format text not null,
filters_json jsonb not null default '{}'::jsonb,
status text not null default 'queued',
requested_by_user_id uuid references user_accounts(id) on delete set null,
file_asset_id uuid references file_assets(id) on delete set null,
created_at timestamptz not null default now(),
started_at timestamptz,
completed_at timestamptz,
expires_at timestamptz,
error text,
constraint export_status_check check(
status in ('queued','processing','completed','failed','expired','cancelled')
),
constraint export_format_check check(format in ('csv','xlsx','pdf')),
constraint export_type_check check(
export_type in ('sales_report','inventory','waste','purchasing','employees','guests','safety','incidents','audit')
)
);
create index if not exists idx_export_jobs_org_status
on export_jobs(organization_id,status,created_at desc);
create table if not exists notifications (
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,
location_id uuid references locations(id) on delete set null,
notification_type text not null,
title_key text not null,
body_key text not null,
params_json jsonb not null default '{}'::jsonb,
severity text not null default 'info',
related_entity_type text,
related_entity_id uuid,
status text not null default 'unread',
deduplication_key text,
created_at timestamptz not null default now(),
read_at timestamptz,
archived_at timestamptz,
constraint notification_status_check check(status in ('unread','read','archived')),
constraint notification_severity_check check(severity in ('info','attention','urgent','critical'))
);
create unique index if not exists idx_notifications_dedup
on notifications(organization_id,user_id,deduplication_key)
where deduplication_key is not null;
create index if not exists idx_notifications_user_status
on notifications(user_id,status,created_at desc);
create table if not exists notification_preferences (
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,
notification_type text not null,
in_app_enabled boolean not null default true,
email_enabled boolean not null default false,
push_enabled boolean not null default false,
quiet_hours_policy jsonb,
updated_at timestamptz not null default now(),
constraint notification_preference_unique unique(organization_id,user_id,notification_type)
);
create table if not exists notification_deliveries (
id uuid primary key default gen_random_uuid(),
notification_id uuid not null references notifications(id) on delete cascade,
channel text not null,
status text not null default 'queued',
provider text,
provider_message_id text,
attempt_count integer not null default 0,
next_attempt_at timestamptz,
last_attempt_at timestamptz,
last_error text,
created_at timestamptz not null default now(),
constraint notification_delivery_channel_check check(channel in ('in_app','email','push')),
constraint notification_delivery_status_check check(
status in ('queued','sending','sent','failed','dead_letter')
),
constraint notification_delivery_unique unique(notification_id,channel)
);
create index if not exists idx_notification_deliveries_queue
on notification_deliveries(status,next_attempt_at,created_at);
insert into permissions(permission_key,description) values
('files.read','Read authorized files'),
('files.upload','Upload files'),
('files.manage','Archive/manage files'),
('imports.read','Read import jobs'),
('imports.create','Create import jobs'),
('imports.execute','Confirm/execute import jobs'),
('exports.create','Create export jobs'),
('exports.read','Read/download export jobs'),
('exports.sensitive','Create sensitive exports')
on conflict(permission_key) do nothing;