create table case_file (
  id uuid primary key,
  status text not null,
  applicant_name text,
  applicant_email text,
  reviewer_id uuid,
  supervisor_id uuid,
  document_1_url text,
  document_2_url text,
  document_3_url text,
  decision text,
  decision_reason text,
  sla_due_at timestamptz,
  escalation_status text,
  comment_1 text,
  comment_2 text,
  comment_3 text,
  created_at timestamptz not null
);

create table case_file (
  id uuid primary key,
  case_number text not null unique,
  case_type text not null,
  lifecycle_state text not null,
  priority text not null,
  severity text,
  opened_at timestamptz not null,
  closed_at timestamptz,
  current_owner_team_id uuid,
  state_version bigint not null default 0,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),
  constraint case_file_lifecycle_chk check (
    lifecycle_state in (
      'DRAFT',
      'SUBMITTED',
      'TRIAGE',
      'UNDER_REVIEW',
      'WAITING',
      'DECIDED',
      'CLOSED',
      'CANCELLED'
    )
  ),
  constraint case_file_priority_chk check (
    priority in ('LOW', 'NORMAL', 'HIGH', 'CRITICAL')
  )
);

create table case_type (
  code text primary key,
  label text not null,
  description text,
  is_active boolean not null default true
);

create table workflow_definition (
  id uuid primary key,
  workflow_code text not null,
  case_type text not null references case_type(code),
  version int not null,
  status text not null check (status in ('DRAFT', 'ACTIVE', 'RETIRED')),
  effective_from timestamptz not null,
  effective_to timestamptz,
  constraint workflow_definition_uk unique (workflow_code, version)
);

alter table case_file
add column workflow_definition_id uuid not null references workflow_definition(id);

applicant_id uuid,
respondent_id uuid,
representative_id uuid,
witness_id uuid

create table party (
  id uuid primary key,
  party_type text not null check (party_type in ('PERSON', 'ORGANIZATION')),
  display_name text not null,
  external_ref text,
  created_at timestamptz not null default now()
);

create table case_party_role (
  id uuid primary key,
  case_id uuid not null references case_file(id),
  party_id uuid not null references party(id),
  role_code text not null,
  effective_from timestamptz not null,
  effective_to timestamptz,
  created_at timestamptz not null default now(),
  constraint case_party_role_period_chk check (
    effective_to is null or effective_to > effective_from
  )
);

APPLICANT
COMPLAINANT
RESPONDENT
SUBJECT
REPRESENTATIVE
WITNESS
INSPECTOR
REVIEWER
DECISION_MAKER

create table case_subject (
  id uuid primary key,
  case_id uuid not null references case_file(id),
  subject_type text not null,
  subject_ref text not null,
  relationship_code text not null,
  is_primary boolean not null default false,
  created_at timestamptz not null default now(),
  constraint case_subject_uk unique (case_id, subject_type, subject_ref, relationship_code)
);

subject_type = LICENSE, subject_ref = LIC-123, relationship = TARGET_LICENSE
subject_type = FACILITY, subject_ref = FAC-456, relationship = INSPECTED_FACILITY
subject_type = TRANSACTION, subject_ref = TX-789, relationship = SUSPICIOUS_TRANSACTION

create table case_task (
  id uuid primary key,
  case_id uuid not null references case_file(id),
  task_type text not null,
  task_state text not null,
  title text not null,
  description text,
  priority text not null,
  due_at timestamptz,
  created_by uuid,
  created_at timestamptz not null default now(),
  started_at timestamptz,
  completed_at timestamptz,
  cancelled_at timestamptz,
  state_version bigint not null default 0,
  constraint case_task_state_chk check (
    task_state in ('OPEN', 'CLAIMED', 'IN_PROGRESS', 'BLOCKED', 'DONE', 'CANCELLED')
  ),
  constraint case_task_priority_chk check (
    priority in ('LOW', 'NORMAL', 'HIGH', 'CRITICAL')
  )
);

create table task_definition (
  id uuid primary key,
  workflow_definition_id uuid not null references workflow_definition(id),
  task_type text not null,
  label text not null,
  default_priority text not null,
  default_due_duration interval,
  is_required boolean not null default true,
  sort_order int not null,
  constraint task_definition_uk unique (workflow_definition_id, task_type)
);

alter table case_task
add column task_definition_id uuid references task_definition(id);

alter table case_task
add column task_definition_version int,
add column task_label_snapshot text;

alter table case_task add column assignee_id uuid;

create table task_assignment (
  id uuid primary key,
  task_id uuid not null references case_task(id),
  assignee_type text not null check (assignee_type in ('USER', 'TEAM', 'ROLE')),
  assignee_id uuid not null,
  assignment_role text not null,
  assigned_by uuid,
  assigned_at timestamptz not null default now(),
  released_at timestamptz,
  release_reason text,
  is_primary boolean not null default true,
  constraint task_assignment_period_chk check (
    released_at is null or released_at > assigned_at
  )
);

select *
from task_assignment
where task_id = :task_id
  and released_at is null;

create index task_assignment_current_user_idx
on task_assignment (assignee_id, assigned_at desc)
where released_at is null and assignee_type = 'USER';

create index task_assignment_current_task_idx
on task_assignment (task_id)
where released_at is null;

alter table case_task
add column current_assignee_type text,
add column current_assignee_id uuid;

Team/person accountable for overall case progress.

Person/team responsible for a specific work item.

create table case_assignment (
  id uuid primary key,
  case_id uuid not null references case_file(id),
  assignee_type text not null check (assignee_type in ('USER', 'TEAM', 'ROLE')),
  assignee_id uuid not null,
  responsibility_code text not null,
  assigned_by uuid,
  assigned_at timestamptz not null default now(),
  released_at timestamptz,
  release_reason text
);

CASE_OWNER
SUPERVISOR
PRIMARY_REVIEWER
SECONDARY_REVIEWER
LEGAL_ADVISOR

select *
from case_task t
join case_file c on c.id = t.case_id
left join task_assignment a on a.task_id = t.id
where t.task_state in ('OPEN', 'CLAIMED', 'IN_PROGRESS')
  and (... 30 dynamic conditions ...)
order by c.priority, t.due_at;

create table work_queue_item (
  id uuid primary key,
  task_id uuid not null unique references case_task(id),
  case_id uuid not null references case_file(id),
  queue_code text not null,
  visible_to_type text not null check (visible_to_type in ('USER', 'TEAM', 'ROLE')),
  visible_to_id uuid not null,
  priority_rank int not null,
  due_at timestamptz,
  case_type text not null,
  case_priority text not null,
  task_state text not null,
  assignment_state text not null,
  sla_state text,
  escalation_level int not null default 0,
  available_at timestamptz not null default now(),
  removed_at timestamptz,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create index work_queue_visible_idx
on work_queue_item (
  visible_to_type,
  visible_to_id,
  queue_code,
  priority_rank,
  due_at
)
where removed_at is null;

Queue item must reflect task/case/assignment eligibility within declared freshness contract.

select id from case_task
where task_state = 'OPEN'
limit 1;

update case_task
set task_state = 'CLAIMED', assignee_id = :user_id
where id = :task_id;

update case_task
set task_state = 'CLAIMED',
    state_version = state_version + 1,
    started_at = coalesce(started_at, now())
where id = :task_id
  and task_state = 'OPEN';

insert into task_assignment (
  id,
  task_id,
  assignee_type,
  assignee_id,
  assignment_role,
  assigned_by,
  assigned_at
)
values (
  :assignment_id,
  :task_id,
  'USER',
  :user_id,
  'OWNER',
  :user_id,
  now()
);

with candidate as (
  select t.id
  from case_task t
  where t.task_state = 'OPEN'
  order by t.due_at nulls last, t.created_at
  limit 1
  for update skip locked
)
update case_task t
set task_state = 'CLAIMED',
    state_version = state_version + 1
from candidate c
where t.id = c.id
returning t.*;

create table case_sla_timer (
  id uuid primary key,
  case_id uuid not null references case_file(id),
  task_id uuid references case_task(id),
  timer_type text not null,
  timer_state text not null,
  started_at timestamptz not null,
  due_at timestamptz not null,
  paused_at timestamptz,
  total_paused_seconds bigint not null default 0,
  breached_at timestamptz,
  completed_at timestamptz,
  policy_version text not null,
  constraint case_sla_timer_state_chk check (
    timer_state in ('RUNNING', 'PAUSED', 'BREACHED', 'COMPLETED', 'CANCELLED')
  )
);

create table case_sla_event (
  id uuid primary key,
  sla_timer_id uuid not null references case_sla_timer(id),
  event_type text not null,
  from_state text,
  to_state text,
  occurred_at timestamptz not null,
  actor_id uuid,
  reason_code text,
  metadata jsonb not null default '{}'::jsonb
);

due_at = current operational deadline
policy_version = rule used to compute it
sla_event = explanation of changes

Respond within 5 business days excluding holidays in jurisdiction X.

create table business_calendar (
  code text primary key,
  timezone text not null,
  description text
);

create table business_calendar_holiday (
  id uuid primary key,
  calendar_code text not null references business_calendar(code),
  holiday_date date not null,
  label text not null,
  constraint business_calendar_holiday_uk unique (calendar_code, holiday_date)
);

create table sla_policy (
  id uuid primary key,
  policy_code text not null,
  version int not null,
  calendar_code text not null references business_calendar(code),
  duration_seconds bigint not null,
  duration_type text not null check (duration_type in ('CALENDAR_TIME', 'BUSINESS_TIME')),
  effective_from timestamptz not null,
  effective_to timestamptz,
  constraint sla_policy_uk unique (policy_code, version)
);

create table case_escalation (
  id uuid primary key,
  case_id uuid not null references case_file(id),
  escalation_level int not null,
  escalation_state text not null,
  reason_code text not null,
  reason_text text,
  triggered_by text not null,
  triggered_at timestamptz not null,
  acknowledged_by uuid,
  acknowledged_at timestamptz,
  resolved_by uuid,
  resolved_at timestamptz,
  constraint case_escalation_state_chk check (
    escalation_state in ('OPEN', 'ACKNOWLEDGED', 'RESOLVED', 'CANCELLED')
  )
);

alter table case_file
add column current_escalation_level int not null default 0;

create table case_decision (
  id uuid primary key,
  case_id uuid not null references case_file(id),
  decision_type text not null,
  outcome text not null,
  decision_state text not null,
  decided_by uuid not null,
  decided_at timestamptz not null,
  reason_code text,
  reason_text text,
  policy_version text,
  effective_from timestamptz,
  effective_to timestamptz,
  created_at timestamptz not null default now(),
  constraint case_decision_state_chk check (
    decision_state in ('DRAFT', 'PROPOSED', 'APPROVED', 'ISSUED', 'REVOKED', 'SUPERSEDED')
  )
);

create table case_decision_evidence (
  decision_id uuid not null references case_decision(id),
  evidence_id uuid not null references case_evidence(id),
  relevance_code text not null,
  primary key (decision_id, evidence_id)
);

create table case_decision_condition (
  id uuid primary key,
  decision_id uuid not null references case_decision(id),
  condition_type text not null,
  condition_text text not null,
  due_at timestamptz,
  condition_state text not null check (
    condition_state in ('OPEN', 'SATISFIED', 'WAIVED', 'BREACHED')
  )
);

Approved with conditions
Rejected with appeal option
Warning issued
Penalty imposed
License suspended until condition satisfied

create table case_evidence (
  id uuid primary key,
  case_id uuid not null references case_file(id),
  evidence_type text not null,
  evidence_state text not null,
  title text not null,
  description text,
  source_type text not null,
  source_ref text,
  storage_uri text,
  content_hash text,
  submitted_by uuid,
  submitted_at timestamptz not null,
  received_at timestamptz,
  verified_by uuid,
  verified_at timestamptz,
  constraint case_evidence_state_chk check (
    evidence_state in ('RECEIVED', 'UNDER_VERIFICATION', 'VERIFIED', 'REJECTED', 'SUPERSEDED')
  )
);

USER_UPLOAD
SYSTEM_IMPORT
INSPECTION_RESULT
EXTERNAL_AGENCY
EMAIL
MANUAL_ENTRY

alter table case_evidence
add column supersedes_evidence_id uuid references case_evidence(id);

create table evidence_custody_event (
  id uuid primary key,
  evidence_id uuid not null references case_evidence(id),
  event_type text not null,
  actor_id uuid not null,
  occurred_at timestamptz not null,
  location_ref text,
  notes text,
  content_hash text
);

create table case_note (
  id uuid primary key,
  case_id uuid not null references case_file(id),
  note_type text not null,
  visibility text not null,
  body text not null,
  created_by uuid not null,
  created_at timestamptz not null default now(),
  edited_at timestamptz,
  deleted_at timestamptz,
  constraint case_note_visibility_chk check (
    visibility in ('INTERNAL', 'EXTERNAL', 'RESTRICTED')
  )
);

create table case_note_version (
  id uuid primary key,
  note_id uuid not null references case_note(id),
  version_no int not null,
  body text not null,
  edited_by uuid not null,
  edited_at timestamptz not null,
  edit_reason text,
  constraint case_note_version_uk unique (note_id, version_no)
);

create table case_communication (
  id uuid primary key,
  case_id uuid not null references case_file(id),
  channel text not null,
  direction text not null check (direction in ('INBOUND', 'OUTBOUND')),
  communication_state text not null,
  subject text,
  body_ref text,
  sent_at timestamptz,
  received_at timestamptz,
  external_message_id text,
  created_at timestamptz not null default now(),
  constraint case_communication_channel_chk check (
    channel in ('EMAIL', 'LETTER', 'PORTAL', 'SMS', 'PHONE', 'MEETING')
  )
);

create table case_communication_party (
  communication_id uuid not null references case_communication(id),
  party_id uuid not null references party(id),
  role_code text not null,
  primary key (communication_id, party_id, role_code)
);

create table case_event (
  id uuid primary key,
  case_id uuid not null references case_file(id),
  event_type text not null,
  event_time timestamptz not null,
  actor_id uuid,
  source text not null,
  correlation_id uuid,
  causation_id uuid,
  payload jsonb not null default '{}'::jsonb,
  created_at timestamptz not null default now()
);

CASE_CREATED
CASE_SUBMITTED
CASE_ASSIGNED
TASK_CREATED
TASK_COMPLETED
EVIDENCE_RECEIVED
EVIDENCE_VERIFIED
SLA_BREACHED
CASE_ESCALATED
DECISION_ISSUED
COMMUNICATION_SENT
CASE_CLOSED

create view case_timeline as
select
  id,
  case_id,
  'STATE_TRANSITION' as item_type,
  transition_code as title,
  occurred_at as item_time,
  actor_id,
  jsonb_build_object(
    'fromState', from_state,
    'toState', to_state,
    'reasonCode', reason_code
  ) as details
from case_state_transition

union all

select
  id,
  case_id,
  'TASK_CREATED' as item_type,
  task_type as title,
  created_at as item_time,
  created_by as actor_id,
  jsonb_build_object('taskState', task_state) as details
from case_task

union all

select
  id,
  case_id,
  'EVIDENCE' as item_type,
  evidence_type as title,
  submitted_at as item_time,
  submitted_by as actor_id,
  jsonb_build_object('evidenceState', evidence_state) as details
from case_evidence;

create table case_timeline_item (
  id uuid primary key,
  case_id uuid not null,
  item_type text not null,
  item_time timestamptz not null,
  title text not null,
  actor_id uuid,
  source_table text not null,
  source_id uuid not null,
  details jsonb not null default '{}'::jsonb,
  constraint case_timeline_source_uk unique (source_table, source_id)
);

create table case_relation (
  id uuid primary key,
  from_case_id uuid not null references case_file(id),
  to_case_id uuid not null references case_file(id),
  relation_type text not null,
  created_at timestamptz not null default now(),
  created_by uuid,
  constraint case_relation_no_self_chk check (from_case_id <> to_case_id),
  constraint case_relation_uk unique (from_case_id, to_case_id, relation_type)
);

CREATED_FROM
APPEAL_OF
REMEDIATION_FOR
DUPLICATE_OF
RELATED_TO
SPLIT_FROM
MERGED_INTO

create table case_duplicate_candidate (
  id uuid primary key,
  case_id uuid not null references case_file(id),
  duplicate_of_case_id uuid not null references case_file(id),
  confidence_score numeric(5,4),
  detection_source text not null,
  review_state text not null check (review_state in ('PENDING', 'CONFIRMED', 'REJECTED')),
  created_at timestamptz not null default now(),
  reviewed_by uuid,
  reviewed_at timestamptz
);

create table case_merge (
  id uuid primary key,
  survivor_case_id uuid not null references case_file(id),
  merged_case_id uuid not null references case_file(id),
  merged_by uuid not null,
  merged_at timestamptz not null,
  merge_reason text not null,
  constraint case_merge_no_self_chk check (survivor_case_id <> merged_case_id)
);

ACTIVE -> MERGED

create table case_split (
  id uuid primary key,
  source_case_id uuid not null references case_file(id),
  new_case_id uuid not null references case_file(id),
  split_by uuid not null,
  split_at timestamptz not null,
  split_reason text not null
);

create table case_wait_period (
  id uuid primary key,
  case_id uuid not null references case_file(id),
  wait_reason text not null,
  wait_state text not null check (wait_state in ('OPEN', 'RESOLVED', 'CANCELLED')),
  started_at timestamptz not null,
  expected_until timestamptz,
  resolved_at timestamptz,
  resolved_by uuid,
  notes text
);

create unique index case_wait_one_open_primary_idx
on case_wait_period (case_id)
where wait_state = 'OPEN';

Submit -> Review -> Request More Info -> Resubmit -> Review -> Decision

create table case_review_cycle (
  id uuid primary key,
  case_id uuid not null references case_file(id),
  cycle_no int not null,
  cycle_state text not null check (cycle_state in ('OPEN', 'COMPLETED', 'CANCELLED')),
  started_at timestamptz not null,
  completed_at timestamptz,
  outcome text,
  constraint case_review_cycle_uk unique (case_id, cycle_no)
);

alter table case_task add column review_cycle_id uuid references case_review_cycle(id);
alter table case_evidence add column review_cycle_id uuid references case_review_cycle(id);

approved_by uuid,
approved_at timestamptz

create table case_approval_request (
  id uuid primary key,
  case_id uuid not null references case_file(id),
  approval_type text not null,
  approval_state text not null check (
    approval_state in ('REQUESTED', 'APPROVED', 'REJECTED', 'CANCELLED')
  ),
  requested_by uuid not null,
  requested_at timestamptz not null,
  completed_at timestamptz,
  policy_version text
);

create table case_approval_action (
  id uuid primary key,
  approval_request_id uuid not null references case_approval_request(id),
  approver_id uuid not null,
  action text not null check (action in ('APPROVE', 'REJECT', 'ABSTAIN')),
  acted_at timestamptz not null,
  reason_text text,
  constraint case_approval_action_uk unique (approval_request_id, approver_id)
);

case_attribute(case_id, name, value)

create table case_field_definition (
  id uuid primary key,
  case_type text not null references case_type(code),
  field_code text not null,
  label text not null,
  data_type text not null check (data_type in ('TEXT', 'NUMBER', 'DATE', 'BOOLEAN', 'ENUM')),
  is_required boolean not null default false,
  is_queryable boolean not null default false,
  validation_rule jsonb not null default '{}'::jsonb,
  constraint case_field_definition_uk unique (case_type, field_code)
);

create table case_field_value (
  case_id uuid not null references case_file(id),
  field_definition_id uuid not null references case_field_definition(id),
  value_text text,
  value_number numeric,
  value_date date,
  value_boolean boolean,
  value_json jsonb,
  updated_at timestamptz not null default now(),
  primary key (case_id, field_definition_id)
);

case status = value from workflow engine internal runtime table

alter table case_file
add column workflow_instance_id text,
add column workflow_definition_id uuid;

WorkflowTaskCompleted -> transition domain task
DomainCaseApproved -> signal workflow engine

with transitions as (
  select
    case_id,
    to_state as state,
    occurred_at as entered_at,
    lead(occurred_at) over (
      partition by case_id
      order by state_version
    ) as exited_at
  from case_state_transition
)
select
  state,
  avg(coalesce(exited_at, now()) - entered_at) as avg_duration
from transitions
group by state;

create table case_daily_metric (
  metric_date date not null,
  case_type text not null,
  team_id uuid,
  opened_count int not null default 0,
  closed_count int not null default 0,
  breached_count int not null default 0,
  escalated_count int not null default 0,
  primary key (metric_date, case_type, team_id)
);

create table case_search_document (
  case_id uuid primary key references case_file(id),
  case_number text not null,
  case_type text not null,
  lifecycle_state text not null,
  party_names text,
  subject_refs text,
  decision_outcome text,
  current_assignee_ids uuid[],
  search_text text,
  updated_at timestamptz not null
);

create table case_access_grant (
  id uuid primary key,
  case_id uuid not null references case_file(id),
  grantee_type text not null check (grantee_type in ('USER', 'TEAM', 'ROLE', 'PARTY')),
  grantee_id uuid not null,
  access_level text not null check (access_level in ('READ', 'WRITE', 'ADMIN')),
  granted_by uuid,
  granted_at timestamptz not null default now(),
  revoked_at timestamptz,
  reason text
);

alter table case_note add column classification text not null default 'NORMAL';
alter table case_evidence add column classification text not null default 'NORMAL';

alter table case_file
add column closure_reason text,
add column retention_class text,
add column retain_until date,
add column legal_hold boolean not null default false;

create table case_retention_policy_assignment (
  id uuid primary key,
  case_id uuid not null references case_file(id),
  retention_policy_code text not null,
  assigned_at timestamptz not null,
  retain_until date not null,
  legal_hold boolean not null default false,
  assigned_by uuid
);

SubmitCase(case_id, actor_id, command_id)

begin;

select *
from case_file
where id = :case_id
for update;

-- guard checks omitted for brevity

update case_file
set lifecycle_state = 'SUBMITTED',
    state_version = state_version + 1,
    updated_at = now()
where id = :case_id;

insert into case_state_transition (...)
values (... 'DRAFT', 'SUBMITTED', 'SUBMIT_CASE', ...);

insert into case_task (...)
values (..., :case_id, 'TRIAGE', 'OPEN', 'Triage submitted case', ...);

insert into case_sla_timer (...)
values (..., :case_id, null, 'TRIAGE_RESPONSE', 'RUNNING', now(), :due_at, :policy_version);

insert into case_event (...)
values (..., :case_id, 'CASE_SUBMITTED', now(), :actor_id, ...);

insert into outbox_event (...)
values (..., 'CASE', :case_id, 'CaseSubmitted', :payload, ...);

commit;

CompleteTask(task_id, actor_id, outcome, command_id)

select *
from task_assignment
where task_id = :task_id
  and released_at is null
  and assignee_type = 'USER'
  and assignee_id = :actor_id;