sdsd

Tables (15)

communication_request

  • request_id — INTEGER — Primary key — NOT NULL
  • utterance_id — INTEGER — Foreign key → utterance.utt_id — NOT NULL
  • patient_id — INTEGER — Foreign key → patient_profile.patient_id — NOT NULL
  • ward_code — VARCHAR(32) — NOT NULL
  • room_number — VARCHAR(32) — NOT NULL
  • phrase_code — VARCHAR(64)
  • text — VARCHAR(200) — NOT NULL
  • category — VARCHAR(16) — NOT NULL
  • confidence — NUMERIC(4, 3)
  • priority — VARCHAR(16) — NOT NULL
  • status — VARCHAR(16) — NOT NULL
  • requested_at — TIMESTAMP WITH TIME ZONE — NOT NULL
  • acknowledged_at — TIMESTAMP WITH TIME ZONE
  • acknowledged_by — INTEGER — Foreign key → users.user_id
  • completed_at — TIMESTAMP WITH TIME ZONE
  • completed_by — INTEGER — Foreign key → users.user_id
  • resolution_note — VARCHAR(500)

login_session

  • login_session_id — INTEGER — Primary key — NOT NULL
  • user_id — INTEGER — Foreign key → users.user_id — NOT NULL
  • session_token — VARCHAR(128) — NOT NULL
  • created_at — TIMESTAMP WITH TIME ZONE — NOT NULL
  • expires_at — TIMESTAMP WITH TIME ZONE — NOT NULL
  • revoked_at — TIMESTAMP WITH TIME ZONE

patient_profile

  • patient_id — INTEGER — Primary key — NOT NULL
  • user_id — INTEGER — Foreign key → users.user_id — NOT NULL
  • patient_code — VARCHAR(64) — NOT NULL
  • display_name — VARCHAR(50) — NOT NULL
  • ward_code — VARCHAR(32) — Foreign key → ward.ward_code — NOT NULL
  • room_number — VARCHAR(32) — NOT NULL
  • admitted_on — DATE — NOT NULL
  • communication_status — VARCHAR(32) — NOT NULL
  • assistive_method — VARCHAR(100)
  • notes — TEXT

phrase

  • phrase_id — INTEGER — Primary key — NOT NULL
  • phrase_code — VARCHAR(64) — NOT NULL
  • phrase_text — VARCHAR(100) — NOT NULL
  • category — VARCHAR(16) — NOT NULL

phrase_usage_stat

  • user_id — INTEGER — Foreign key → users.user_id — NOT NULL
  • phrase_code — VARCHAR(64) — Foreign key → phrase.phrase_code — NOT NULL
  • usage_count — INTEGER — NOT NULL
  • accepted_count — INTEGER — NOT NULL
  • corrected_count — INTEGER — NOT NULL
  • last_used_at — TIMESTAMP WITH TIME ZONE
  • updated_at — TIMESTAMP WITH TIME ZONE — NOT NULL

request_event

  • event_id — INTEGER — Primary key — NOT NULL
  • request_id — INTEGER — Foreign key → communication_request.request_id — NOT NULL
  • event_type — VARCHAR(16) — NOT NULL
  • actor_user_id — INTEGER — Foreign key → users.user_id
  • occurred_at — TIMESTAMP WITH TIME ZONE — NOT NULL
  • note — VARCHAR(500)

request_idempotency

  • idempotency_id — INTEGER — Primary key — NOT NULL
  • actor_user_id — INTEGER — Foreign key → users.user_id — NOT NULL
  • request_id — INTEGER — Foreign key → communication_request.request_id — NOT NULL
  • idempotency_key — VARCHAR(128) — NOT NULL
  • operation — VARCHAR(16) — NOT NULL
  • request_fingerprint — VARCHAR(64) — NOT NULL
  • created_at — TIMESTAMP WITH TIME ZONE — NOT NULL

session

  • session_id — INTEGER — Primary key — NOT NULL
  • mode — VARCHAR(16) — NOT NULL
  • started_at — TIMESTAMP WITH TIME ZONE — NOT NULL
  • ended_at — TIMESTAMP WITH TIME ZONE

staff_ward_access

  • staff_user_id — INTEGER — Foreign key → users.user_id — NOT NULL
  • ward_code — VARCHAR(32) — Foreign key → ward.ward_code — NOT NULL
  • created_at — TIMESTAMP WITH TIME ZONE — NOT NULL

training_candidate

  • sample_id — VARCHAR(36) — Primary key — NOT NULL
  • user_id — INTEGER — Foreign key → users.user_id — NOT NULL
  • utterance_id — INTEGER — Foreign key → utterance.utt_id — NOT NULL
  • video_id — INTEGER — Foreign key → video_asset.video_id — NOT NULL
  • model_version — VARCHAR(100)
  • predicted_phrase_code — VARCHAR(64)
  • confidence — NUMERIC(4, 3)
  • status — VARCHAR(20) — NOT NULL
  • created_at — TIMESTAMP WITH TIME ZONE — NOT NULL
  • updated_at — TIMESTAMP WITH TIME ZONE — NOT NULL
  • published_at — TIMESTAMP WITH TIME ZONE

user_consent

  • user_id — INTEGER — Foreign key → users.user_id — NOT NULL
  • model_training_consent — BOOLEAN — NOT NULL
  • consent_version — VARCHAR(50) — NOT NULL
  • created_at — TIMESTAMP WITH TIME ZONE — NOT NULL
  • updated_at — TIMESTAMP WITH TIME ZONE — NOT NULL

users

  • user_id — INTEGER — Primary key — NOT NULL
  • storage_uuid — UUID — NOT NULL
  • username — VARCHAR(50) — NOT NULL
  • password_hash — VARCHAR(255) — NOT NULL
  • display_name — VARCHAR(50) — NOT NULL
  • role — VARCHAR(16) — NOT NULL
  • created_at — TIMESTAMP WITH TIME ZONE — NOT NULL

utterance

  • utt_id — INTEGER — Primary key — NOT NULL
  • user_id — INTEGER — Foreign key → users.user_id
  • session_id — INTEGER — Foreign key → session.session_id
  • phrase_id — INTEGER — Foreign key → phrase.phrase_id
  • raw_text — VARCHAR(200)
  • corrected_text — VARCHAR(200)
  • confidence — NUMERIC(4, 3)
  • model_version — VARCHAR(100)
  • created_at — TIMESTAMP WITH TIME ZONE — NOT NULL

video_asset

  • video_id — INTEGER — Primary key — NOT NULL
  • user_id — INTEGER — Foreign key → users.user_id — NOT NULL
  • utterance_id — INTEGER — Foreign key → utterance.utt_id — NOT NULL
  • idempotency_key — VARCHAR(64) — NOT NULL
  • object_key — VARCHAR(512) — NOT NULL
  • original_mime_type — VARCHAR(100) — NOT NULL
  • normalized_mime_type — VARCHAR(100)
  • codec — VARCHAR(50)
  • width — INTEGER
  • height — INTEGER
  • fps — NUMERIC(7, 3)
  • duration_ms — INTEGER
  • size_bytes — BIGINT — NOT NULL
  • checksum — VARCHAR(128) — NOT NULL
  • storage_status — VARCHAR(32) — NOT NULL
  • storage_purpose — VARCHAR(32) — NOT NULL
  • consent_version — VARCHAR(50)
  • created_at — TIMESTAMP WITH TIME ZONE — NOT NULL
  • retention_until — TIMESTAMP WITH TIME ZONE
  • deleted_at — TIMESTAMP WITH TIME ZONE

ward

  • ward_code — VARCHAR(32) — Primary key — NOT NULL
  • ward_name — VARCHAR(100) — NOT NULL
  • created_at — TIMESTAMP WITH TIME ZONE — NOT NULL