The native backend is a purpose-built data platform for the Revique healthcare / medspa
business. Where the existing Portal is a React front-end shipped through an AWS CI/CD pipeline
(documented on the main page), the native backend is the system of record behind it: a
normalized PostgreSQL database that owns catalog & inventory, service packages, gift cards, a
loyalty program, promotions, native card payments, the patient portal, cross-system notifications,
and a fine-grained role-based access-control (RBAC) system.
It was built by Codex as a fresh, "native" replacement layer that runs alongside the legacy
HPT system — many tables carry legacy_* columns (legacy IDs / payloads) so
records can be reconciled back to the old platform during migration.
What it is
- Aurora PostgreSQL 16.13 cluster in
us-east-1
- 36 tables across 10 functional domains
- UUID / identity-based primary keys (no sequences)
pgcrypto enabled for hashing & UUID generation- Append-only ledgers for money & balance movements
How it relates to the Portal
- Portal (React/CI-CD) = presentation & delivery
- Native backend = authoritative business data
- Bridged to legacy HPT via
legacy_* keys
- Notifications ingest from both
legacy_hpt and revique
- Payments processed natively via the FluidPay gateway
The database has no public IP. Engineers reach it through an AWS SSM Session Manager
port-forwarding tunnel that hops through a tiny bastion EC2 instance sitting in the same VPC as
the cluster. There is no SSH key and no open inbound port — your AWS credentials are the
authentication, and Session Manager brokers the encrypted channel.
💻
Your machine
aws cli + SSM plugin
psql → 127.0.0.1:5433
→
🛡️
Bastion EC2
revique-api-dev-db-tunnel
t4g.nano · i-0****
→
🗄️
Aurora PostgreSQL
rev****.amazonaws.com
db: revique · :5432
Engine
Aurora PostgreSQL 16.13, region us-east-1. UUID PKs via gen_random_uuid().
Cryptography
pgcrypto extension enabled — used for UUID generation and for hashing sensitive values (gift-card numbers, portal login identifiers, emulation tokens).
Access model
Private cluster · bastion i-0**** · SSM port-forward tunnel · IAM-based auth (no SSH key, no public endpoint).
Credentials live in AWS Secrets Manager at
/revique/revique-api/dev/database/admin (host, port, dbname, username,
password). Reference the path — never copy the password into code, tickets, or this page.
A reproducible runbook for opening a read/write session against the dev cluster. All sensitive
values are masked — substitute the real ones from describe-* output and
Secrets Manager. Commands assume an AWS CLI profile named hptdev.
All eight steps use the masked identifiers shown on this page
(i-0****, rev****.amazonaws.com). Replace them
with the real values you retrieve in steps b and c.
a
Verify your AWS identity
# Confirm you're authenticated into the right account
aws sts get-caller-identity --profile hptdev
# → Account: 292**** (masked) · Arn: arn:aws:sts::292****:...
b
Find the Aurora cluster endpoint
aws rds describe-db-clusters --region us-east-1 --profile hptdev \
--query "DBClusters[?contains(DBClusterIdentifier, 'revique')].[DBClusterIdentifier,Endpoint,EngineVersion]" \
--output table
# → Endpoint: rev****.us-east-1.rds.amazonaws.com · EngineVersion: 16.13 (masked)
c
Find the bastion instance
aws ssm describe-instance-information --region us-east-1 --profile hptdev \
--query "InstanceInformationList[?contains(ComputerName, 'revique')].[InstanceId,ComputerName]" \
--output table
# → i-0**** revique-api-dev-db-tunnel (t4g.nano, masked)
d
Install the Session Manager plugin (Ubuntu) — if missing
curl "https://s3.amazonaws.com/session-manager-downloads/plugin/latest/ubuntu_64bit/session-manager-plugin.deb" \
-o "session-manager-plugin.deb"
sudo dpkg -i session-manager-plugin.deb
session-manager-plugin --version # verify install
e
Get credentials from Secrets Manager (never print the password)
# Retrieve the secret — inspect it in a secure shell; do NOT echo/paste the password anywhere
aws secretsmanager get-secret-value --region us-east-1 --profile hptdev \
--secret-id /revique/revique-api/dev/database/admin \
--query SecretString --output text
# → JSON: { "username": "revique_admin", "host": "rev****...", "port": 5432, "dbname": "revique", "password": "•••••" }
Treat the password as write-only: let
psql/PGAdmin prompt for it, or export it to an env var in a private
shell. Do not commit it or include it in shared docs.
f
Open the SSM port-forward tunnel (local port 5433)
aws ssm start-session --profile hptdev --region us-east-1 \
--target i-0**** \
--document-name AWS-StartPortForwardingSessionToRemoteHost \
--parameters '{"host":["rev****.us-east-1.rds.amazonaws.com"],"portNumber":["5432"],"localPortNumber":["5433"]}'
# Leave this session running — it forwards 127.0.0.1:5433 → Aurora:5432
Gotcha we hit: use local port
5433, not 5432. If you already run a local PostgreSQL it will occupy 5432, and the tunnel
will silently collide with it — you'll end up connecting to your own machine instead of Aurora.
5433 keeps them separate.
g
Connect with psql / PGAdmin
# In a second terminal (tunnel stays open in the first):
psql "host=127.0.0.1 port=5433 dbname=revique user=revique_admin sslmode=require"
# PGAdmin → New Server → Host 127.0.0.1 Port 5433 DB revique User revique_admin
# Password: from Secrets Manager (step e)
h
Export the schema (structure only, no data)
pg_dump "host=127.0.0.1 port=5433 dbname=revique user=revique_admin sslmode=require" \
--schema-only --no-owner --no-privileges \
-f revique-native-backend-schema.sql
# This is exactly how the schema behind this page was produced.
The raw
revique-native-backend-schema.sql still contains unmasked identifiers.
Keep it local — do not upload it with the shared site.
Design characteristics
- UUID identity: every entity PK is
uuid DEFAULT gen_random_uuid() — zero sequences.
- Money in cents: integer
*_cents columns with non-negative CHECK constraints.
- Append-only ledgers: gift-card, package and promotion movements are immutable (enforced by triggers).
- Multi-location: pricing / configs scoped by
practice_location_id, often with partial unique indexes for a "global default" vs per-location row.
Cross-cutting conventions
created_at / updated_at on virtually every table.set_updated_at() trigger auto-touches updated_at on UPDATE.is_active / is_deleted soft-state flags; partial indexes exclude deleted rows.legacy_* columns + legacy_payload jsonb preserve the migration lineage from HPT.idempotency_key + unique indexes protect money operations from double-apply.
Tables grouped into their business domains. For each, the key columns are listed with type,
nullability and default. ★ marks a primary-key column;
◆ marks a foreign-key column. Not every CHECK constraint is shown —
see the notes for the important ones.
★ primary key
◆ foreign key
PK / UNIQUE / APPEND-ONLY
Catalog / Inventory
4 tables
The product / service / fee catalog and its per-location pricing, inventory lots and provider mappings.
catalog_items— master items: products, services & feesPK id
| Column | Type | Null | Notes |
|---|
| ★ id | uuid | not null | gen_random_uuid() |
| practice_id | text | not null | tenant scope |
| kind | text | not null | CHECK in (product, service, fee) |
| name | text | not null | indexed lower(name) per practice |
| sku / upc | text | null | partial-unique per practice+kind |
| service_duration_minutes | integer | null | CHECK > 0 |
| is_procedural / is_lot_tracked / is_taxable | boolean | not null | default false |
| is_active / is_deleted | boolean | not null | soft-state flags |
| legacy_item_id / legacy_service_id / legacy_fee_id / legacy_payload | text / jsonb | null | HPT migration lineage |
catalog_item_location_pricing— per-location prices, tax & discounts◆ catalog_item_id → catalog_items
| Column | Type | Null | Notes |
|---|
| ★ id | uuid | not null | gen_random_uuid() |
| ◆ catalog_item_id | uuid | not null | → catalog_items (CASCADE) |
| practice_location_id | text | null | NULL row = global default (partial unique) |
| cash_price_cents / card_price_cents / unit_price_cents | integer | mixed | non-negative CHECKs |
| cost_cents / overhead_cents / discount_cents | integer | null | margin inputs |
| discount_percent / tax_percent | numeric(7,4) | mixed | 0–100 range CHECK |
catalog_item_lots— inventory lots (lot #, expiry, quantities)◆ catalog_item_id
| Column | Type | Null | Notes |
|---|
| ★ id · ◆ catalog_item_id | uuid | not null | → catalog_items (CASCADE) |
| practice_location_id / lot_number | text | not null | stock kept per location |
| expiration_date / received_on | date | null | |
| received_quantity / remaining_quantity | integer | not null | CHECK remaining ≤ received |
catalog_item_provider_mappings— which providers can deliver an item◆ catalog_item_id
| Column | Type | Null | Notes |
|---|
| ★ id · ◆ catalog_item_id | uuid | not null | → catalog_items (CASCADE) |
| provider_id | text | not null | global- and location-scoped partial uniques |
| practice_location_id | text | null | NULL = applies everywhere |
Gift Cards
4 tables
Definitions (templates), issued cards, per-location availability and an append-only ledger of every balance movement.
gift_card_definitions— gift-card templatesPK id
| Column | Type | Null | Notes |
|---|
| ★ id | uuid | not null | |
| practice_id / name | text | not null | unique on (practice, lower(name)) |
| amount_mode | text | not null | CHECK in (fixed, custom) |
| fixed_amount_cents | integer | null | required iff amount_mode=fixed |
| currency_code | char(3) | not null | default USD, ^[A-Z]{3}$ |
gift_cards— issued cards & live balance◆ gift_card_definition_id
| Column | Type | Null | Notes |
|---|
| ★ id | uuid | not null | |
| ◆ gift_card_definition_id | uuid | not null | → gift_card_definitions (RESTRICT) |
| patient_id / practice_id / location_id | text | not null | |
| card_number / card_number_hash / card_number_last4 | text | not null | hash is uniquely indexed (pgcrypto) |
| original_amount_cents / current_balance_cents | integer | not null | CHECK balance ≤ original, ≥ 0 |
| status | text | not null | active / exhausted / voided / refunded |
gift_card_ledger_entries— immutable balance movementsAPPEND-ONLY◆ gift_card_id
| Column | Type | Null | Notes |
|---|
| ★ id · ◆ gift_card_id | uuid | not null | → gift_cards (RESTRICT) |
| event_kind | text | not null | issue / redemption / reload / adjustment / void / refund |
| amount_delta_cents / balance_after_cents | integer | not null | sign rules enforced by CHECK |
| source_kind / source_id / actor_user_id | text | mixed | checkout / invoice / payment / manual / system |
| idempotency_key | text | null | partial-unique (double-apply guard) |
gift_card_definition_locations— per-location availability & taxability◆ gift_card_definition_id
| Column | Type | Null | Notes |
|---|
| ★ id · ◆ gift_card_definition_id | uuid | not null | → gift_card_definitions (CASCADE) |
| practice_location_id | text | not null | unique (definition, location) |
| is_active / taxable | boolean | not null | |
Loyalty
3 tables
A points-based loyalty program (default program key colorscience), its per-patient profiles, point adjustments and eligible products.
loyalty_profiles— per-patient loyalty membershipUNIQUE (practice, patient, program)
| Column | Type | Null | Notes |
|---|
| ★ id | uuid | not null | |
| practice_id / patient_id | text | not null | |
| program_key | text | not null | default colorscience |
| status / points_balance | text / integer | not null | registered / inactive · balance ≥ 0 |
| email / phone / postal_code / birth_month / birth_day | text / int | null | enrollment profile; month 1–12, day 1–31 |
| consented_at / registered_at | timestamptz | mixed | |
loyalty_point_adjustments— point earn/burn history◆ loyalty_profile_id
| Column | Type | Null | Notes |
|---|
| ★ id · ◆ loyalty_profile_id | uuid | not null | → loyalty_profiles (CASCADE) |
| points_delta / points_balance_after | integer | not null | delta ≠ 0, balance ≥ 0 |
| source_type / source_id | text | null | partial-unique per (profile, source) — idempotent |
| eligible_amount_cents | integer | null | |
loyalty_program_products— products that earn pointsUNIQUE (program, sku)
| Column | Type | Null | Notes |
|---|
| ★ id | uuid | not null | |
| program_key / name / sku | text | not null | sku non-empty |
| upc | text | null | CHECK digits only |
Packages
6 tables
Sellable service/product bundles: definitions, their line items and per-location prices, plus
the per-patient instances, per-item balances and an append-only package ledger.
package_definitions— sellable package templatesPK id
| Column | Type | Null | Notes |
|---|
| ★ id | uuid | not null | |
| practice_id / name | text | not null | unique (practice, lower(name)) |
| price_cents / currency_code | integer / char(3) | not null | price ≥ 0 |
| starts_at / expires_after_days | timestamptz / int | null | expiry > 0 if set |
package_items— what a package contains◆ package_definition_id
| Column | Type | Null | Notes |
|---|
| ★ id · ◆ package_definition_id | uuid | not null | → package_definitions (CASCADE) |
| catalog_item_kind / catalog_item_id / catalog_item_name | text | mixed | kind ∈ (product, service) |
| quantity | integer | not null | CHECK > 0; unique per (def, kind, item) |
package_definition_location_prices— per-location price overrides◆ package_definition_id
| Column | Type | Null | Notes |
|---|
| ★ id · ◆ package_definition_id | uuid | not null | → package_definitions (CASCADE) |
| practice_location_id / price_cents | text / int | not null | unique (def, location); price ≥ 0 |
patient_package_instances— a package a patient bought◆ package_definition_id
| Column | Type | Null | Notes |
|---|
| ★ id · ◆ package_definition_id | uuid | not null | → package_definitions (RESTRICT) |
| practice_id / patient_id / location_id | text | mixed | |
| source_checkout_id / source_invoice_id / source_payment_id | text | null | purchase provenance |
| purchased_at / expires_at / status | timestamptz / text | mixed | active / exhausted / expired / voided / refunded |
| price_paid_cents | integer | not null | ≥ 0 |
patient_package_item_balances— remaining redemptions per item◆ instance, item
| Column | Type | Null | Notes |
|---|
| ★ id | uuid | not null | |
| ◆ patient_package_instance_id | uuid | not null | → patient_package_instances (CASCADE) |
| ◆ package_item_id | uuid | not null | → package_items (RESTRICT) |
| original_quantity / remaining_quantity | integer | not null | CHECK remaining ≤ original, ≥ 0 |
package_ledger_entries— immutable package movementsAPPEND-ONLY4 FKs
| Column | Type | Null | Notes |
|---|
| ★ id | uuid | not null | |
| ◆ patient_package_instance_id | uuid | not null | → patient_package_instances (RESTRICT) |
| ◆ patient_package_item_balance_id | uuid | null | → patient_package_item_balances (RESTRICT) |
| ◆ package_definition_id · ◆ package_item_id | uuid | mixed | → package_definitions / package_items (RESTRICT) |
| event_kind / quantity_delta / remaining_quantity_after | text / int | mixed | purchase / redemption / adjustment / void / refund / expiration |
| idempotency_key | text | null | partial-unique |
Payments
2 tables
Native card payments through the FluidPay gateway: per-practice/location gateway configs
and the transaction log. Card data is tokenized — only card_token method
is allowed and no PAN is stored.
payment_gateway_configs— gateway credentials/config (secret by reference)PK id
| Column | Type | Null | Notes |
|---|
| ★ id | uuid | not null | |
| practice_id / location_id | text | mixed | global- & location-scoped partial uniques |
| gateway / gateway_environment | text | not null | gateway = fluidpay; env sandbox / production |
| public_key | text | null | CHECK begins with pub_ |
| private_api_key_secret_id | text | null | reference to a secret — not the key itself |
| processor_id / api_base_url | text | null | |
native_payment_transactions— payment attempts & results◆ payment_gateway_config_id
| Column | Type | Null | Notes |
|---|
| ★ id | uuid | not null | |
| ◆ payment_gateway_config_id | uuid | not null | → payment_gateway_configs (RESTRICT) |
| practice_id / location_id / patient_id / invoice_id / checkout_id | text | mixed | order context |
| payment_method_type | text | not null | CHECK = card_token (no raw PAN) |
| amount_cents / currency_code | integer / char(3) | not null | amount > 0 |
| status | text | not null | pending / approved / declined / gateway_declined / processor_error / gateway_error |
| idempotency_key / gateway_idempotency_key | text / uuid | mixed | partial-unique per (practice, key) |
| gateway_transaction_id / gateway_response* / processor_id | text / int | null | raw gateway result |
Promotions
3 tables
Discount definitions, the catalog items they target, and an append-only record of every
time a promotion was applied to a cart / invoice.
promotion_definitions— discount rulesPK id
| Column | Type | Null | Notes |
|---|
| ★ id | uuid | not null | |
| practice_id / name | text | not null | unique (practice, lower(name)) |
| discount_type | text | not null | percent | fixed_amount (exactly one of the two amounts set) |
| discount_percent / discount_amount_cents | numeric / int | null | percent 0–100 |
| starts_at / ends_at | timestamptz | null | CHECK ends > starts |
promotion_linked_items— items a promotion applies to◆ promotion_definition_id
| Column | Type | Null | Notes |
|---|
| ★ id · ◆ promotion_definition_id | uuid | not null | → promotion_definitions (CASCADE) |
| catalog_item_kind / catalog_item_id | text | not null | unique per (def, kind, item) |
promotion_applications— every applied discountAPPEND-ONLYUNIQUE idempotency_key
| Column | Type | Null | Notes |
|---|
| ★ id · ◆ promotion_definition_id | uuid | not null | → promotion_definitions (RESTRICT) |
| practice_id / patient_id / location_id | text | mixed | |
| source_kind / source_invoice_id / source_checkout_id | text | mixed | checkout / invoice / payment / manual / system |
| discount_cents | integer | not null | CHECK > 0 |
| promotion_snapshot / cart_context | jsonb | not null | frozen quote context |
| idempotency_key | text | not null | globally UNIQUE |
Patient Portal
3 tables
Patient-facing portal accounts, the providers each account is linked to, and patient file uploads (S3/R2).
patient_portal_accounts— portal logins (bridged to legacy patient/user)UNIQUE legacy_patient_id
| Column | Type | Null | Notes |
|---|
| ★ id | uuid | not null | |
| legacy_patient_id / legacy_user_id | text | not null | patient_id is unique |
| login_identifier_hash | text | null | hashed login (pgcrypto), partial-unique |
| status / last_login_at | text / timestamptz | mixed | active / disabled |
patient_portal_provider_links— providers visible to an account◆ account_id
| Column | Type | Null | Notes |
|---|
| ★ id · ◆ account_id | uuid | not null | → patient_portal_accounts (CASCADE) |
| legacy_provider_id / provider_name / provider_url_suffix | text | mixed | unique (account, provider) |
| is_active / last_verified_at | boolean / timestamptz | not null | |
patient_files— uploaded documents / photos / mediaUNIQUE (provider, object_key)
| Column | Type | Null | Notes |
|---|
| ★ id | uuid | not null | |
| practice_id / patient_id / category | text | not null | category ∈ documents / photos / media |
| file_name / content_type / size_bytes | text / bigint | mixed | |
| storage_provider / object_key | text | not null | s3 / r2; object_key unique per provider |
| upload_status / completed_at / deleted_at | text / timestamptz | mixed | pending / uploaded; soft-delete |
Notifications
2 tables
A cross-system notification feed (ingesting from legacy HPT and native Revique) with per-user read/dismiss/action state.
notifications— notification feed itemsUNIQUE (practice, system, type, entity)
| Column | Type | Null | Notes |
|---|
| ★ id | uuid | not null | |
| practice_id / location_id | text | mixed | |
| source_system / source_type / source_entity_id | text | not null | system ∈ legacy_hpt / revique; type ∈ patient_message / lead_message / form_submission / alert |
| title / preview / body / priority | text | mixed | priority low / normal / high / urgent |
| target / metadata | jsonb | not null | routing / audience |
notification_user_states— per-user read/dismiss/action◆ notification_id
| Column | Type | Null | Notes |
|---|
| ★ id · ◆ notification_id | uuid | not null | → notifications (CASCADE) |
| user_id | text | not null | unique (notification, user) |
| read_at / dismissed_at / actioned_at | timestamptz | null | indexed for unread queries |
RBAC (Access Control)
8 tables
The largest domain — a full role-based permission model with per-location scoping, role emulation
and an audit trail. See the RBAC deep-dive for how these fit together.
rbac_users— platform usersUNIQUE (practice, normalized_user_name)
| Column | Type | Null | Notes |
|---|
| ★ id | uuid | not null | |
| practice_id / user_name / normalized_user_name | text | not null | |
| legacy_user_id / user_type | text / int | null | HPT bridge |
| first_name / last_name / email / phone / is_active | text / bool | mixed | |
rbac_roles— roles per practiceUNIQUE (practice, normalized_name)
| Column | Type | Null | Notes |
|---|
| ★ id | uuid | not null | |
| practice_id / name / normalized_name | text | not null | CHECK normalized_name ≠ 'master' |
| is_system / is_active | boolean | not null | system roles protected |
rbac_feature_catalog— catalog of features & allowed actionsPK feature_key
| Column | Type | Null | Notes |
|---|
| ★ feature_key | text | not null | natural PK (no uuid) |
| module_key / name / description | text | mixed | grouped by module |
| actions | text[] | not null | subset of view/create/edit/delete/export/manage/assign/refund |
| sort_order / is_active | int / bool | not null | |
rbac_role_permissions— which (feature, action) a role grantsPK (role, feature, action)
| Column | Type | Null | Notes |
|---|
| ★ ◆ role_id | uuid | not null | → rbac_roles (CASCADE) |
| ★ ◆ feature_key | text | not null | → rbac_feature_catalog |
| ★ action | text | not null | CHECK ∈ 8 verbs |
rbac_user_roles— user ⇄ role assignmentsPK (user, role)
| Column | Type | Null | Notes |
|---|
| ★ ◆ user_id | uuid | not null | → rbac_users (CASCADE) |
| ★ ◆ role_id | uuid | not null | → rbac_roles (CASCADE) |
rbac_user_locations— which locations a user is scoped toPK (user, location)
| Column | Type | Null | Notes |
|---|
| ★ ◆ user_id | uuid | not null | → rbac_users (CASCADE) |
| ★ practice_location_id | text | not null | per-location access scoping |
| is_active | boolean | not null | |
rbac_role_emulation_grants— temporary "act as role / super-admin" tokensUNIQUE token_hash
| Column | Type | Null | Notes |
|---|
| ★ id | uuid | not null | |
| ◆ role_id | uuid | null | → rbac_roles (CASCADE); NULL for super_admin |
| token_hash | text | not null | hashed (pgcrypto), globally unique |
| emulation_kind | text | not null | role (role_id set) | super_admin (role_id NULL) |
| granted_by_user_id / expires_at | text / timestamptz | mixed | CHECK expires_at > created_at |
rbac_audit_events— access-control audit trailPK id
| Column | Type | Null | Notes |
|---|
| ★ id | uuid | not null | |
| practice_id / actor_user_id / actor_user_name | text | mixed | who did it |
| action / target_type / target_id | text | mixed | what & on which object |
| metadata | jsonb | not null | indexed by (practice, created_at) |
System
1 table
schema_migrations— applied migration ledgerPK id
| Column | Type | Null | Notes |
|---|
| ★ id | text | not null | migration identifier |
| name / applied_at | text / timestamptz | not null | tracks Codex migrations |
RBAC is the most detailed domain in the schema. It implements a classic users → roles →
permissions model, extended with per-location scoping, time-boxed role emulation
and a dedicated audit trail. Everything is tenant-scoped by practice_id.
The permission chain
rbac_users ──(rbac_user_roles)──▶ rbac_roles ──(rbac_role_permissions)──▶ (feature_key, action)
│ │
└──(rbac_user_locations)──▶ practice_location_id rbac_feature_catalog defines the
(which sites the user may act in) valid features & their allowed actions
1 · Users & roles
rbac_users holds each platform user (bridged to the
legacy user via legacy_user_id). rbac_roles
defines named roles per practice. The join table rbac_user_roles
(composite PK) grants a user one or more roles. A user with no role has no permissions.
2 · Permissions via a feature catalog
rbac_feature_catalog is the source of truth for what
can be permissioned: each feature declares an actions text[]
drawn from view, create, edit, delete, export, manage, assign, refund.
rbac_role_permissions then grants a role a specific
(feature_key, action) pair — its composite PK makes each grant unique,
and the FK to the catalog stops invalid feature keys.
3 · Per-location scoping
rbac_user_locations (composite PK of user +
practice_location_id) restricts a user to specific sites. Roles say
what you can do; locations say where. This lets a "Front Desk" role at
Location A be entirely separate from the same role at Location B.
4 · Role emulation
rbac_role_emulation_grants issues a time-boxed,
hashed token that lets an authorized user temporarily "act as" another role — or, when
emulation_kind = super_admin (with role_id NULL),
as a super-admin. A CHECK enforces that expires_at > created_at, and
only the token_hash is stored (never the raw token). Ideal for
support/impersonation with a built-in expiry.
5 · Audit. rbac_audit_events records who
(actor_user_id/name) did what (action) to which
object (target_type/target_id), with a JSON
metadata blob and a (practice_id, created_at DESC)
index for fast per-tenant timelines. Combined with emulation grants, every "act as" is traceable.
Design note. A CHECK on rbac_roles forbids the
normalized name 'master', and is_system marks
protected built-in roles — guardrails so the reserved super-role can't be spoofed by a
practice-created role.
All 27 foreign keys, grouped by domain. CASCADE = children are
removed with their parent; RESTRICT = the parent can't be deleted
while children exist (used to protect ledgers & financial history).
| Child column | | References | On delete |
|---|
| catalog_item_location_pricing.catalog_item_id | → | catalog_items.id | CASCADE |
| catalog_item_lots.catalog_item_id | → | catalog_items.id | CASCADE |
| catalog_item_provider_mappings.catalog_item_id | → | catalog_items.id | CASCADE |
| gift_card_definition_locations.gift_card_definition_id | → | gift_card_definitions.id | CASCADE |
| gift_cards.gift_card_definition_id | → | gift_card_definitions.id | RESTRICT |
| gift_card_ledger_entries.gift_card_id | → | gift_cards.id | RESTRICT |
| loyalty_point_adjustments.loyalty_profile_id | → | loyalty_profiles.id | CASCADE |
| native_payment_transactions.payment_gateway_config_id | → | payment_gateway_configs.id | RESTRICT |
| notification_user_states.notification_id | → | notifications.id | CASCADE |
| package_definition_location_prices.package_definition_id | → | package_definitions.id | CASCADE |
| package_items.package_definition_id | → | package_definitions.id | CASCADE |
| package_ledger_entries.package_definition_id | → | package_definitions.id | RESTRICT |
| package_ledger_entries.package_item_id | → | package_items.id | RESTRICT |
| package_ledger_entries.patient_package_instance_id | → | patient_package_instances.id | RESTRICT |
| package_ledger_entries.patient_package_item_balance_id | → | patient_package_item_balances.id | RESTRICT |
| patient_package_instances.package_definition_id | → | package_definitions.id | RESTRICT |
| patient_package_item_balances.package_item_id | → | package_items.id | RESTRICT |
| patient_package_item_balances.patient_package_instance_id | → | patient_package_instances.id | CASCADE |
| patient_portal_provider_links.account_id | → | patient_portal_accounts.id | CASCADE |
| promotion_applications.promotion_definition_id | → | promotion_definitions.id | RESTRICT |
| promotion_linked_items.promotion_definition_id | → | promotion_definitions.id | CASCADE |
| rbac_role_emulation_grants.role_id | → | rbac_roles.id | CASCADE |
| rbac_role_permissions.feature_key | → | rbac_feature_catalog.feature_key | NO ACTION |
| rbac_role_permissions.role_id | → | rbac_roles.id | CASCADE |
| rbac_user_locations.user_id | → | rbac_users.id | CASCADE |
| rbac_user_roles.role_id | → | rbac_roles.id | CASCADE |
| rbac_user_roles.user_id | → | rbac_users.id | CASCADE |
Pattern: definition/config parents CASCADE to their pure children
(locations, items, links), but anything that touches money or history — issued gift cards,
package instances, all three ledgers, payment configs — is RESTRICT, so financial records
can never be silently deleted by removing a parent.
set_updated_at()
Trigger function — sets NEW.updated_at = now() on
every row UPDATE. Wired as a BEFORE UPDATE trigger on ~24 tables so
updated_at is always accurate without application code.
prevent_gift_card_ledger_mutation()
Raises 'gift_card_ledger_entries is append-only'.
Attached as BEFORE UPDATE and BEFORE DELETE
triggers, making the gift-card ledger strictly insert-only.
prevent_package_ledger_mutation()
Raises 'package_ledger_entries is append-only'.
Same UPDATE/DELETE guard for the package ledger.
prevent_promotion_application_mutation()
Raises 'promotion_applications is append-only'.
Locks the promotion-application log against edits/deletes.
Extension — pgcrypto. Enabled at the database level
(CREATE EXTENSION pgcrypto). Supplies
gen_random_uuid() for every UUID primary key, and cryptographic hashing
for sensitive values stored as *_hash columns (gift-card numbers, portal
login identifiers, role-emulation tokens) so raw secrets are never persisted.
Why append-only ledgers? Money and entitlement movements
(gift-card balances, package redemptions, applied promotions) must be auditable and
irreversible. Instead of updating a balance in place, the app inserts a signed delta row and
the trigger forbids editing history — corrections are new compensating entries, not edits.
Open items aligned with what leadership asked for — captured here as a working checklist to review
and document as the native backend hardens toward production.
Logging & events
- Confirm application & DB logs (slow queries, errors) ship to CloudWatch / central logging.
- Verify
rbac_audit_events is written on every privileged action.
- Retention policy for the append-only ledgers & audit trail.
Scaling & availability
- Enable / tune Aurora auto-scaling (read replicas, ACU limits for Serverless v2).
- Confirm Multi-AZ & automated backup / PITR windows.
- Right-size the bastion and document its lifecycle.
Monitoring & alerting
- CloudWatch alarms: CPU, connections, replica lag, storage.
- Enable Performance Insights / Enhanced Monitoring.
- Alert on failed payment transactions & gateway errors.
Security & access
- Rotate the Secrets Manager DB credential; scope least-privilege DB roles.
- Restrict SSM start-session to a named IAM group; log all sessions.
- Confirm encryption at rest (KMS) & TLS in transit are enforced.
Do-not-publish reminder. The raw
revique-native-backend-schema.sql dump contains unmasked identifiers and
must be kept local — exclude it from anything uploaded with this shared site.