Soltron CRM — how we build it

Scope & guiding principles

Build a bespoke clinic-management system the front desk lives in, without touching the Medesync medical record — architected so the lean first release grows into the full product without rework.

The two scopes, held at once

The client team agreed to keep Release 1 lean: the booking calendar, with patient records staying in Medesync. But we design the schema and services for the full system (patients, billing, packages, reports, leads) from day one, so nothing has to be re-architected later. The difference between R1 and later is which UI ships, not which foundation exists.

Non-negotiable principles

• The database enforces the invariants — one patient per chamber (incl. cleaning buffer) is a Postgres constraint, not application code, so no path (staff, AI, web, a future script) can violate it.
• One write path. Every booking mutation goes through a single validated service; the AI has the same rules and fewer privileges than staff.
• Patient data stays in Canada, encrypted, access-controlled, audited — Law 25 / PIPEDA by design, not bolted on.
• Server is the source of truth. Client validation is for UX; the server re-validates and re-authorizes everything.
• Everything is tested — the booking engine, the API, and the UI (we already have the headless harness).

Architecture overview

A single Next.js application (UI + API in one deployable) on top of PostgreSQL, self-hosted in a Canadian region, with a thin real-time channel for the live board and a signed endpoint for the AI receptionist.

For a single clinic (tens of staff, hundreds of bookings/day, thousands of patients) a modular monolith is the correct choice — not microservices. It is simpler to secure, cheaper to run, easier to reason about, and fast enough by an order of magnitude. We keep clean module boundaries (booking, patients, billing, packages, reports, auth) so pieces could be extracted later, but we do not pay the distributed-systems tax now.

                         ┌──────────────────────────────────────────────┐
   Staff browsers  ───►  │  nginx (TLS 1.3, HSTS, security headers,      │
   (desktop/tablet)      │        rate-limit)  — Canadian region        │
                         └───────────────┬──────────────────────────────┘
                                         │
   Retell voice AI  ─── signed ─────►    │  ┌──────────────────────────┐
   (FR/EN, guardrailed)                  └─►│  Next.js app (TypeScript) │
                                            │  • React UI (the demo)    │
                                            │  • Route handlers / RPC   │
   Web widget (later) ── signed ──────────►│  • Server actions         │
                                            │  • Auth + RBAC middleware │
                                            │  • Booking service (1 path)│
                                            └───────┬─────────┬─────────┘
                                                    │         │  SSE (live board)
                                          Prisma ORM│         └──────────────► browsers
                                                    ▼
                                       ┌────────────────────────────┐
                                       │ PostgreSQL 16 (Canada)     │
                                       │ • EXCLUDE constraint       │
                                       │ • append-only audit trigger│
                                       │ • materialized report views│
                                       │ • encrypted at rest + PITR │
                                       └────────────────────────────┘
                                       Encrypted object storage (CA) — consent scans (later phase)
  

Environments: dev (local, Postgres 16 + seed), staging (mirror of prod, client UAT), production (Canadian region). Promotion is via GitLab CI; database migrations run automatically and are reversible.

Technology stack

Deliberately boring, proven choices — the scaffold already uses most of them, so R1 starts from a running codebase.

Data model & the capture layer

The foundation. Every field that a future report or the billing engine will need is present from R1, even where its UI ships later.

Core entities

* Scope note: whether consent scans + session notes live here (vs. Medesync) is the open patient-data decision. The schema supports both; UI ships per that call.

The appointment — capture fields (built R1, un-backfillable)

-- statuses: the full nine, so cancellation & no-show reports are ever possible
status        ENUM(requested, confirmed, checked_in, in_treatment, completed,
                   cancelled_by_client, cancelled_by_clinic, no_show, rescheduled)
booked_at     timestamptz   -- when the booking was CREATED (≠ start; powers lead-time/demand)
starts_at / ends_at   timestamptz   -- the actual appointment (UTC)
buffer_minutes int       -- cleaning time, attached to THIS booking, removable, excluded from reports
cancelled_at  timestamptz   -- to compute cancellation notice period
cancel_reason text
rescheduled_from_id  fk    -- trace a moved booking
booking_source ENUM(13 values)  -- "how did you hear about us" — Valerie's #1 report
booking_channel ENUM(voice_ai, web, phone, walk_in, staff)
-- patient.acquisition_source (first-touch) is SEPARATE from appointment.booking_source (per-booking)

The conflict rule — enforced by Postgres

CREATE EXTENSION btree_gist;
occupancy = tstzrange(starts_at, ends_at + (buffer_minutes||' min')::interval, '[)')  -- half-open
ALTER TABLE appointments ADD CONSTRAINT one_patient_per_chamber
  EXCLUDE USING gist (resource_id WITH =, occupancy WITH &&)
  WHERE (status IN ('requested','confirmed','checked_in','in_treatment','completed'));

Half-open ranges make back-to-back bookings legal (a buffer ending 10:45 and the next start at 10:45 don't overlap); the WHERE means cancelled/no-show free the slot instantly while the row stays for reporting.

Migrations

Prisma migrations for tables/columns; the EXCLUDE constraint, the audit trigger, and materialized views ship as raw-SQL migration steps. Every migration is reversible and runs in CI against a throwaway DB before prod.

Booking engine & concurrency

One pure function validates every booking; the database is the final arbiter; two writers can never take the same slot.

• Pure validator validateBooking() — occupancy overlap (per resource, incl. buffer, adjacency allowed), fit-inside-window (incl. trailing buffer), min/max duration + hard 3-hour ceiling, resource-type compatibility, active/ai-bookable. Runs in the browser for instant feedback and on the server as the authority.
• Transactional commit — open a transaction, re-validate against live rows, insert/update; the EXCLUDE constraint is the backstop. On a race the loser hits an exclusion violation → clean 409 CONFLICT.
• Concurrency guard — this is also the AI's guard: if two callers race for the last chamber, one commit fails and the AI re-offers. No application lock is required for correctness (the DB serializes), though a per-resource advisory lock gives cleaner queuing.
• DST correctness — the operating window is resolved from local Montreal wall-clock on the specific date via Luxon; all math is in UTC so a session never lands in a nonexistent hour.

API design & the single write path

Server actions and route handlers, all funnelling booking mutations through one service. Reads are typed queries; writes are validated, authorized, audited.

• Every input is parsed with a Zod schema at the boundary; invalid shapes are rejected before any logic.
• Every write goes through bookingService.commit() / the equivalent domain service — staff routes and the AI route both call it, so there is literally one code path.
• Idempotency — the AI confirm and any retry-prone write carry an Idempotency-Key; replays return the original result, never a duplicate.
• Errors are typed (VALIDATION / CONFLICT / FORBIDDEN / NOT_FOUND) with safe messages; stack traces never reach the client.

Authentication & roles

Four roles, enforced on the server for every action — never trusting the browser.

• Auth.js credentials with argon2id password hashing, database-backed sessions, httpOnly + SameSite=Lax + Secure cookies, short idle timeout, server-side logout/invalidation.
• Brute-force protection: per-account + per-IP rate limiting, exponential backoff, lockout, and (optional) TOTP MFA required for the Administrator role.
• RBAC: role in the session; a requireRole() guard on every route handler and server action; data queries are scoped so a Therapist can't read Billing, etc.

Security & Law 25 compliance

Defense-in-depth for a Quebec clinic handling patient-adjacent data — hardened at every layer and audited before go-live.

The layers

Law 25 / PIPEDA specifics

• Data residency: database, backups, files, and error tracking all in a Canadian region. No PII leaves the country.
• Data minimization: store only what the booking system needs; clinical records stay in Medesync (clear data-classification boundary, signed with the client).
• Retention & deletion: a retention schedule; soft-delete/anonymize rather than hard-delete where records must be kept; a documented process for access/erasure requests.
• Breach procedure: a written 72-hour notification runbook, incident logging, and a named privacy contact (Earl).
• Governance: a record of processing activities, a short privacy policy, and consent capture where required.

Performance & optimization

At clinic scale the system is inherently fast; we still design so it stays instant and never does needless work.

• Indexing: the GiST constraint doubles as the overlap index; plus (resource_id, starts_at) partial on active statuses, an expression index on (starts_at AT TIME ZONE 'America/Montreal') for day fetches, a GIN/trigram index for patient search, and invoice/patient lookups.
• The day fetch is bounded (one date, ~5 resources) → single fast query, no N+1 (Prisma include / a join).
• Reports read materialized views (busiest days, utilisation, source revenue…) refreshed nightly by a pg-boss job, so heavy aggregation never blocks the UI. Custom date ranges fall back to indexed queries.
• Caching: Next.js route/data caching for read-mostly config (services, hours); no Redis needed at this scale (added only if load ever demands it).
• Connection pooling: a bounded pool (PgBouncer if needed) so bursts don't exhaust connections.
• Frontend: code-split per route, minimal JS, tabular-nums, no calendar virtualization needed (few columns), optimized/inlined assets, lazy below-the-fold.
• Budget: interactive under ~1s on the clinic LAN; day board renders instantly; reports under ~2s. Load-tested with k6 before go-live.

Real-time & multi-user

When several patients are seated at 9:00 across chambers, every station's board updates live.

• SSE per-day channel — each open board subscribes to /api/day/:date/stream. A booking mutation emits a Postgres NOTIFY; the app relays it to subscribed streams; boards patch the changed appointment.
• Optimistic UI — a drag/edit applies instantly, then reconciles with the server's authoritative result; on rejection it rolls back with the reason.
• Concurrent edits — optimistic-concurrency (version/updated_at); a stale write returns 409 and refetches, so two receptionists never silently clobber each other.
• SSE needs no websocket server and sits behind nginx unchanged — ideal for this scale.

AI voice booking (bilingual, guardrailed)

One French/English AI receptionist that only books, stays on-script, and writes straight into this calendar — replacing today's unguarded agent.

• Three-step signed API (§6): availability (offer times where ≥1 chamber is free for session+buffer), hold (first free chamber, short TTL, never named to the caller), confirm (idempotent). Same validation path as staff; create-only, no override.
• Guardrails: a locked system prompt scoped to booking; refuse/deflect off-topic; a hard max call duration; profanity/abuse handling; language auto-switch FR/EN; it confirms date and time only, never a chamber number.
• Security: dedicated service credential, HMAC-signed webhooks, rate-limited, per-call/number ceilings, full audit (booking_channel = voice_ai, call id logged).
• Capture: the AI records booking_source ("how did you hear about us") at booking — without it Valerie's #1 report can't exist.

Files & documents

Consent scans and session notes — built the moment the patient-data scope is confirmed.

• Stored in encrypted Canadian object storage, never on the app server; uploaded via short-lived presigned URLs; downloaded via signed, expiring links.
• Type + size allow-list (PDF/DOC/JPG/PNG/TIFF), content sniffing, antivirus scan on ingest.
• TIFF (common for scanned consent) doesn't preview in browsers → download-to-view at launch, with optional server-side PNG conversion later.
• Notes are timestamped + authored + audited; classified as operational (not the Medesync legal record).

Testing & QA

The booking rules are safety-critical, so they get the most tests; everything ships behind CI.

• Unit (Vitest): the booking engine — overlap incl. buffer, adjacency, window fit, DST transition days, min/max, the 9-status occupancy set. This is where correctness lives.
• Integration: the API against a real Postgres (the EXCLUDE race, idempotency, RBAC denials, audit rows written).
• E2E (Playwright — the harness we already built): full flows on desktop and mobile, mapped to the spec, zero console errors.
• Load (k6): the day board + booking under concurrency before go-live.
• Security: dependency scan in CI, plus the independent review/pen test.
• CI gate: lint → typecheck → unit → integration → build; E2E on staging; nothing merges red.

Hosting, DevOps & backups

• Hosting: a Canadian region (an OVH Montréal/Beauharnois box — we already run OVH Canada — or an equivalent CA provider) for Law 25 residency. App in Docker behind nginx; Postgres on the same box or a managed CA Postgres.
• CI/CD: self-hosted GitLab pipeline — build image, run migrations against staging, run tests, deploy to staging, then a gated promotion to prod with automatic rollback on health-check failure.
• Config: environment-injected secrets, least-privilege DB roles, per-environment settings.
• Backups: nightly base + WAL (point-in-time recovery), encrypted, off-box in Canada, with scheduled restore drills and a documented RTO/RPO.
• Monitoring: uptime checks, error tracking, auth/security alerts, disk/DB metrics; on-call runbook.
• SSL: Let's Encrypt auto-renew (the pattern the demos already use).

Migration & go-live

No export from Medesync (confirmed not feasible). We start clean and let history build.

1. Seed the clinic config — chambers + laser, services, operating hours, users/roles.
2. Cutoff date agreed with the client; from that date, bookings live in the new system.
3. ~2 weeks of current bookings keyed in by staff (a fast entry screen). This is the clinic using its own operational data — not the Medesync "export" that triggers TELUS's legal process.
4. UAT on staging with Luisa (workflow) and Valerie (report definitions) signing off.
5. Go-live: switch the AI + front desk to the new calendar; Medesync stays for the medical record.
6. Reporting note: figures start counting at go-live — month one of data is month one. (Exactly why the capture layer is R1.)

Phased roadmap

The order is chosen so each phase proves the foundation and unblocks the next. Estimates are engineering-time ranges for planning, not quotes.

Phases 3–7 can be re-ordered to the client's priority; only Phase 0 and the Phase-1 capture layer are strict prerequisites.

Risks & mitigations

Appendix & checklists

Go-live security checklist

• ☐ TLS 1.3 + HSTS + security headers verified · ☐ argon2id + lockout + (admin) MFA · ☐ RBAC on every route audited
• ☐ Zod on every input · ☐ CSRF + rate limits · ☐ audit trigger append-only, app role INSERT-only
• ☐ Encrypted at rest + encrypted backups · ☐ PITR restore drill passed · ☐ Canadian region confirmed for DB/files/errors
• ☐ Dependency scan clean · ☐ independent pen-test passed · ☐ breach runbook + privacy contact in place
• ☐ Load test passed · ☐ E2E green on staging · ☐ data-classification appendix signed

Environment configuration (representative)

DATABASE_URL           # Postgres 16, Canadian region
AUTH_SECRET            # session signing
AI_SERVICE_KEY / AI_HMAC_SECRET   # signed voice-agent endpoint
STORAGE_ENDPOINT / KEYS           # CA object storage (later)
SENTRY_DSN             # CA-hosted error tracking
SMTP_*                 # invoice/quote email (later)

What already exists

A tested, deployed front-end demo of every screen (crm.nexuscai.com), a validated booking engine + Postgres schema pattern (the calendar repo), the Prisma scaffold, and the Playwright test harness. Phase 0/1 start from running code, not a blank page.