RTL Complete Database Schema

Entity Relationship Overview

Demonstrates how the Administrative catalog tables link to the Core transactional tables.

users

System authentication for Admin and Lab Staff. Fully hashed passwords.

patients

Core PHI table. Uses Laravel 13 `AsEncryptedArray` for AES-256 encryption at rest.

locations

operating_hours

Replaces the legacy `time_slot`, `day_off`, and `special_day` tables.

services

medical_references

Replaces `icd_codes` and `specimen_types` tables.

appointments

Core logistics hub. Replaces the massive legacy `appoints` table.

order_items

clinical_intakes

test_results

payments

patient_insurances

coupons

Condenses 4 legacy tables into 1 elegant JSONB rule-set.

audit_logs

system_settings

Complete SQL DDL (Ordered for Foreign Keys)

-- 1. Users (Admin & Staff)
CREATE TABLE users (
  id              uuid         PRIMARY KEY DEFAULT gen_random_uuid(),
  name            varchar(255) NOT NULL,
  email           varchar(255) UNIQUE NOT NULL,
  password        varchar(255) NOT NULL,
  is_active       boolean      DEFAULT true
);

-- 2. Patients (Model Encrypted PHI)
CREATE TABLE patients (
  id              uuid         PRIMARY KEY DEFAULT gen_random_uuid(),
  first_name      text         NOT NULL,
  last_name       text         NOT NULL,
  dob             text         NOT NULL,
  email           varchar(255) NOT NULL,
  phone           varchar(255) NOT NULL
);

-- 3. Locations (Clinics)
CREATE TABLE locations (
  id              serial       PRIMARY KEY,
  name            varchar(255) NOT NULL,
  address         jsonb        NOT NULL,
  is_active       boolean      DEFAULT true
);

-- 4. Operating Hours
CREATE TABLE operating_hours (
  id              serial       PRIMARY KEY,
  location_id     integer      REFERENCES locations(id) ON DELETE CASCADE,
  day_of_week     integer,
  specific_date   date,
  open_time       time,
  close_time      time,
  is_closed       boolean      DEFAULT false
);

-- 5. Services (Test Catalog)
CREATE TABLE services (
  id              serial       PRIMARY KEY,
  name            varchar(255) NOT NULL,
  test_type       varchar(100) NOT NULL,
  price           numeric(10,2)NOT NULL,
  duration_mins   integer      DEFAULT 15,
  is_active       boolean      DEFAULT true
);

-- 6. Medical References (ICD/Specimens)
CREATE TABLE medical_references (
  id              serial       PRIMARY KEY,
  reference_type  varchar(50)  NOT NULL,
  code            varchar(50)  NOT NULL,
  description     text
);

-- 7. Appointments (Logistics)
CREATE TABLE appointments (
  id              uuid         PRIMARY KEY DEFAULT gen_random_uuid(),
  patient_id      uuid         REFERENCES patients(id) ON DELETE CASCADE,
  location_id     integer      REFERENCES locations(id),
  scheduled_at    timestamptz  NOT NULL,
  status          varchar(50)  DEFAULT 'booked',
  legacy_order_id varchar(50)
);

-- 8. Clinical Intakes (JSONB Forms)
CREATE TABLE clinical_intakes (
  id              uuid         PRIMARY KEY DEFAULT gen_random_uuid(),
  appointment_id  uuid         REFERENCES appointments(id) ON DELETE CASCADE,
  intake_data     jsonb        NOT NULL
);

-- 9. Order Items (Purchased Tests)
CREATE TABLE order_items (
  id              uuid         PRIMARY KEY DEFAULT gen_random_uuid(),
  appointment_id  uuid         REFERENCES appointments(id) ON DELETE CASCADE,
  service_id      integer      REFERENCES services(id),
  price           numeric(10,2)NOT NULL
);

-- 10. Test Results (Lab Queue)
CREATE TABLE test_results (
  id              uuid         PRIMARY KEY DEFAULT gen_random_uuid(),
  order_item_id   uuid         REFERENCES order_items(id) ON DELETE CASCADE,
  analyte_name    varchar(255) NOT NULL,
  result_value    varchar(255) NOT NULL,
  is_abnormal     boolean      DEFAULT false
);

-- 11. Payments (Square)
CREATE TABLE payments (
  id              uuid         PRIMARY KEY DEFAULT gen_random_uuid(),
  appointment_id  uuid         REFERENCES appointments(id),
  square_receipt_id varchar(255) NOT NULL,
  amount          numeric(10,2)NOT NULL
);

-- 12. Patient Insurances
CREATE TABLE patient_insurances (
  id              uuid         PRIMARY KEY DEFAULT gen_random_uuid(),
  appointment_id  uuid         REFERENCES appointments(id) ON DELETE CASCADE,
  provider_name   varchar(255) NOT NULL,
  policy_number   text         NOT NULL,
  group_number    text,
  card_front_url  text,
  card_back_url   text
);

-- 13. Coupons (Discount Engine)
CREATE TABLE coupons (
  id              serial       PRIMARY KEY,
  code            varchar(50)  UNIQUE NOT NULL,
  discount_type   varchar(20)  NOT NULL,
  discount_value  numeric(10,2)NOT NULL,
  valid_from      timestamptz,
  valid_until     timestamptz,
  usage_rules     jsonb,
  is_active       boolean      DEFAULT true
);

-- 14. Audit Logs (HIPAA)
CREATE TABLE audit_logs (
  id              bigserial    PRIMARY KEY,
  user_id         uuid         REFERENCES users(id),
  action          varchar(255) NOT NULL,
  resource_id     uuid,
  ip_address      inet,
  created_at      timestamptz  DEFAULT now()
);

-- 15. System Settings
CREATE TABLE system_settings (
  id              serial       PRIMARY KEY,
  setting_key     varchar(100) UNIQUE NOT NULL,
  setting_value   jsonb        NOT NULL
);