# CareCollect — Phases 0 & 1 Phlebotomy / home sample collection platform. PHP 8.3 + MySQL 8, no Composer, no framework. Working name only. --- ## What's in this drop **Phase 0 — foundation** Config layer · PDO wrapper with slow-query logging and read-replica routing · Argon2id auth with two guards and account lockout · RBAC with wildcard permissions · tenant resolution by subdomain or custom domain · CSRF, session hardening, security headers · router with middleware · Redis-or-file cache · partitioned audit log · job queue worker · **barcode series** · console UI shell, sign-in, dashboard, error pages. **Phase 1 — platform console (Caresoft, level 1)** Hospitals: list, search, filter, create, edit, activate/suspend · first-admin handover with one-time password · plans and billing ledger · Caresoft staff accounts with last-superadmin protection · audit trail viewer. --- ## Install ```bash # 1. Database (MySQL 8.0+) mysql -u root -p < sql/carecollect_schema.sql mysql -u root -p carecollect < sql/002_phase0.sql mysql -u root -p carecollect < sql/003_phase1.sql # 2. Config — never edit config/config.php on a server cp config/config.local.example.php config/config.local.php # set db credentials, app.url, app.key, and app.debug = false # 3. Permissions chmod -R 775 storage/ # 4. First superadmin php bin/console.php passwd admin@caresoft.co.in platform # 5. Point the web root at /public — nothing above it is reachable ``` Local check: `php -S 127.0.0.1:8080 -t public` --- ## Deployment Nginx + PHP-FPM. Web root is `/public`. ```nginx server { server_name carecollect.in *.carecollect.in; root /var/www/carecollect/public; index index.php; location / { try_files $uri $uri/ /index.php?$query_string; } location ~ \.php$ { fastcgi_pass unix:/run/php/php8.3-fpm.sock; fastcgi_param SCRIPT_FILENAME $document_root$fastcgi_script_name; include fastcgi_params; } location ~* \.(css|js|png|jpg|svg|woff2)$ { expires 30d; add_header Cache-Control "public, immutable"; } client_max_body_size 12m; gzip on; gzip_types text/css application/javascript application/json image/svg+xml; } ``` Wildcard DNS `*.carecollect.in` plus a wildcard TLS cert gives every hospital its own subdomain without any per-tenant setup. **Job worker** — run at least two, under systemd: ```ini [Unit] Description=CareCollect worker After=network.target mysql.service [Service] User=www-data ExecStart=/usr/bin/php /var/www/carecollect/bin/worker.php --queue=default --sleep=2 Restart=always RestartSec=5 [Install] WantedBy=multi-user.target ``` **Cron** ``` 0 2 1 * * php /var/www/carecollect/bin/console.php partitions 20 0 * * * php /var/www/carecollect/bin/console.php rollup ``` The first adds next month's partitions to `location_ping`, `api_log` and `audit_log` ahead of time. If it stops running, inserts eventually pile into `pmax` and the tables slow down — monitor it. The second computes yesterday's figures for every hospital. Safe to re-run: it replaces a day rather than adding to it. If it misses a night the reports say so on screen and offer to rebuild that day. **Production checklist** - `app.debug = false`, `session.secure = true` - `opcache.enable=1`, `opcache.validate_timestamps=0` - `redis.enabled = true` (the file cache is a dev convenience, not a plan) - Set a real 32-character `app.key` - MySQL slow query log at 300 ms --- ## The barcode CareCollect generates it; HIS consumes it. Sixteen numeric digits: ``` TTTT YYMMDD NNNNN C | | | └── Luhn check digit | | └─────── per-hospital sequence for that day | └─────────────── collection date └────────────────────── hospital number ``` Numeric-only means Code128 subset C packs two digits per symbol, so the label fits a 25 mm tube, and there is no O-versus-0 ambiguity when someone reads it back over a phone call. The Code128 encoder was verified module-for-module against an independent reference implementation. Sequence allocation is atomic (`INSERT … ON DUPLICATE KEY UPDATE LAST_INSERT_ID(last_seq+1)`), so simultaneous collections cannot collide. ```bash php bin/console.php barcode:test 1 0001260821000014 valid=yes {"tenant_no":1,"date":"2026-08-21","sequence":1} ``` Render with `Barcode::svg($code)` for browser printing or `Barcode::png($code)` for thermal printers. --- ## CLI ``` php bin/console.php passwd [platform|tenant] php bin/console.php tenant:add "Name" [timezone] php bin/console.php barcode:test php bin/console.php partitions php bin/console.php health ``` --- ## Conventions the next phases must keep 1. `tenant_id` is the **first column of every composite index** on tenant-owned tables. A query that doesn't start there doesn't ship. 2. `Paginator` is for bounded admin lists only. Orders, pings and API logs use keyset pagination (`WHERE id < :last_id`). 3. Nothing slow inside a web request. WhatsApp, webhooks, route optimisation, HIS pushes and PDFs all go through `job_queue`. 4. Every mutation calls `Audit::log()`. When the actor is Caresoft staff acting on a hospital, pass the tenant id explicitly — platform users have no tenant context of their own. 5. Never `SELECT *` on `order`. Name the columns. 6. Passwords are shown once, in the interface. Never emailed, never stored in plain text, never written to the audit trail. --- ## What was found while testing Built and run against a live database, not just linted. Four real bugs were caught and fixed in the process: - Helpers were loaded after the config that used them — every CLI command died on startup. - `platform_user` had no `failed_attempts` column, so signing in as Caresoft staff threw a 500. Fixed properly in `003_phase1.sql` by giving platform accounts the same lockout protection as hospital accounts — they can see every hospital, so if anything they matter more. - The audit trail recorded `tenant_id = NULL` for every platform action, which made the hospital filter on the audit screen useless. - Route parameters matched any characters, so `/tenants/new` could be read as `/tenants/{id}`. Ids are now digits-only by pattern, not by route ordering. Verified working end to end: sign-in and lockout, CSRF rejection, hospital create with duplicate-subdomain and validation guards, role cloning per hospital, admin handover with one-time password, activate/suspend, audit writes, 404 on unknown ids, and level separation — a hospital admin signing in on their subdomain gets their own dashboard and a **403 on the platform console**. One deployment note: the schema targets MySQL 8. The test box had MariaDB 10.11, which required dropping `SRID 4326` from the `zone.boundary` column to load. Everything else ran unmodified. On MySQL 8 it loads as written. --- ## Phase 2 — hospital console Branches and collection centres · team accounts with password reset · phlebotomist profiles (one action creates the app account, the profile and the zone links) · zones drawn on a Leaflet map or defined by pincode list or radius · slot rules and a materialised 45-day calendar · test catalogue with per-hospital pricing and a price change log. **Zone resolution order** — pincode list, then polygon containment, then centre plus radius. Pincode is an indexed equality match and covers most Indian addresses, so the expensive checks rarely run. **Slot calendar** — `slot_template` is the rule; `slot_day` is the materialised calendar the booking screen reads. A booking page that evaluates recurrence rules live is the thing that goes slow first at volume. Rebuilding never destroys a window that already has bookings: capacity moves up, never down. **Pricing** — hospitals never edit the global master. Their price lives in `tenant_test_price`, every change is written to `price_change_log`, and a test they add themselves is priced at its list rate immediately, so nothing is ever billable at zero by accident. ### Verified in Phase 2 Branch and team creation · phlebotomist creation producing exactly one app account, one profile and one zone link · duplicate mobile rejected · malformed map shapes rejected before MySQL sees them · polygon stored and auto-closed, containment confirmed (inside hits, far-away misses) · pincode parsing across mixed separators · backwards slot windows rejected · calendar generation (180 windows over 45 days) · booked windows surviving a rebuild · catalogue filters, bulk list-rate pricing, single price override with change log · zone resolution returning served and not-served correctly. **Isolation, tested rather than assumed:** a hospital admin editing another hospital's zone or phlebotomist by id gets a 404 and the record is untouched; a phlebotomist signing in gets 403 on every console screen. Two more bugs fixed here: the boundary column needed a nullable definition to match the shipped schema, and cross-tenant writes returned a 500 from a rolled-back transaction instead of a clean 404 — ownership is now checked before the transaction opens. **Portability note:** zone containment uses the two-argument `ST_SRID` form, which is MySQL 8. On an older server or MariaDB the spatial lookup is skipped with a logged warning and pincode plus radius still work, so bookings never break outright. --- ## Phase 3 — orders Console booking · patient self-booking from a branded link, no login and no app install · dispatch board scoped to a day · manual assignment with load checks · the status engine · patient tracking page · queued notifications. **The status engine is the centre of this phase.** Every status change goes through `OrderService::transition()`, which holds an explicit table of what may follow what. Controllers, the phlebotomist app and the partner API all call the same method, so an order cannot reach a state by one route that it could not reach by another. A sample cannot be handed over before it is collected; a completed order cannot slide back to pending. Timestamps are set by the engine, never by the caller — an app with a wrong clock must not be able to backdate a collection. **Overbooking is prevented in one statement:** ```sql UPDATE slot_day SET booked = booked + 1 WHERE id = ? AND blocked = 0 AND booked < capacity ``` Capacity is checked and claimed together, so two people booking the last seat at the same instant cannot both win — the loser gets zero affected rows and a clear message. Cancelling releases the seat; rescheduling claims the new window *before* releasing the old one, so a failed move never leaves a patient with no slot at all. **Prices never come from the browser.** The public booking form posts test ids and nothing else; every amount is read from the catalogue inside `book()`. A missing price falls back to list rate rather than billing zero. **Notifications are queued, never sent inline.** A WhatsApp API that takes four seconds must not sit inside the request that books an appointment — the booking is the thing that has to succeed. Until provider credentials are configured, the worker marks messages `skipped` with the reason recorded, so an unsent message is visible in the queue rather than silently lost. That is the failure mode that makes a patient turn up unfasted. **Patient tracking** uses an unguessable per-order token, not a login. It is safe to send over WhatsApp, shows only what the patient already knows plus where things stand, and lets them cancel while it is still early. ### Verified in Phase 3 Public booking end to end · fasting detection driving a second message · tracking page renders, wrong and malformed tokens 404 · **four simultaneous bookings for one remaining seat: exactly one succeeded, three were refused, the counter ended at capacity, and the losers' patient records rolled back cleanly** · illegal transition refused with a readable reason · the full happy path from assigned to completed · cancellation blocked without a reason and releasing the slot with one · a cancelled order cannot be revived · reschedule moving the counter across windows, and a reschedule onto a full window failing without disturbing the original · worker draining the queue. Isolation holds: a phlebotomist gets 403 on the board, another hospital's order id gives 404. ### Two more fixes made here **Nested transactions.** A controller opening a transaction to create a patient and then calling `OrderService::book()`, which opens its own, hit *"There is already an active transaction"* — PDO has no nesting. `Db::transaction()` now uses savepoints: an inner rollback unwinds only its own work, and the outermost caller still decides whether the whole thing commits. Services can now call each other freely. **The public booking throttle was too tight.** Ten submissions per ten minutes per IP looks reasonable until you remember Indian mobile carriers put thousands of users behind one CGNAT address — it would have blocked real patients long before it blocked anyone abusing the form. Raised to thirty; CSRF and validation do the actual work. --- ## Phase 4 — the phlebotomist app Two halves, and they are not equally finished. **The mobile API (`/api/v1/*`) is built and tested** against a live database: token auth, attendance, the day's route, the vial checklist, the door-code gate, collection with server-issued barcodes, failure reasons, payments, photo upload, handover, and batched location intake. **The Android app source is written but not compiled.** It is a working skeleton for your Android developer — the foreground location service, the Room offline queue and the sync worker are the parts worth reading first, because those are the decisions that are expensive to change later. See `android/README-android.md`. ### Idempotency is the spine of the whole thing Every write endpoint takes a `client_op_id`. The server records it and replays the original response if the same id arrives twice. This is not a nicety — the common failure on Indian mobile data is that the request *succeeded* and the response never came back, so the phone retries. Without this, one collection in a lift shaft becomes two barcodes and two bills. Verified: replaying a collect returns the same two barcodes and creates no new samples. A rejection (422) is recorded too, so a doomed action stops hammering the server. A genuine failure (5xx, timeout) is deliberately *not* recorded, so the app can retry it. ### Guards that came out of thinking about the actual job - **Collection is refused until the door code is verified.** It is the proof the right patient was visited. - **Check-out is blocked while samples are still in the bag.** Otherwise someone walks home with a patient's blood. - **Barcodes are issued server-side.** Two phones can never mint the same label. - **Overpayment is refused** with the real balance in the message. - **A bad barcode at handover is reported back, not dropped.** That scan is the moment a missing tube gets noticed. - **Mock locations are flagged, not refused.** Refusing tells whoever is faking it exactly what to fix; flagging lets the dispatcher see it. ### Location intake The highest-volume endpoint in the product — 200 phlebotomists at two pings a minute is roughly 190,000 rows a day. It is built to be boring: batched upload, one multi-row INSERT, an upsert into a single hot row per person. The live map reads `live_location` or Redis and **never** touches the ping history. ### Verified in Phase 4 App login with hospital code · a console user cannot sign into the field app · bearer auth required · check-in, and the same op id replaying instead of double-recording · route and stop detail with tests grouped into a **vial checklist by tube colour** · collect refused before the door code · wrong code refused · collect issuing two barcodes, replay returning the same two · part payment then balance, overpayment refused · check-out blocked with samples in the bag, allowed after handover · handover accepting real barcodes and reporting a fake one · location batch dropping 0,0 and non-numeric points while flagging a mock · revoked token refused after logout. ### Two bugs found in the tracking path Both were the same class of problem — a late batch arriving out of order. 1. **Tied timestamps picked the wrong fix.** Points in one batch often share a second; the code kept the first rather than the last, so the marker lagged by a whole batch. 2. **A stale batch dragged the marker backwards.** Guarding only `recorded_at` was not enough — the position columns updated unconditionally, so a 45-minute-old batch arriving late moved the dispatcher's map to where the person *had been*. Every column now moves together or none of them do. Verified: a 45-minute-old batch is stored in history and leaves the live marker untouched. The second one is exactly the "why is he suddenly in Pune" bug, and it would have been very hard to diagnose from a support ticket. --- ## Phase 5 — the map layer Live map, travel-time engine, assignment suggestions, auto-assignment, route optimisation with arrival times, and the patient's live ETA. ### The maps decision, deferred properly You have not picked a provider, so rather than guess I built the abstraction and shipped a default that works with nothing installed: | Provider | Cost | Accuracy | When | |---|---|---|---| | `haversine` | none | rough | **default** — works on day one | | `osrm` | free, self-hosted | good | the right answer at real volume | | `google` | metered per element | best, traffic-aware | if you want live traffic | It is a config value, not a code change. The fallback is straight-line distance times a 1.4 detour factor at per-profile city speeds — good enough to *rank* candidates, not to promise a patient an arrival time. **Crucially, the board tells the truth about which is running.** If OSRM is configured but the container is down, travel times silently fall back to estimates and the badge reads "Routing offline — estimates". A screen promising traffic-aware times while quietly serving straight-line guesses is lying to the coordinator, and that is how people stop trusting the whole product. ### The cache is what makes any provider affordable Keyed on a geohash-7 pair (~150 m cells), an hour bucket and a travel profile. Two pickups on the same street at the same time of day share one lookup. Three traffic buckets rather than 24 hourly ones, so the cache fills eight times faster. With Google, cache hit rate *is* the bill. ### Suggestions show their working Ranked candidates come back with the reasoning attached: *"About 12 minutes from their previous stop · 2 of 18 collections booked · Not assigned to this zone"*. A coordinator who cannot see why the system picked someone stops trusting it by the second week. Scoring is entirely in seconds of cost, so the terms are comparable rather than arbitrary: travel time from their previous stop, plus penalties for wrong zone, current load, missing skills and not being checked in. ### Route optimisation respects the promise, not just the distance Slot windows are a commitment to a patient, so stops are grouped by window and only reordered *inside* it — nearest-neighbour then 2-opt. Optimising across windows would produce a shorter route and a stream of angry calls about someone arriving three hours early. **The output that matters is the breach list.** The plan reports which stops it cannot reach inside their window, and by how much, before the day starts: ``` ! CC26-000016 window 07:00–07:30 -> arriving ~09:24 (1 hour 55 min late) ``` The coordinator hears that at 06:00, not from the patient at 11:00. ### Verified in Phase 5 Travel engine per profile (bike 20 min / car 25 min / on foot 1 h 19 for the same 4 km) · cache storing one row per profile · suggestions ranking two phlebotomists with visible reasoning · auto-assignment spreading load and **honestly skipping two orders as "slot is outside their shift"** rather than forcing them · route optimisation writing sequence and ETAs back onto the orders · breach detection catching all 5 stops on a deliberately impossible day · 2-opt measurably improving on nearest-neighbour (113.8 km as booked → 66.6 nearest-neighbour → 60.6 after 2-opt) · live ETA computed from a real fix, and **correctly refusing to give one from a 40-minute-old fix** · stale markers drawn hollow on the map · a phlebotomist still gets 403 on the dispatch map. ### Two bugs found here 1. **The travel cache ignored travel mode.** Keyed only on the point pair and the hour, so a phlebotomist on foot was served a motorcycle's travel time — a four-times error, and exactly the kind that produces a schedule nobody can keep. Profile is now part of the key. 2. **The provider badge reported configuration, not reality.** With OSRM configured but unreachable it claimed traffic-aware routing while serving estimates. It now probes the provider and reports what is actually running. --- ## Phase 6 — samples and the lab Bag receiving with discrepancy detection, rejection with free recollection, stability monitoring, and a full chain of custody per tube. ### Samples are tracked separately from orders, on purpose An order is an appointment. A sample is a physical object that can be rejected and recollected while the order stays open. An accreditation auditor asks about the tube, not the booking, so `sample_event` is its own trail: ``` collected pending -> collected phlebo bagged collected -> in_transit phlebo Handed over in B260821-BA02BD received in_transit -> received lab rejected received -> recollect lab Haemolysed sample — Visible haemolysis recollection_booked lab New order CC26-000017 ``` ### A missing tube is the headline, not a footnote The lab scans what is physically in the bag, not what the manifest claims. Anything on the manifest that goes unscanned is recorded as **missing** and the bag is flagged as a discrepancy with the phlebotomist's number on screen. Anything scanned that is not on the manifest is reported with a reason — and if it is a real tube from a different bag, it says which order it belongs to, because "unknown barcode" is not an answer anybody can act on. Tested: a four-tube bag received with three scanned plus one stray and one nonsense code produced `expected 4, received 3, missing 1, extra 2`, marked the bag as a discrepancy, and left the missing tube in transit rather than quietly closing it. ### Rejection ends with a booking, not a dead end A rejected sample still leaves a patient without their test. Retryable reasons (haemolysed, clotted, insufficient) put the tube on a recollection worklist; one click books the revisit as a **new, free, linked order**. New rather than reopening the old one, because the first visit did happen — someone was paid for it and the patient was billed for it. Rewriting history would break the day's numbers and the phlebotomist's record. And free, because charging twice for our own rejection is how a hospital loses a customer. ### Stability, because past the window a result is wrong, not late Each sample type carries a stability window (fluoride 4h, EDTA 8h, urine 2h, serum 24h). Anything in the field or in transit that is expired or has under an hour left is surfaced at the top of the lab screen. A potassium from an eight-hour-old EDTA tube reads high whatever the patient's actual chemistry — the lab needs to know before it runs the test, not after. ### An order reaches the lab only when every tube does One received tube out of three is not an order the lab can work on, so `received_lab` is set only once nothing is outstanding. Verified. ### Verified in Phase 6 Real field run through the API producing four tubes in a bag · discrepancy receiving (3 received, 1 missing, 2 unexpected, one identified as belonging to another order) · order closing only on the last tube · rejection moving a sample to recollect and queueing the patient message · the worklist showing it · free revisit booked with zero-priced items and a link back to the original · double-booking a recollection refused · single stray tube received on its own, receiving it twice refused, nonsense barcode refused · stability crossing from "3 hours left" to "critical" to "past the window by 1 hour" at the right boundaries · at-risk worklist surfacing an expired EDTA and a fluoride with 30 minutes left · phlebotomist gets 403 on every lab screen · another hospital's bag gives 404. ### One fix along the way The sample id for the custody event was being read back with `lastInsertId()` immediately after the insert. It worked, but it is exactly the kind of thing that breaks silently the day someone adds a write between the two lines. Now the id is captured from the insert itself. --- ## Phase 7 — money Payments, refunds, receipts, payment links, and the cash trail from a phlebotomist's pocket to the counter. ### Two rules the whole module is built on **Nothing is ever edited.** A payment row is written once. A refund is a second row with a negative amount pointing at the first. Money taken and given back is two facts, and an auditor needs to see both — an amended row hides the first one. Verified: a partial refund left the original ₹200 row untouched and added `RF26-000006 / -50.00 / refund_of 3`. **Cash in a pocket is a liability until it is counted.** The system's figure and the phlebotomist's declared figure are compared at the counter, never assumed equal. ### The deposit flow, and why it has two steps Declaring is not depositing. The phlebotomist says what they are handing in; the counter counts the notes and accepts. Only on acceptance are the individual payments tied to the deposit and cleared from their outstanding figure — doing it at declaration would let someone clear a liability by typing a number. Tested end to end: held ₹500, declared ₹480, still showed ₹500 outstanding. Accepting a ₹20-short count **with no reason was refused**; with a reason it was accepted, flagged `disputed`, and the outstanding figure dropped to zero. The outstanding screen sorts by **age, not amount**. Cash sitting with someone for three days is the number a hospital actually wants flagged. ### One code path for money The field app and the front desk now both call `PaymentService::record()` — same balance checks, same receipt series, same audit line. Overpayment is refused with the real balance, and a payment replayed after a network timeout returns the original receipt rather than charging twice (verified: paid_amount moved by exactly ₹100 across two identical calls). ### Receipt numbers Sequential per hospital per year — `R26-000005`, refunds as `RF26-000006`. Also a **bug fix**: the field app had been building receipt numbers from a random two-digit suffix. Fine until two collide and an auditor asks which one is real. Now the same atomic counter as barcodes and order numbers, with a unique key behind it. ### Payment links without a gateway With no provider configured a link is still created and recorded as `pending`, and the screen says plainly that it cannot actually be paid. Same principle as maps and notifications: visible and honest beats a silent failure or a fake success. ### Verified in Phase 7 Field payment through the shared service · overpayment refused with the real balance · unsupported field modes refused · idempotent replay not double charging · cash position excluding UPI · declare-then-verify with a variance blocked until explained · payments tied to the deposit only on acceptance · refund requiring a reason, respecting the refundable remainder, and leaving the original row intact · order payment status moving to `partial` after a refund · receipts rendering for both payments and refunds · payment link created as pending with no gateway · phlebotomist gets 403 on the money screens · cross-tenant receipt gives 404 · checkout reporting the cash to carry to the counter. --- ## Phase 8 — reports Overview, productivity, zones, rejection analysis and revenue, on nightly rollups. CSV export throughout. ### Range reports never touch the orders table Four small rollup tables carry the day's figures. A thirty-day report reads thirty rows on the primary key instead of scanning months of orders — that is the difference between a management screen that opens instantly in year three and one that times out. Today is the exception: it is computed on demand, because a coordinator looking at this morning cannot wait for tonight's cron. Reports also route to the read replica where one is configured, so a month-end export never competes with the dispatch board. ### Two decisions that keep the numbers honest **Recompute, never increment.** Each run replaces a day wholesale. Re-running after a late correction — a sample rejected the next morning, a refund posted on Monday for Friday's collection — gives the right answer instead of double counting. Verified: four consecutive runs left the figures identical and one row per day. **Store counts, not ratios.** Counts add across days; percentages do not. Averaging seven daily "82% on time" figures is wrong unless every day had identical volume, and that error is invisible until someone checks by hand. All ratios are derived at read time from stored counts. Percentiles are the exception, since they cannot be recombined — so p50 and p90 are stored per day and the range view honestly shows *best and worst daily median* rather than inventing a median of medians. **Turnaround is reported as medians, not averages.** One collection that sat in a bag overnight drags an average badly and tells you nothing about the typical visit. **"On time" excludes what could not be timed** from both the numerator and the denominator. A denominator that quietly counts untimed visits as successes flatters the figure. ### A judgement call worth disagreeing with The productivity screen carries a warning against ranking field staff on collections per day, and deliberately puts rejection rate and first-attempt success next to volume. Someone drawing twenty a day with a 9% rejection rate is doing worse work than someone drawing fourteen with none — the rejections come back as free revisits that cost more than the extra collections earned. If a hospital wants a pure volume leaderboard, the data is all there and the CSV export gives it to them. But the default should not quietly push people toward rushing draws. ### Verified in Phase 8 Rollup across two hospitals including one with no activity · idempotency across four runs · the on-time metric flipping correctly when a collection was moved inside its window · real percentiles · a fortnight of genuine historical orders backfilled and rolled up (81 booked, 64 collected, 4 failed) · all five report screens · three CSV exports with correct headers and content type · missing-day detection after deleting two rollup rows, and rebuilding one from the screen · `EXPLAIN` confirming the range report is a 13-row primary-key range, not a scan · the nightly job running through the worker · phlebotomist gets 403 on every report including the CSV export. --- ## Phase 9 — the partner API Client-credentials auth, versioned endpoints under `/v1`, signed webhooks, a sandbox, per-client rate limits, an access log and an integration guide. ### `external_ref` is required, and that is the point Order creation takes the partner's own reference and refuses without one. Send the same reference twice and you get the same order back with `idempotent_replay: true` — not a second phlebotomist at the same door. Partners retry; this is what makes retrying safe. Orders can be looked up by our number or by their reference. Both work, so a partner never has to store ours. ### Sandbox lives in the same tables, marked Sandbox credentials work against the hospital's real catalogue, real prices and real collection windows. The orders carry `is_sandbox` and every operational path filters them out — dispatch board, live map, phlebotomist route, all four rollups. A live token cannot read a sandbox order and vice versa; both get 404. A separate database would be cleaner in theory and doubles the migration surface forever in practice. The risk with a flag is a screen that forgets the predicate, so it is applied centrally and was tested by booking a sandbox order and confirming it appears on **none** of those screens while sitting in the table (13 rows, 12 counted). ### Webhooks HMAC-SHA256 over `timestamp.body`, the conventional scheme, so a partner's developer already knows how to verify it and a captured payload cannot be replayed tomorrow. Queued and retried with backoff (1, 4, 9, 16, 25 minutes, six attempts). A `4xx` that is not 408 or 429 is a permanent refusal and is not retried — hammering a rejected payload sixty times helps nobody. An endpoint that fails 20 times in a row is switched off until someone saves the URL again, so a partner whose server was down for a week does not get two thousand callbacks when it returns. Tested against a real listener that verified the signature independently: `order.confirmed` and `order.cancelled` both delivered, HTTP 200, signature valid, attempts recorded. ### What a partner is not told Tracking returns a status and a duration — never the phlebotomist's coordinates. A partner needs to tell their patient "about twenty minutes"; they do not need to watch a named employee move around a map, and that person has a reasonable expectation that a third party cannot follow them house to house. ### Verified in Phase 9 Token exchange, wrong secret 401, missing token 401 · the full booking journey end to end · idempotent replay creating exactly one order · sandbox invisible on every operational screen and in the rollups · live token reading a sandbox order 404 · scope enforcement naming the missing scope and what was granted · unknown test codes named individually · rate limit at 60/min returning 429 with `Retry-After` · secret rotation revoking live tokens instantly · access log recording failures as well as successes · plain-http callbacks refused in both the API and the console · live credentials blocked when the plan does not include them · phlebotomist gets 403 on the console screen · full regression sweep across all 19 console screens after the sandbox predicates went in. ### Two bugs found 1. **The sandbox flag was set after the order was created**, so the `order.confirmed` callback was built from a payload that still said `sandbox: false` and was routed away from the sandbox credentials that booked it — a silent no-callback. The flag is now part of the insert, where everything downstream in the same transaction can see it. 2. **The access log was written but never called.** `logCall()` existed and no handler reached it, because every API path exits through `json_out()` or `fail()`. It is now armed as a shutdown function, so a 401 is recorded as faithfully as a 200 — and a burst of unattributed 401s is exactly what a leaked credential looks like from this side. --- ## Phase 10 — Caresoft HIS integration Outbox, connector API, item mapping, result push-back, and orders raised inside HIS. The cloud half is built and tested; the on-premise connector is specified in `HIS-CONNECTOR.md` for your .NET developer. ### The architecture is decided by where the data lives Caresoft HIS runs on-premise on MSSQL behind a hospital firewall. The cloud cannot reach in. Asking a thousand hospitals for a VPN, a static IP or an inbound port is not a rollout plan — it is a two-year project that never finishes, and it stalls on the one hospital whose IT contact left. So a small agent sits next to the HIS database and polls **outbound over HTTPS**, which every hospital already permits. No firewall change, no network team, no per-site negotiation. It installs the same way a HIS update does. ### An outbox, not a call A row is written in the same transaction as the sample it describes, and the agent drains it. We never "call HIS and hope" — we record the intent and let the agent collect it whenever the link is up. Over a connection that is down half the time, that is the difference between a lost sample and a late one. Rows are **claimed, not deleted**: if the agent dies mid-batch they come back after ten minutes rather than vanishing. Verified by faking a twenty-minute-old claim. ### Nothing is pushed on a guess A test with no HIS item code parks the push as `blocked` with a reason, and it appears on a worklist. A guessed item code means the wrong test billed and possibly the wrong result filed against a patient — a support ticket is a much better outcome than a wrong result. Tested end to end: a collection with no mapping was held (`No HIS item code for: CBC`), the agent's pull correctly returned nothing, and mapping the test released it — *"Mapped. 1 held-up push(es) released."* The same applies inbound: an unmapped HIS item code on an order raised in HIS is refused by name rather than dropped or defaulted. ### The patient key is three columns HIS identifies a patient by `ptype`, `pno`, `pyr` — not one id. Storing our own guess of a UHID breaks the moment a hospital reuses numbers across years, which is exactly what `pyr` exists to handle. The ack carries all three plus the HIS lab number, so support can jump between the two systems on any order. ### The barcode handshake Ours is the lab barcode. HIS accepts it rather than minting its own, which is what removes the relabelling step at the bench. That was the Phase 0 decision and this is where it pays off. ### Both directions Orders raised **inside HIS** — a clerk booking follow-up bloods for a discharged patient — appear on the CareCollect board without anyone keying the patient twice. `his_ref` makes that idempotent: the same reference twice returns the same order, never a second phlebotomist. ### Verified in Phase 10 Agent heartbeat, wrong secret 401, missing key 401 · unmapped test blocking a push and mapping releasing it · pull handing out a full payload (barcode, container, HIS item code, department, patient key) · a second pull correctly returning nothing · ack recording `LAB/2026/44821` plus ptype/pno/pyr/uhid and flipping the sample to `sent` · a retryable failure going back to `pending`, a `permanent: true` failure going to `failed` · a stale claim being re-offered · results arriving, the patient being notified, an unknown lab number refused rather than silently dropped · an order raised in HIS landing on the board with its patient key intact, and a retry returning the same order · mapping suggestions scoring an exact code match at 1.00 and fuzzy name matches at 0.80, labelled as suggestions requiring human confirmation · cross-tenant isolation (another hospital's agent pulls nothing and cannot ack our rows) · integration switched off queues nothing and the agent gets `not_enabled` · full regression across all 22 console screens. ### One thing worth noticing in the test output A result arriving for a sample that never reached the lab did **not** jump the order to `completed` — it stayed at `collected`. The chain of custody is not something a result can fake its way past, and that guard held without being specifically tested for. --- ## Phase 11 — the marketing site Public site, CMS, and the SEO / AEO / GEO layer, including area pages built from real coverage. ### One rule governs the whole phase **A page may only claim what the database can back.** An area page that promises coverage we do not have is worse than no page — the patient books, nobody can go, and the hospital wears it. So coverage is recomputed from live zones every time an area page is saved, and everything on the page comes from the operational tables: which pincodes are genuinely in a live zone, what the earliest window actually is, what the cheapest test really costs. Nothing is typed by hand, so nothing can drift out of date and start lying. Tested: a Bengaluru page claiming two pincodes we do not serve **saved as a draft** with the reason stated. A Thane page claiming six published with *"5 of 6 pincodes are actually covered, by 1 hospital(s)"* — and the uncovered sixth does not appear anywhere on the live page. Then the harder case: switching a zone off and rechecking **pulled the page back to draft**, removed it from the sitemap, and made the URL 404. Restoring the zone flagged it as *"Coverage back — republish"* rather than silently resurrecting it, because auto-demoting is safe and auto-promoting would bring back pages somebody drafted on purpose. ### AEO: the answer block is a field, not a hope Every page, article and area page carries a 40-word answer as its own column, rendered in a box at the top and emitted as FAQ markup. That is the paragraph an answer engine lifts. Hoping a model picks the right sentence out of a body is not a strategy. `/llms.txt` publishes the same answers as plain text — not a standard anyone enforces yet, and cheap enough to be worth having before it is. Schema emitted and verified live: `MedicalBusiness`, `GeoCoordinates`, `PostalAddress`, `City`, `FAQPage`, `Question`, `Answer`, `MedicalWebPage`, `Person`, `reviewedBy`. ### Clinical articles need a named reviewer A patient guide or lab-quality piece cannot publish without one. Unsigned medical advice is exactly what search engines have spent years learning to distrust, and on a healthcare site it is a reasonable thing to be strict about. Verified: the same article was refused, then published once a reviewer was named, and the byline appears on the page and in the schema. ### Verified in Phase 11 Public site, blog, article, area page, 404 on an unknown slug · sitemap, robots and `llms.txt` all rendering · the reviewer gate refusing and then allowing · lead capture with UTM, and a honeypot silently dropping a bot while telling it "thanks" · leads landing in the console · **console paths still winning over the wildcard page route** (`/dashboard`, `/tenants`, `/cms`, `/plans`, `/audit` all 200) · hospital staff getting 403 on the marketing CMS · full regression across all three surfaces — 14 hospital screens, 7 platform screens, 7 public URLs. ### One dev-environment fix PHP's built-in server 404s any path that looks like a file, so `/sitemap.xml`, `/robots.txt` and `/llms.txt` never reached the front controller locally. Nginx and Apache do the right thing from their rewrite rules; `dev-server.php` now makes the dev machine behave the same way. Run it as: ```bash php -S 127.0.0.1:8080 -t public dev-server.php ``` ### A note on volume The CMS deliberately nudges toward publishing area pages in small batches. Fifty good ones beat five thousand thin ones, and a sudden burst of near-identical pages is the exact pattern search engines penalise. The coverage rule already caps how many can honestly exist. --- ## Phase 12 — hardening Load tested at volume, indexes fixed where the measurements said so, two automated gates, and the deployment artefacts. Full detail in `OPERATIONS.md`. ### Measured, not assumed Seeded 12,089 orders, 24,078 items, 9,595 samples and **500,000 location pings**, then timed the hot paths. On a hundred rows every query looks fast and every plan looks fine, which is exactly why this was worth doing. | Path | ms | |---|---| | Dispatch board (200-order day) | 10 | | Live map feed | 13 | | Live map, 8 dispatchers at once | 13 each | | Collections | 7 | | Reports overview, 30 days | 47 → **4** | | Revenue, 30 days | 13 | | Location ping intake | **840 points/sec** | 840 points/sec is roughly 25,000 phlebotomists pinging every 30 seconds — more than the entire Caresoft base would field at once. ### Four real problems the measurements found 1. **`DATE(paid_at) = ?` in twelve places.** Wrapping an indexed column in a function makes the index unusable; the revenue report was a full table scan (`type: ALL`, `key: NULL`). All twelve are now half-open ranges, and the audit script fails the build if one comes back. 2. **A correlated subquery in the rejection report** re-scanned the sample table once per tube type. Replaced with two indexed passes merged in PHP — deliberately two queries, because an `OR` across two different date columns cannot use either index and produces one query and one full scan. 3. **The reports page recomputed today's rollup on every request** — 47 ms at 200 orders a day, and four times that at 2,000, for every viewer on every refresh. Now cached for 60 seconds per hospital: **47 ms → 4 ms**. 4. **"Cash still in pockets" was the slowest query on the board.** A covering index on `(tenant_id, mode, status, deposit_id, collected_by, amount)` means it now answers from the index without touching the table. ### Two gates that fail a bad release `bin/preflight.php` — checks debug mode, the signing key, HTTPS cookies, Redis, OPcache, partition headroom, worker backlog, rollup freshness, HIS connector heartbeats, provider configuration and directory permissions. Verified against a deliberately bad production config: **6 failures, exit code 1, "do not deploy"**. `bin/audit.php` — checks the code against the conventions this README claims. **It found four violations in my own code**: five `SELECT *` on the order table, one surviving date-function wrapper, six tenant-scoped tables with no `tenant_id`-leading index, and a false positive in my own regex (it was matching `FROM order_status_log`). All fixed; it now passes clean. That is the point of writing the checker rather than the paragraph. A convention nobody verifies is a convention that decays. ### Shipped for deployment `deploy/` — nginx config with a wildcard cert and a dedicated rate-limit zone for the location endpoint, two systemd unit templates (workers claim with `SKIP LOCKED`, so parallel copies are safe), the cron file, logrotate, and a backup script. The backup deliberately **excludes `location_ping`** — it is most of the volume and none of what you need to run tomorrow. Breadcrumbs are evidence, not state. The restore drill is written into the backup script with one instruction that matters: run it quarterly and **write down how long it took**. That number is your real recovery time, and it is the only honest answer to "how long would we be down". ### Verified in Phase 12 Volume seed and benchmark · four index and query fixes with before/after plans · preflight passing in dev and failing correctly on a bad prod config · the audit finding and then clearing four real violations · `prune` and `slots:generate` commands the cron file references · **full regression across all 31 screens plus the field, partner and HIS APIs after every change**. ### Still open, and now the only things between this and a pilot 1. **The product name.** `CareCollect` is in the database name, every screen, the barcode documentation, the connector spec and the marketing site. Cheap to change today, expensive after the first hospital. 2. **Credentials** — WhatsApp/SMS, a payment gateway, a routing provider. One config value each; all three degrade visibly rather than failing silently, and preflight fails production without them. 3. **Redis and a read replica.** Both are config; `Db::replica()` already routes every report and export. 4. **The Android app** — source is complete, never compiled or signed. 5. **The HIS connector** — specified in `HIS-CONNECTOR.md`, needs a .NET developer.