Case Study · Clinical
ExClinCalc
Clinical Decision Support System (CDSS) for primary clinics
Pain points
Taiwan's National Health Insurance covers 99.9% of the population, with over 23,000 medical institutions nationwide — yet primary clinics' digitization gap is huge. Many still rely on paper charts, ad-hoc Excel files, or siloed commercial software. Large hospitals have full HIS / EMR systems, but small-to-mid clinics either can't afford them or find the workflow doesn't fit.
Meanwhile, algorithms and LLM tools are advancing rapidly in clinical decision support, but two practical limits prevent real adoption: regulation and liability (automated tools cannot replace physician judgment) and data security (medical data centralized on third-party clouds is uncontrollable).
System architecture
Six roles share one PostgreSQL; authorization isn't in backend code, it's in Row Level Security policies inside the database.
- 1 · Healthcare staff login
Email + password (Supabase Auth) → enroll TOTP at
/pro/security→ every subsequent login requires mfa-verify before entering/pro/* - 2 · Middleware route protection
All
/pro/*routes requireaal2(Authenticator Assurance Level 2 = TOTP verified). Otherwise, redirect to enroll. - 3 · Database-layer RLS (the core defense)
Every query is checked by PostgreSQL against JWT and policy before execution. 14 tables, 29 policies — no permission checks in backend code.
- 4 · Trigger-based audit
Logins, prescription creation, SOAP edits, drug-interaction queries, and other sensitive operations are auto-written to
audit_logsvia Supabase triggers (90-day retention). - 5 · Edge-node service
Cloudflare Workers runs Next.js 16 + OpenNext. Gemini API key and Supabase service-role key live in Worker runtime secrets, never exposed to the frontend.
Security highlight 1 — PostgreSQL Row Level Security
ExClinCalc fully implements 14 tables / 29 RLS policies, covering SELECT /
INSERT / UPDATE / DELETE. The application layer hardly needs if (user.role === 'doctor')
anywhere — authorization rules are in the database, applied automatically to every query.
auth.uid() to the users table, then derives visible rows based on role + clinic_id.Core advantages:
- Single source of truth: rules live in PG, all applications (web / future mobile / 3rd-party integrations) share the same authorization logic
- SQL injection is also blocked: even if an attacker bypasses the application layer and hits PG directly, policies still apply
- JOINs handled automatically: SELECT prescriptions JOIN patients — PG applies policies to both tables
Trade-off
- Hard to debug: an RLS-rejected query just returns "no row" — you can't tell whether the row truly doesn't exist or whether policy blocked it. Workaround: in dev,
RESET ROLEto superuser and rerun for comparison - Complex queries can slow down: EXISTS subqueries run per row. Workaround: index anchor columns, use STABLE function caching
- Schema migrations must stay in sync: forget to update policies after adding a column and it gets blocked. Workaround: every migration runs a 4-role RLS test
→ Detailed reasoning is in the article RLS vs application-layer authorization (in 中文).
Security highlight 2 — TOTP MFA
All healthcare-staff roles enforce RFC 6238 TOTP via Supabase Auth's AAL2. 5 failed attempts lock the user for 30 minutes (per-user, not per-IP — an attacker without the password can't trigger lockout).
Trade-off
No backup-code mechanism today (lost phone = no recovery). Known gap, future direction: backup codes + magic-link recovery.
Security highlight 3 — Audit / Secret management / Supply chain
Beyond RLS and TOTP, three frequently overlooked but equally critical layers run in the background:
- Audit trail (audit_logs)
Sensitive operations — login, prescription creation, SOAP edits, drug-interaction queries — are auto-written by Supabase triggers, retained 90 days. Even if the application layer is tampered with, the trigger fires at the DB layer and cannot be bypassed; usable for forensic review.
- Edge secret isolation
Gemini API key and Supabase service-role key live only in Cloudflare Worker runtime secrets — never appear in frontend bundles, git history, or logs. Any frontend vulnerability gets you nothing on the backend.
- Supply-chain defense
GitHub repo has Secret Scanning + Push Protection (pushes containing secrets are blocked) + Dependabot scans npm vulnerabilities weekly. CI/CD pipeline has a secret-rotation checkpoint.
Six roles
SOAP 7-step charting, ICD-10 auto-suggest
Triage workbench, 7 vital signs
Prescription dispensing, two-layer drug-interaction check
Profiles read-only (same clinic only)
Account management, drug-DB CRUD, usage statistics
Same as admin + medical-reference write access
Physician workflow — from dashboard to prescription
Three screens form the physician core: today's queue dashboard, SOAP 7-step charting, prescription drug-interaction check.
Trade-off
- The 7-step SOAP is my own decomposition for "forced completeness", not the SOAP standard. Physicians may find it tedious — future direction: optional skip + LLM auto-fill
- 12 drug-interaction pairs is not exhaustive, only "most common, most lethal" combinations. Future direction: integrate FDA OpenFDA API + LLM to expand rule-base coverage
Multi-role collaboration demo
RBAC isn't only at the backend. The same schema yields completely different workbenches per role:
Administration & analytics
Admin can view platform usage statistics, prescription distribution, user activity. The dashboard is for clinic self-review, not externally exposed.
Tech stack
Research questions surfaced
- RLS design methodology for multi-tenant medical systems — I replaced application-layer authorization with 29 RLS policies, but the design lacks a systematic methodology. How can RLS policy drafts be auto-derived from business requirements? How can policy completeness be formally verified?
- Graded architecture for safely embedding LLMs in SOAP workflows — How do you design graded LLM intervention ratios? How do you quantitatively evaluate the trade-off between hallucination rate and coverage?
- Real-world evaluation methodology for CDSS tools — much academic CDSS research stops at "feature completeness evaluation"; "real-world deployment evaluation" is missing. How do you design rigorous CDSS field evaluation, including user acceptance and alert-fatigue measurement?