POSTGRESQL DESIGN

PostgreSQL and SQLite Schema Design, Las Vegas NV

PostgreSQL and SQLite Schema Design, Las Vegas NV — Las Vegas NV
PostgreSQL and SQLite Schema Design, Las Vegas NV

Relational design for clinical data: constraints as controls, migration discipline, indexing that survives volume, and the audit columns nobody adds.

Constraints are controls

A foreign key is an integrity control; removing it to move faster converts a database error into a data-quality incident.

In practice this is where most programmes lose time: the decision is either never made explicitly or made once and never revisited. Naming the owner, the artefact and the review date converts it from an opinion into a control that can be tested.

Test it deliberately before it matters. A control that has never been exercised under adversarial conditions is a hypothesis, and in a regulated estate the difference between the two is discovered at the worst possible moment.

Migrations as releases

Every schema change is a release with a rollback, tested against a real dataset rather than an empty one.

In practice this is where most programmes lose time: the decision is either never made explicitly or made once and never revisited. Naming the owner, the artefact and the review date converts it from an opinion into a control that can be tested.

Test it deliberately before it matters. A control that has never been exercised under adversarial conditions is a hypothesis, and in a regulated estate the difference between the two is discovered at the worst possible moment.

Indexing by query, not by column

Indexes follow the queries you run; a column index nobody queries is write cost with no read benefit.

In practice this is where most programmes lose time: the decision is either never made explicitly or made once and never revisited. Naming the owner, the artefact and the review date converts it from an opinion into a control that can be tested.

Test it deliberately before it matters. A control that has never been exercised under adversarial conditions is a hypothesis, and in a regulated estate the difference between the two is discovered at the worst possible moment.

Volume changes the plan

A query that is instant on ten thousand rows is a different query on ten million, and the plan changes before the timing does.

In practice this is where most programmes lose time: the decision is either never made explicitly or made once and never revisited. Naming the owner, the artefact and the review date converts it from an opinion into a control that can be tested.

Test it deliberately before it matters. A control that has never been exercised under adversarial conditions is a hypothesis, and in a regulated estate the difference between the two is discovered at the worst possible moment.

Audit columns

Who wrote this, when, from where, and under what authority; retrofitting them means the history before the retrofit does not exist.

In practice this is where most programmes lose time: the decision is either never made explicitly or made once and never revisited. Naming the owner, the artefact and the review date converts it from an opinion into a control that can be tested.

Test it deliberately before it matters. A control that has never been exercised under adversarial conditions is a hypothesis, and in a regulated estate the difference between the two is discovered at the worst possible moment.

Backups and point-in-time recovery

A backup is a file; recovery is a capability, and only one of them has been tested.

In practice this is where most programmes lose time: the decision is either never made explicitly or made once and never revisited. Naming the owner, the artefact and the review date converts it from an opinion into a control that can be tested.

Test it deliberately before it matters. A control that has never been exercised under adversarial conditions is a hypothesis, and in a regulated estate the difference between the two is discovered at the worst possible moment.

SQLite where it belongs

A single-writer engine is excellent for embedded and moderate-concurrency workloads and wrong for a busy clinical API.

In practice this is where most programmes lose time: the decision is either never made explicitly or made once and never revisited. Naming the owner, the artefact and the review date converts it from an opinion into a control that can be tested.

Test it deliberately before it matters. A control that has never been exercised under adversarial conditions is a hypothesis, and in a regulated estate the difference between the two is discovered at the worst possible moment.

Retention and deletion

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

In practice this is where most programmes lose time: the decision is either never made explicitly or made once and never revisited. Naming the owner, the artefact and the review date converts it from an opinion into a control that can be tested.

Test it deliberately before it matters. A control that has never been exercised under adversarial conditions is a hypothesis, and in a regulated estate the difference between the two is discovered at the worst possible moment.

Constraints are the cheapest control you own

A foreign key, a unique constraint, a not-null with a default — each converts a class of application bug into an error the database refuses at write time. Removing them to move faster converts a database error into a data-quality incident discovered months later by a clinician.

The audit columns are the other constraint: who wrote this row, when, from where and under what authority. Retrofitting them means the history before the retrofit does not exist, and that is exactly the history an investigation wants.

Migrations are releases. Every schema change needs a rollback, a plan for the data already in the column, and a test against a real dataset rather than an empty one — because the failure mode is never the empty path.

Volume changes the plan before the timing

A query that is instant on ten thousand rows is a different query on ten million. The planner changes strategy, the index that helped stops helping, and the change is invisible until the table grows past a threshold nobody documented.

Index by query rather than by column: an index nobody queries is write cost with no read benefit, and a column that appears in a where clause is not automatically a good index candidate.

SQLite is excellent for embedded and moderate-concurrency workloads and wrong for a busy clinical API, because the single-writer model becomes the bottleneck before the read volume does. Knowing which one you are building is a design decision, not an implementation detail.

Point-in-time recovery is the capability, not the backup

A nightly dump restores to the last night. Point-in-time recovery restores to the minute before the bad migration, which is the moment you actually want. The difference is write-ahead log archiving, configured before the incident rather than after.

Test the recovery into a different environment and read a row. A restore that completes and produces a database that will not start is a control that reported success.

And design deletion before you need it: patient data that must be removed has to be removable without cascading through data that must be retained, and that boundary is a schema decision rather than a script written under pressure.

Queries that will hurt at volume

Unbounded selects on a growing table, joins without an index on the joining column, and any pattern that fetches rows and filters in the application. Each is invisible at ten thousand rows and obvious at ten million.

Look at the plan, not the timing. A sequential scan that finishes in milliseconds today is the same plan that finishes in seconds next year, and the plan tells you before the stopwatch does.

Put a limit on anything a user can trigger. A page that can request a hundred thousand rows is a denial-of-service request waiting for an impatient click.

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. Las Vegas, Nevada 89101.

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 postgresql and sqlite schema design, las vegas nv — the scope, the failure modes it prevents and how the work is measured. It sits in the Cloud and Operations 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

Available for architecture review, conformance work and fractional leadership. Standard rate $195 per hour, committed $165 per hour at twenty hours or more per week for three months or more.

Talk to Charles

702-559-3489 · charles@trinh.io

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
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

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.