apps/backend/src/infrastructure/database/migrations/0031_sprint_o_completion.sqlSQL
-- Sprint O completion — websocket session audit, snapshot cursors and safety limits.
alter table sync_clients
add column if not exists disabled_at timestamptz;
create table if not exists realtime_sessions (
id uuid primary key default gen_random_uuid(),
organization_id uuid not null references organizations(id) on delete cascade,
location_id uuid not null references locations(id) on delete cascade,
user_id uuid references user_accounts(id) on delete set null,
sync_client_id uuid references sync_clients(id) on delete set null,
socket_id text not null,
connected_at timestamptz not null default now(),
disconnected_at timestamptz,
remote_address text,
user_agent text,
close_reason text
);
create index if not exists idx_realtime_sessions_location_connected
on realtime_sessions(location_id,connected_at desc);
create table if not exists realtime_snapshot_cursors (
organization_id uuid not null references organizations(id) on delete cascade,
location_id uuid not null references locations(id) on delete cascade,
channel_key text not null,
snapshot_version bigint not null default 1,
updated_at timestamptz not null default now(),
primary key(location_id,channel_key)
);
create table if not exists sync_dead_letters (
id uuid primary key default gen_random_uuid(),
organization_id uuid not null references organizations(id) on delete cascade,
location_id uuid not null references locations(id) on delete cascade,
sync_command_id uuid not null references sync_commands(id) on delete cascade,
reason text not null,
payload jsonb not null,
created_at timestamptz not null default now(),
resolved_at timestamptz
);
create index if not exists idx_sync_dead_letters_location_open
on sync_dead_letters(location_id,resolved_at,created_at);
insert into permissions(permission_key,description) values
('sync.diagnostics.read','Read device-level sync diagnostics'),
('realtime.kds.read','Subscribe to KDS realtime data'),
('realtime.pos.read','Subscribe to POS realtime data'),
('realtime.reservations.read','Subscribe to reservation realtime data'),
('realtime.operations.read','Subscribe to operations realtime data')
on conflict(permission_key) do nothing;