capstone

Tables (13)

accounts

  • id — uuid — Primary key — NOT NULL
  • login_id — varchar(100) — NOT NULL
  • password_hash — text
  • display_name — varchar(100) — NOT NULL
  • role — varchar(20) — NOT NULL
  • status — varchar(20) — NOT NULL
  • created_at — timestamptz — NOT NULL

call_analyses

  • id — uuid — Primary key — NOT NULL
  • call_id — uuid — Foreign key → calls.id — NOT NULL
  • pipeline_version — text — NOT NULL
  • status — varchar(20) — NOT NULL
  • transcript_object_key — text
  • private_result_object_key — text
  • applied_request_id — uuid — Foreign key → service_requests.id
  • applied_content_version — bigint
  • created_at — timestamptz — NOT NULL
  • completed_at — timestamptz

calls

  • id — uuid — Primary key — NOT NULL
  • request_id — uuid — Foreign key → service_requests.id
  • provider — varchar(100) — NOT NULL
  • external_call_id — text — NOT NULL
  • caller_phone_ciphertext — bytea
  • encryption_key_ref — text
  • recording_object_key — text
  • started_at — timestamptz
  • ended_at — timestamptz
  • received_at — timestamptz — NOT NULL

dispatches

  • id — uuid — Primary key — NOT NULL
  • request_id — uuid — Foreign key → recommendation_candidates.request_id — NOT NULL
  • run_id — uuid — Foreign key → recommendation_candidates.run_id — NOT NULL
  • candidate_id — uuid — Foreign key → recommendation_candidates.id — NOT NULL
  • technician_id — uuid — Foreign key → recommendation_candidates.technician_id — NOT NULL
  • attempt_no — smallint — NOT NULL
  • idempotency_key — uuid — NOT NULL
  • status — varchar(20) — NOT NULL
  • offered_at — timestamptz — NOT NULL
  • expires_at — timestamptz — NOT NULL
  • accepted_at — timestamptz
  • closed_at — timestamptz
  • close_reason — text
  • version — bigint — NOT NULL

outbox_events

  • id — uuid — Primary key — NOT NULL
  • dispatch_id — uuid — Foreign key → dispatches.id — NOT NULL
  • recipient_account_id — uuid — Foreign key → accounts.id — NOT NULL
  • event_type — varchar(100) — NOT NULL
  • deduplication_key — text — NOT NULL
  • status — varchar(20) — NOT NULL
  • delivery_attempts — integer — NOT NULL
  • available_at — timestamptz — NOT NULL
  • locked_until — timestamptz
  • created_at — timestamptz — NOT NULL
  • sent_at — timestamptz

recommendation_approvals

  • run_id — uuid — Foreign key → recommendation_runs.id — NOT NULL
  • request_id — uuid — Foreign key → recommendation_runs.request_id — NOT NULL
  • approved_by — uuid — Foreign key → accounts.id — NOT NULL
  • approved_at — timestamptz — NOT NULL
  • revoked_at — timestamptz

recommendation_candidates

  • id — uuid — Primary key — NOT NULL
  • run_id — uuid — Foreign key → recommendation_runs.id — NOT NULL
  • request_id — uuid — Foreign key → recommendation_runs.request_id — NOT NULL
  • technician_id — uuid — Foreign key → technicians.id — NOT NULL
  • candidate_rank — smallint — NOT NULL
  • travel_seconds — integer — NOT NULL
  • origin_latitude — numeric(10,7) — NOT NULL
  • origin_longitude — numeric(10,7) — NOT NULL
  • location_measured_at — timestamptz — NOT NULL
  • route_calculated_at — timestamptz — NOT NULL

recommendation_runs

  • id — uuid — Primary key — NOT NULL
  • request_id — uuid — Foreign key → service_requests.id — NOT NULL
  • round_no — integer — NOT NULL
  • input_content_version — bigint — NOT NULL
  • algorithm_version — text — NOT NULL
  • algorithm_config — jsonb — NOT NULL
  • route_provider — text — NOT NULL
  • status — varchar(30) — NOT NULL
  • created_at — timestamptz — NOT NULL
  • completed_at — timestamptz

request_events

  • id — uuid — Primary key — NOT NULL
  • request_id — uuid — Foreign key → dispatches.request_id — NOT NULL
  • dispatch_id — uuid — Foreign key → dispatches.id
  • actor_account_id — uuid — Foreign key → accounts.id
  • event_type — varchar(100) — NOT NULL
  • metadata — jsonb — NOT NULL
  • created_at — timestamptz — NOT NULL

request_private_data

  • request_id — uuid — Foreign key → service_requests.id — NOT NULL
  • contact_phone_ciphertext — bytea
  • address_ciphertext — bytea
  • detail_address_ciphertext — bytea
  • encryption_key_ref — text
  • destination_latitude — numeric(10,7)
  • destination_longitude — numeric(10,7)
  • updated_at — timestamptz — NOT NULL

service_requests

  • id — uuid — Primary key — NOT NULL
  • status — varchar(30) — NOT NULL
  • area_label — varchar(200)
  • symptom_summary — text
  • content_version — bigint — NOT NULL
  • version — bigint — NOT NULL
  • created_at — timestamptz — NOT NULL
  • updated_at — timestamptz — NOT NULL

technician_locations

  • technician_id — uuid — Foreign key → technicians.id — NOT NULL
  • latitude — numeric(10,7) — NOT NULL
  • longitude — numeric(10,7) — NOT NULL
  • accuracy_meters — numeric(10,2) — NOT NULL
  • measured_at — timestamptz — NOT NULL
  • received_at — timestamptz — NOT NULL

technicians

  • id — uuid — Primary key — NOT NULL
  • account_id — uuid — Foreign key → accounts.id — NOT NULL
  • work_status — varchar(20) — NOT NULL
  • status_changed_at — timestamptz — NOT NULL
  • version — bigint — NOT NULL