krona-staff/migrations/0011_offline_sync_backend.up.sql

-- Offline synchronization backend foundation for Krona Staff local terminals and clients.
-- Protected by PostgreSQL Row Level Security (RLS) and strict tenant isolation.

CREATE TABLE staff_sync_devices (
  id VARCHAR(26) PRIMARY KEY CHECK (id ~ '^[0-9A-HJKMNP-TV-Z]{26}$'),
  tenant_id VARCHAR(26) NOT NULL CHECK (tenant_id ~ '^[0-9A-HJKMNP-TV-Z]{26}$'),
  device_code VARCHAR(64) NOT NULL,
  name VARCHAR(255) NOT NULL,
  token_hash VARCHAR(64) NOT NULL CHECK (token_hash ~ '^[0-9a-f]{64}$'),
  token_hint VARCHAR(16) NOT NULL,
  status VARCHAR(20) NOT NULL DEFAULT 'active' CHECK (status IN ('active', 'revoked', 'pending')),
  allowed_scopes TEXT[] NOT NULL DEFAULT ARRAY['attendance:sync'],
  org_unit_id VARCHAR(26),
  registered_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  last_seen_at TIMESTAMPTZ,
  revoked_at TIMESTAMPTZ,
  created_by VARCHAR(26) NOT NULL CHECK (created_by ~ '^[0-9A-HJKMNP-TV-Z]{26}$'),
  UNIQUE (tenant_id, id),
  UNIQUE (tenant_id, device_code),
  UNIQUE (tenant_id, token_hash),
  FOREIGN KEY (tenant_id, org_unit_id) REFERENCES staff_org_units (tenant_id, id) ON DELETE SET NULL (org_unit_id)
);

CREATE INDEX staff_sync_devices_token_hash_idx
  ON staff_sync_devices (tenant_id, token_hash);
CREATE INDEX staff_sync_devices_status_idx
  ON staff_sync_devices (tenant_id, status);

ALTER TABLE staff_sync_devices ENABLE ROW LEVEL SECURITY;
ALTER TABLE staff_sync_devices FORCE ROW LEVEL SECURITY;
CREATE POLICY staff_sync_devices_tenant_isolation
  ON staff_sync_devices
  USING (tenant_id = current_setting('app.tenant_id', true))
  WITH CHECK (tenant_id = current_setting('app.tenant_id', true));

CREATE TABLE staff_sync_change_log (
  id VARCHAR(26) PRIMARY KEY CHECK (id ~ '^[0-9A-HJKMNP-TV-Z]{26}$'),
  tenant_id VARCHAR(26) NOT NULL CHECK (tenant_id ~ '^[0-9A-HJKMNP-TV-Z]{26}$'),
  cursor_seq BIGSERIAL NOT NULL,
  entity_type VARCHAR(64) NOT NULL,
  entity_id VARCHAR(26) NOT NULL,
  operation VARCHAR(20) NOT NULL CHECK (operation IN ('upsert', 'delete')),
  revision INTEGER NOT NULL DEFAULT 1 CHECK (revision > 0),
  payload JSONB NOT NULL DEFAULT '{}'::jsonb,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  UNIQUE (tenant_id, id)
);

CREATE INDEX staff_sync_change_log_cursor_idx
  ON staff_sync_change_log (tenant_id, cursor_seq);
CREATE INDEX staff_sync_change_log_entity_idx
  ON staff_sync_change_log (tenant_id, entity_type, entity_id);

ALTER TABLE staff_sync_change_log ENABLE ROW LEVEL SECURITY;
ALTER TABLE staff_sync_change_log FORCE ROW LEVEL SECURITY;
CREATE POLICY staff_sync_change_log_tenant_isolation
  ON staff_sync_change_log
  USING (tenant_id = current_setting('app.tenant_id', true))
  WITH CHECK (tenant_id = current_setting('app.tenant_id', true));

CREATE TABLE staff_sync_command_inbox (
  id VARCHAR(26) PRIMARY KEY CHECK (id ~ '^[0-9A-HJKMNP-TV-Z]{26}$') ,
  tenant_id VARCHAR(26) NOT NULL CHECK (tenant_id ~ '^[0-9A-HJKMNP-TV-Z]{26}$'),
  device_id VARCHAR(26) NOT NULL,
  command_type VARCHAR(64) NOT NULL,
  schema_version INTEGER NOT NULL DEFAULT 1,
  idempotency_key VARCHAR(128) NOT NULL,
  correlation_id VARCHAR(36) NOT NULL,
  causation_id VARCHAR(36),
  payload JSONB NOT NULL DEFAULT '{}'::jsonb,
  status VARCHAR(20) NOT NULL CHECK (status IN ('applied', 'rejected', 'conflict')),
  result JSONB,
  conflict JSONB,
  error_code VARCHAR(64),
  error_message TEXT,
  created_at TIMESTAMPTZ NOT NULL,
  processed_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  UNIQUE (tenant_id, id),
  UNIQUE (tenant_id, device_id, idempotency_key),
  FOREIGN KEY (tenant_id, device_id) REFERENCES staff_sync_devices (tenant_id, id) ON DELETE CASCADE
);

CREATE INDEX staff_sync_command_inbox_device_idx
  ON staff_sync_command_inbox (tenant_id, device_id, processed_at DESC);

ALTER TABLE staff_sync_command_inbox ENABLE ROW LEVEL SECURITY;
ALTER TABLE staff_sync_command_inbox FORCE ROW LEVEL SECURITY;
CREATE POLICY staff_sync_command_inbox_tenant_isolation
  ON staff_sync_command_inbox
  USING (tenant_id = current_setting('app.tenant_id', true))
  WITH CHECK (tenant_id = current_setting('app.tenant_id', true));

CREATE TABLE staff_sync_device_checkpoints (
  device_id VARCHAR(26) NOT NULL,
  tenant_id VARCHAR(26) NOT NULL CHECK (tenant_id ~ '^[0-9A-HJKMNP-TV-Z]{26}$'),
  last_acked_cursor_seq BIGINT NOT NULL DEFAULT 0,
  last_acked_cursor TEXT,
  acknowledged_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  PRIMARY KEY (tenant_id, device_id),
  FOREIGN KEY (tenant_id, device_id) REFERENCES staff_sync_devices (tenant_id, id) ON DELETE CASCADE
);

ALTER TABLE staff_sync_device_checkpoints ENABLE ROW LEVEL SECURITY;
ALTER TABLE staff_sync_device_checkpoints FORCE ROW LEVEL SECURITY;
CREATE POLICY staff_sync_device_checkpoints_tenant_isolation
  ON staff_sync_device_checkpoints
  USING (tenant_id = current_setting('app.tenant_id', true))
  WITH CHECK (tenant_id = current_setting('app.tenant_id', true));

CREATE OR REPLACE FUNCTION staff_sync_cleanup_retention(p_days INTEGER DEFAULT 30)
RETURNS TABLE(purged_change_logs BIGINT, purged_commands BIGINT)
LANGUAGE plpgsql
AS $$
DECLARE
  v_change_logs BIGINT;
  v_commands BIGINT;
BEGIN
  WITH deleted_changes AS (
    DELETE FROM staff_sync_change_log
    WHERE created_at < now() - (p_days || ' days')::INTERVAL
    RETURNING 1
  )
  SELECT count(*) INTO v_change_logs FROM deleted_changes;

  WITH deleted_cmds AS (
    DELETE FROM staff_sync_command_inbox
    WHERE processed_at < now() - (p_days || ' days')::INTERVAL
    RETURNING 1
  )
  SELECT count(*) INTO v_commands FROM deleted_cmds;

  RETURN QUERY SELECT v_change_logs, v_commands;
END;
$$;

GRANT SELECT, INSERT, UPDATE, DELETE ON staff_sync_devices TO krona_staff_app;
GRANT SELECT, INSERT, UPDATE, DELETE ON staff_sync_change_log TO krona_staff_app;
GRANT USAGE, SELECT ON SEQUENCE staff_sync_change_log_cursor_seq_seq TO krona_staff_app;
GRANT SELECT, INSERT, UPDATE, DELETE ON staff_sync_command_inbox TO krona_staff_app;
GRANT SELECT, INSERT, UPDATE, DELETE ON staff_sync_device_checkpoints TO krona_staff_app;
GRANT EXECUTE ON FUNCTION staff_sync_cleanup_retention(INTEGER) TO krona_staff_app;