apps/backend/src/infrastructure/database/migrations/0030_sprint_o_offline_realtime.sqlSQL
-- Sprint O — offline sync, conflicts, replay protection and realtime delivery.
create table if not exists sync_clients (
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,
device_id text not null,
platform text,
app_version text,
last_seen_at timestamptz not null default now(),
last_successful_sync_at timestamptz,
created_at timestamptz not null default now(),
constraint sync_clients_device_unique unique(organization_id,location_id,device_id)
);
create table if not exists sync_commands (
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_client_id uuid not null references sync_clients(id) on delete cascade,
client_command_id text not null,
command_type text not null,
aggregate_type text,
aggregate_id uuid,
base_version bigint,
payload jsonb not null,
status text not null default 'received',
result jsonb,
error_code text,
error_details jsonb,
received_at timestamptz not null default now(),
processed_at timestamptz,
constraint sync_command_status_check check(
status in ('received','applied','conflict','rejected','retryable')
),
constraint sync_command_client_id_unique unique(sync_client_id,client_command_id)
);
create index if not exists idx_sync_commands_client_status
on sync_commands(sync_client_id,status,received_at);
create table if not exists sync_conflicts (
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,
conflict_type text not null,
aggregate_type text,
aggregate_id uuid,
client_base_version bigint,
server_version bigint,
server_snapshot jsonb,
client_payload jsonb not null,
resolution text,
resolved_at timestamptz,
created_at timestamptz not null default now(),
constraint sync_conflict_type_check check(
conflict_type in ('version_mismatch','deleted','authorization_changed','business_rule','duplicate_semantic')
)
);
create index if not exists idx_sync_conflicts_location_unresolved
on sync_conflicts(location_id,resolved_at,created_at);
create table if not exists realtime_channels (
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,
channel_key text not null,
sequence bigint not null default 0,
updated_at timestamptz not null default now(),
constraint realtime_channel_unique unique(location_id,channel_key)
);
create table if not exists realtime_events (
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,
channel_key text not null,
sequence bigint not null,
event_type text not null,
aggregate_type text,
aggregate_id uuid,
payload jsonb not null,
occurred_at timestamptz not null default now(),
expires_at timestamptz,
constraint realtime_event_sequence_unique unique(location_id,channel_key,sequence)
);
create index if not exists idx_realtime_events_channel_sequence
on realtime_events(location_id,channel_key,sequence);
create table if not exists realtime_client_cursors (
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_client_id uuid not null references sync_clients(id) on delete cascade,
channel_key text not null,
last_sequence bigint not null default 0,
updated_at timestamptz not null default now(),
constraint realtime_cursor_unique unique(sync_client_id,channel_key)
);
create table if not exists sync_health_snapshots (
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_client_id uuid references sync_clients(id) on delete set null,
pending_commands integer not null default 0,
conflict_count integer not null default 0,
oldest_pending_seconds integer,
realtime_connected boolean,
last_server_sequence bigint,
captured_at timestamptz not null default now()
);
insert into permissions(permission_key,description) values
('sync.read','Read sync health'),
('sync.resolve','Resolve sync conflicts'),
('realtime.read','Subscribe to realtime channels')
on conflict(permission_key) do nothing;