337-table store · constraints · migrations

Charles Trinh — Clinical Data Store Design, Las Vegas NV

Charles Trinh — Clinical Data Store Design, Las Vegas NV
Charles Trinh — Clinical Data Store Design, Las Vegas NV

Schema design for clinical records at scale: constraints that prevent bad data, audit columns that survive review, and migrations that can be rolled back.

Constraints are the cheapest control you own

A foreign key, a check constraint and a not-null are enforced on every write, forever, without anyone remembering to check.

Teams remove constraints to move faster and convert database errors into data-quality incidents that surface months later in a clinical report.

Design the constraints first and let the application fight them; the fight is the value.

Audit columns that survive review

Who wrote this, when, from where and under what authority; add all four to every table that holds patient data before the first row exists.

Retrofitting audit columns leaves everything before the retrofit unexplained, which is exactly the period an investigation covers.

Keep the writer identity distinct from the application identity, or the trail names software instead of people.

Modelling identity and encounter

Separate the person from the identifier from the encounter; collapsing them produces the merge and unmerge problems that destroy clinical history.

Identity resolution belongs in its own tables with merge history, not in a column on the patient row.

Effective-dated attributes let you answer what was true at the time, which is the only question that matters in a dispute.

Migrations and reversibility

Every schema change is a release with a forward and a reverse, tested against a realistic dataset rather than an empty one.

Run migrations in a transaction where the engine allows it, and where it does not, design for resumability.

A migration that cannot be rolled back should require a different approval level than one that can.

Indexing and query plans

Indexes follow queries; a column index nobody queries costs writes and buys nothing.

Read the plan for the ten queries that matter and check them again when the table crosses a million rows, because the planner changes its mind.

Partition by time where retention is time-based, so deletion is a partition drop rather than a delete storm.

Retention and lawful deletion

Deleting patient data is a design problem; cascades that surprise you are a compliance problem.

Model retention per record class explicitly and test the deletion path the same way you test retrieval.

Keep the audit of what was deleted, because the question after a request is always what was removed and when.

How I do it

One trace of a real case with timestamps at every hop, before any document is written.

A findings list naming the defect, the system, the owner and the date, reviewed monthly.

No attestation I did not test, and no template sold as an assessment.

Naming and provenance

Every table that holds patient data needs a name that says what it holds, a comment that says who writes it, and a column that says where the row came from.

Provenance columns cost storage and save investigations, because the first question about an anomalous row is always which system produced it.

Agree the convention once and enforce it at review, because a schema without conventions becomes unreadable at about forty tables.

Transactional boundaries

Decide what must be atomic and make the database enforce it, rather than relying on application code to keep two writes together.

Where the boundary spans systems, use an outbox table and a reconciler rather than a distributed transaction, because the failure mode is easier to reason about.

Record the reconciler's lag, since a growing lag is the earliest signal that a boundary has broken.

Testing the schema

Load a dataset that looks like production, including the awkward cases: hyphenated names, missing addresses, revoked consent, patients with two identifiers.

Run the migration and the rollback against it, and check the queries afterwards rather than only the row counts.

A migration tested on empty tables has tested the parts that never fail.

Documentation that stays true

Generate the schema documentation from the catalog rather than writing it by hand, so it cannot disagree with the database.

Keep the retention rules in the same document as the tables they apply to, because a retention rule in a separate policy is a rule nobody applies.

Review both quarterly against what the application actually writes.

The read model is part of the design

Clinical queries are rarely single-table lookups; they combine a patient, an encounter, a consent state and a set of observations, and the naive join path degrades as volume grows. Designing a read model deliberately, updated from the write path, keeps the clinical screens fast without denormalising the records that need integrity. The trade-off is visible lag, which is acceptable for a chart summary and not acceptable for a medication interaction check.

Where the same data must be both fast and current, separate the tables that change slowly from the ones that change constantly, and only cache the former. Most clinical read paths are dominated by a small number of stable objects, which makes a targeted cache effective and a broad one unnecessary. Measure the query plans before choosing, because intuition about which part is slow is wrong often enough to make the measurement mandatory.

Finding the data that should not exist

Every clinical store accumulates tables nobody can explain: a dump from a migration, an interface's staging area, a reporting extract that became permanent. Each holds a copy of patient data under weaker controls than the primary store and is invisible to the retention policy. The exercise that finds them is comparing the schema catalogue against the list of tables with a named owner, and the gap is the finding.

Once found, each table gets one of three fates: owned and documented, archived and deleted, or access-restricted with a date. Leaving them unowned is the default and the worst, because the copy exists, nobody maintains it, and the next security review will ask who is responsible. Doing this once a year is enough to keep the gap small; doing it after an incident is doing it under pressure.

Performance under real clinical load

Clinical load is bursty in a way generic benchmarks never reproduce: a clinic opens, forty patients check in within a few minutes, and the reads that tested fine at average load time out. Test with a generated load that matches the arrival pattern, not only the volume, and watch the connection pool rather than only the database CPU. The first symptom of pressure is usually pool exhaustion in the application, which looks like a database problem until someone reads both sets of metrics.

Capacity headroom should be stated as a ratio rather than a number, and reviewed when the patient population grows. A store running at sixty percent of the capacity it had eighteen months ago is due a conversation, and the conversation is cheaper before the clinic opens on a busy morning than after. Write the threshold down and alert on the trend rather than on the moment it becomes an incident.

Backup, restore and the testing gap

A backup job that reports success has reported that a file exists, not that the file restores. The control is a restore into a fresh environment followed by a query that proves the data is present and consistent, with the result recorded against a date. Clinical stores usually pass the backup half of this test and fail the restore half, and the failure is discovered during the incident that made it matter.

Restore drills should also answer the question of how long the organisation can be without the store, because that number drives the architecture rather than the other way around. A store that takes six hours to restore and a clinical operation that tolerates one hour has a design problem that no backup frequency solves. Writing both numbers down converts an uncomfortable truth into a funded project.

Proof

  • ONC (g)(10) self-test PASS, 317 of 317 subtests, April 2026 — public suite URL
  • 67 public hostnames, 51 serving, one AWS account
  • 104 clearinghouse enrolments across 53 jurisdictions
  • USCDI v7 proposal — Patient Digital Identity Credential — listed at isp.healthit.gov
  • SAM.gov UEI MVKQW9DKKGB8 · CAGE 20KM5 · NPI 1194645564
  • AWS Business Associate Addendum active since 18 August 2026

Where this work is delivered

Engagements run from Las Vegas and reach across the valley: clients in Las Vegas, NV; Las Vegas, Nevada; Henderson; Clark County, Nevada; and North Las Vegas, Nevada — plus remote delivery nationwide.

How an engagement runs

1. Trace one real case

Follow a single patient or claim end to end with a timestamp at every hop, before any document is written.

2. Written findings

A findings list naming the defect, the system, the owner and the date.

3. Monthly review

Review the list monthly and check whether the dates held.

Frequently asked

What is this service?

This page describes charles trinh — clinical data store design, las vegas nv — the scope, the failure modes it prevents and how the work is measured. It sits in the Data and Ledgers category at trinh.io.

Where is the work delivered?

Engagements run from Las Vegas, Nevada across the valley — Las Vegas, Henderson, North Las Vegas, Spring Valley, Paradise, Winchester, Sunrise Manor, Enterprise, Summerlin, Clark County — and remotely anywhere in the United States. On-site work in the Las Vegas valley, remote delivery elsewhere.

What does it cost?

Standard rate $195 per hour, committed $165 per hour at twenty hours or more per week for three months or more; $1,600 standard day, $1,350 committed day. Scoped in writing before work starts, fixed fee where the deliverable is defined.

Who is Charles Trinh?

A technology architect in Las Vegas, Nevada, working in healthcare identity, consent and interoperability. Not a physician, not a medical practice, and not affiliated with any other Charles Trinh.

How does an engagement start?

With one trace of a real case and a written findings list naming the defect, the system, the owner and the date. The first call is twenty minutes and free: 702-559-3489.

Engagement

Scoped in writing before work starts; fixed fee where the deliverable is defined. First call is 20 minutes and free. 702-559-3489 · charles@trinh.io

Talk to Charles

Record

Profile

Healthcare technology architect working where federal regulation becomes production infrastructure. Built and operates a FHIR R4 clinical platform — SMART App Launch authorization server, patient and provider portals, SQLite — on a hardened single-host AWS estate of 67 public hostnames. The platform returned a self-test PASS on the ONC (g)(10) Standardized API test suite in April 2026, 317 of 317 subtests, publicly verifiable. Designed a consent architecture in which a permissioned ledger records patient grant, refusal and revocation as immutable events while all clinical data stays off-chain and encrypted per patient — no PHI on the ledger by design. Filed a proposed USCDI v7 data element with ONC on verifiable patient identity. Translates the Federal Register, HHS/CMS guidance and HITRUST requirements into architecture, controls and audit evidence.

Selected engineering scale

  • Self-managed estate of 67 public hostnames, 51 serving, on one AWS account.
  • FHIR R4 API with a SMART App Launch authorization server; patient and provider portals; an AI service layer; Hyperledger Fabric peer, orderer and CA; IPFS; a 337-table SQLite clinical store.
  • Two permissioned distributed ledgers in production, one operating a consent registry that records grant, refusal and revocation with no PHI on chain.
  • X12 005010 pipelines across eight transaction sets.
  • Designed, built, deployed and operated end to end by one person.

Credentials and standards work

  • Inferno (g)(10) self-test PASS, 317 of 317 subtests, April 2026 — the ONC Standardized API test suite. Standards validated: US Core 6.1.0, USCDI v3, SMART App Launch 2.0.0, Bulk Data 2.0.0. Public suite URL.
  • USCDI v7 public comment submission, April 2026 — proposed Patient Digital Identity Credential under the Provenance data class, listed publicly on the ONC Interoperability Standards Platform.
  • SAM.gov UEI MVKQW9DKKGB8 · CAGE 20KM5 · NPI 1194645564. AWS Business Associate Addendum active 18 August 2026.

Technical environment

AWS us-east-1 (compute, storage, messaging, SMS, email, voice) · Linux and systemd hardening · nginx · Fabric and EVM ledgers · FHIR R4 · X12 005010 · HL7 · SQLite · self-hosted mail, drive, search and monitoring · Git-based change control and CI release gates.

TRINH — Charles Trinh, technology architect
TRINH

Charles Trinh

Fractional CTO/CIO and Technology Architect — Healthcare Data, Identity & Regulated Infrastructure · Las Vegas, NV

Las Vegas, Nevada · US citizen · Fractional and project engagements

(702) 559-3489 Email Charles Trinh