Reference · Generated 2026-06-25 14:10:32

PostgreSQL schema

Canonical DDL from backend/schema.sql. 20 tables · 10 with tenant_id (multi-tenant isolation).

← Reference annex

audit_log tenant_id

Column	Type
id	SERIAL PRIMARY KEY
tenant_id	INTEGER REFERENCES tenants (id) ON DELETE SET NULL
patient_id	INTEGER REFERENCES patients (id) ON DELETE SET NULL
action	TEXT NOT NULL CHECK (action IN ('DELETE', 'EDIT'))
entity_type	TEXT NOT NULL DEFAULT 'dispensing'
entity_id	INTEGER NOT NULL
performed_by	INTEGER REFERENCES users (id)
snapshot_before	TEXT NOT NULL
snapshot_after	TEXT
changed_fields	TEXT
deletion_reason	TEXT
created_at	TIMESTAMPTZ NOT NULL DEFAULT NOW()

care_group_members

Column	Type
group_id	INTEGER NOT NULL REFERENCES care_groups (id) ON DELETE CASCADE
user_id	INTEGER NOT NULL REFERENCES users (id) ON DELETE CASCADE
display_colour	TEXT NOT NULL DEFAULT '#5a7a6b'
display_name	TEXT
joined_at	TIMESTAMPTZ NOT NULL DEFAULT NOW()

care_groups

Column	Type
id	SERIAL PRIMARY KEY
name_enc	TEXT
patient_id	INTEGER NOT NULL REFERENCES patients (id) ON DELETE CASCADE
created_by	INTEGER REFERENCES users (id)
timezone	TEXT NOT NULL DEFAULT 'Australia/Sydney'
created_at	TIMESTAMPTZ NOT NULL DEFAULT NOW()
enc_version	SMALLINT NOT NULL DEFAULT 0

care_shifts

Column	Type
id	SERIAL PRIMARY KEY
group_id	INTEGER NOT NULL REFERENCES care_groups (id) ON DELETE CASCADE
patient_id	INTEGER NOT NULL REFERENCES patients (id) ON DELETE CASCADE
carer_id	INTEGER REFERENCES users (id)
date	TEXT NOT NULL
time_slot	TEXT NOT NULL DEFAULT 'full_day'
start_time	TEXT
end_time	TEXT
location	TEXT NOT NULL DEFAULT 'home'
priority	TEXT NOT NULL DEFAULT 'normal'
recurrence_rule	TEXT
handover_notes	TEXT
handover_arranged_by_user_id	INTEGER REFERENCES users (id)
wellbeing_note	TEXT
status	TEXT NOT NULL DEFAULT 'scheduled'
created_at	TIMESTAMPTZ NOT NULL DEFAULT NOW()
updated_at	TIMESTAMPTZ NOT NULL DEFAULT NOW()

care_tasks

Column	Type
id	SERIAL PRIMARY KEY
shift_id	INTEGER NOT NULL REFERENCES care_shifts (id) ON DELETE CASCADE
title	TEXT NOT NULL
sort_order	INTEGER NOT NULL DEFAULT 0
completed	BOOLEAN NOT NULL DEFAULT FALSE
completed_at	TIMESTAMPTZ
completed_by	INTEGER REFERENCES users (id)
arranged_by_user_id	INTEGER REFERENCES users (id)
created_at	TIMESTAMPTZ NOT NULL DEFAULT NOW()

dispensings tenant_id

Column	Type
id	SERIAL PRIMARY KEY
tenant_id	INTEGER NOT NULL REFERENCES tenants (id) ON DELETE CASCADE
date	TEXT NOT NULL
time	TEXT NOT NULL
script_id	INTEGER REFERENCES patient_scripts (id) ON DELETE SET NULL
volume	DOUBLE PRECISION NOT NULL
reason_type_id	INTEGER REFERENCES reason_types (id)
notes	TEXT
patient_id	INTEGER REFERENCES patients (id) ON DELETE SET NULL
created_at	TIMESTAMPTZ NOT NULL DEFAULT clock_timestamp()
updated_at	TIMESTAMPTZ NOT NULL DEFAULT clock_timestamp()

invitations tenant_id

Column	Type
id	SERIAL PRIMARY KEY
token	TEXT UNIQUE NOT NULL
tenant_id	INTEGER NOT NULL REFERENCES tenants (id) ON DELETE CASCADE
created_by	INTEGER NOT NULL REFERENCES users (id)
message	TEXT
expires_at	TIMESTAMPTZ NOT NULL
used_at	TIMESTAMPTZ
used_by	INTEGER REFERENCES users (id)
created_at	TIMESTAMPTZ NOT NULL DEFAULT NOW()

patient_access

Column	Type
patient_id	INTEGER NOT NULL REFERENCES patients (id) ON DELETE CASCADE
user_id	INTEGER NOT NULL REFERENCES users (id) ON DELETE CASCADE
role	TEXT NOT NULL DEFAULT 'carer'
granted_by	INTEGER REFERENCES users (id)
granted_at	TIMESTAMPTZ NOT NULL DEFAULT NOW()

patient_appointments

Column	Type
id	SERIAL PRIMARY KEY
patient_id	INTEGER NOT NULL REFERENCES patients (id) ON DELETE CASCADE
date	TEXT NOT NULL
appointment_time	TEXT NOT NULL
title	TEXT NOT NULL
address	TEXT
arranged_by_user_id	INTEGER REFERENCES users (id)
created_at	TIMESTAMPTZ NOT NULL DEFAULT NOW()
updated_at	TIMESTAMPTZ NOT NULL DEFAULT NOW()

patient_scripts tenant_id

Column	Type
id	SERIAL PRIMARY KEY
tenant_id	INTEGER NOT NULL REFERENCES tenants (id) ON DELETE CASCADE
patient_id	INTEGER NOT NULL REFERENCES patients (id) ON DELETE CASCADE
medicine_name_raw	TEXT NOT NULL
strength	TEXT
form	TEXT
dose_text	TEXT
frequency_text	TEXT
dose_instructions	TEXT
unit	TEXT NOT NULL DEFAULT 'tablet'
quantity_per_fill	INTEGER
total_fills_authorised	INTEGER
fills_completed	INTEGER NOT NULL DEFAULT 0
stock_adjustment	INTEGER NOT NULL DEFAULT 0
last_physical_count	INTEGER
last_physical_count_at	TIMESTAMPTZ
low_stock_threshold	INTEGER NOT NULL DEFAULT 7
prescriber_name	TEXT
prescriber_practice	TEXT
issued_date	TEXT
expiry_date	TEXT
image_url	TEXT
image_stored_at	TIMESTAMPTZ
scan_status	TEXT NOT NULL DEFAULT 'manual'
scan_raw_text	TEXT
scan_confidence	REAL
status	TEXT NOT NULL DEFAULT 'active'
color_hex	TEXT NOT NULL DEFAULT '#7C3AED'
as_needed	BOOLEAN NOT NULL DEFAULT FALSE
notes	TEXT
created_by	INTEGER REFERENCES users (id) ON DELETE SET NULL
created_at	TIMESTAMPTZ NOT NULL DEFAULT NOW()
updated_at	TIMESTAMPTZ NOT NULL DEFAULT NOW()

patient_sleep_entries tenant_id

Column	Type
id	SERIAL PRIMARY KEY
tenant_id	INTEGER NOT NULL REFERENCES tenants (id) ON DELETE CASCADE
patient_id	INTEGER NOT NULL REFERENCES patients (id) ON DELETE CASCADE
date	TEXT NOT NULL,
start_time	TEXT NOT NULL,
end_time	TEXT NOT NULL,
notes	TEXT
recorded_by	INTEGER NOT NULL REFERENCES users (id)
created_at	TIMESTAMPTZ NOT NULL DEFAULT NOW()
updated_at	TIMESTAMPTZ NOT NULL DEFAULT NOW()

patients tenant_id

Column	Type
id	SERIAL PRIMARY KEY
tenant_id	INTEGER NOT NULL REFERENCES tenants (id) ON DELETE CASCADE
full_name_enc	TEXT
photo_url	TEXT
date_of_birth_enc	TEXT
address	TEXT
medical_summary	TEXT
created_by	INTEGER REFERENCES users (id)
created_at	TIMESTAMPTZ NOT NULL DEFAULT NOW()
enc_version	SMALLINT NOT NULL DEFAULT 0

reason_types tenant_id

Column	Type
id	SERIAL PRIMARY KEY
tenant_id	INTEGER NOT NULL REFERENCES tenants (id) ON DELETE CASCADE
patient_id	INTEGER NOT NULL REFERENCES patients (id) ON DELETE CASCADE
name	TEXT NOT NULL
description	TEXT
color_hex	TEXT NOT NULL DEFAULT '#6B7280'
display_order	INTEGER NOT NULL DEFAULT 0
active	BOOLEAN NOT NULL DEFAULT TRUE
created_at	TIMESTAMPTZ NOT NULL DEFAULT NOW()

schema_migrations

Column	Type
id	SERIAL PRIMARY KEY
version	TEXT UNIQUE NOT NULL
description	TEXT
applied_at	TIMESTAMPTZ NOT NULL DEFAULT NOW()

script_alert_dismissals tenant_id

Column	Type
id	SERIAL PRIMARY KEY
tenant_id	INTEGER NOT NULL REFERENCES tenants (id) ON DELETE CASCADE
user_id	INTEGER NOT NULL REFERENCES users (id) ON DELETE CASCADE
patient_id	INTEGER NOT NULL REFERENCES patients (id) ON DELETE CASCADE
script_id	INTEGER NOT NULL REFERENCES patient_scripts (id) ON DELETE CASCADE
alert_type	TEXT NOT NULL
dismissed_at	TIMESTAMPTZ NOT NULL DEFAULT NOW()
alert_type	IN (

stripe_tier_local_prices

Column	Type
tier_slug	TEXT NOT NULL
currency	TEXT NOT NULL
amount_cents	INTEGER NOT NULL CHECK (amount_cents > 0)
stripe_price_id	TEXT UNIQUE
active	BOOLEAN NOT NULL DEFAULT TRUE
created_at	TIMESTAMPTZ NOT NULL DEFAULT NOW()
updated_at	TIMESTAMPTZ NOT NULL DEFAULT NOW()

subscriptions tenant_id

Column	Type
id	SERIAL PRIMARY KEY
tenant_id	INTEGER NOT NULL REFERENCES tenants (id) ON DELETE CASCADE
module	TEXT NOT NULL
status	TEXT NOT NULL DEFAULT 'inactive'
valid_until	TIMESTAMPTZ
activated_by	INTEGER REFERENCES users (id)
activated_at	TIMESTAMPTZ
created_at	TIMESTAMPTZ NOT NULL DEFAULT NOW()

tenant_memberships tenant_id

Column	Type
tenant_id	INTEGER NOT NULL REFERENCES tenants (id) ON DELETE CASCADE
user_id	INTEGER NOT NULL REFERENCES users (id) ON DELETE CASCADE
role	TEXT NOT NULL DEFAULT 'member'
status	TEXT NOT NULL DEFAULT 'active'
invited_by	INTEGER REFERENCES users (id)
joined_at	TIMESTAMPTZ NOT NULL DEFAULT NOW()

tenants

Column	Type
id	SERIAL PRIMARY KEY
name_enc	TEXT
slug	TEXT UNIQUE NOT NULL
owner_user_id	INTEGER NOT NULL REFERENCES users (id)
status	TEXT NOT NULL DEFAULT 'trialing'
timezone	TEXT NOT NULL DEFAULT 'Australia/Sydney'
created_at	TIMESTAMPTZ NOT NULL DEFAULT NOW()
enc_version	SMALLINT NOT NULL DEFAULT 0

users

Column	Type
id	SERIAL PRIMARY KEY
email_enc	TEXT
email_idx	TEXT UNIQUE
phone_enc	TEXT
username	TEXT UNIQUE NOT NULL
password_hash	TEXT
role	TEXT NOT NULL DEFAULT 'user'
status	TEXT NOT NULL DEFAULT 'pending'
google_id	TEXT UNIQUE
avatar_url	TEXT
profile_colour	TEXT NOT NULL DEFAULT '#7C3AED'
list_on_key_contacts	BOOLEAN NOT NULL DEFAULT FALSE
is_super_admin	SMALLINT NOT NULL DEFAULT 0
must_change_password	BOOLEAN NOT NULL DEFAULT FALSE
created_at	TIMESTAMPTZ NOT NULL DEFAULT NOW()
last_login	TIMESTAMPTZ
enc_version	SMALLINT NOT NULL DEFAULT 0