# CareFix deployment and pilot runbook

## 1. What is delivered

| Folder | Contents |
|---|---|
| `sql/01_control_db.sql` | Control database `CS_CAREFIX` (SQL Server 2017+). Run once on the CareFix server. |
| `sql/02_hospital_setup.sql` | Run by the DBA on **each** hospital HIS database. Creates `carefix_ro`, `carefix_rw`, the allow-list, the local change log and the two guarded procedures. |
| `sql/03_agent_channel.sql` | Agent tables on `CS_CAREFIX`. Run after `01`. |
| `sql/04_learning_and_integration.sql` | Learn-from-tickets and helpdesk tables. Run after `03`. |
| `sql/06_ai_usage.sql` | AI usage and cost table. Run after `04`. |
| `sql/05_demo_hospital.sql` | Optional demo hospital with generated data and six deliberate faults, for rehearsal. See `docs/DEMO.md`. |
| `backend/CareFix.sln` | Solution with three projects. |
| `backend/CareFix.Core` | Shared library: the SELECT-only guard and every SQL operation. Used by both the server and the agent, so both enforce the same rules. |
| `backend/CareFix.Api` | .NET 10 API, AI engine and safety layer. The built console is already in `wwwroot`. |
| `backend/CareFix.Agent` | Windows service installed at on-prem hospitals. Outbound HTTPS only. |
| `backend/CareFix.Tests` | xUnit tests for the safety-critical logic: the SELECT guard, patient-data masking, approval rules, risk tiering, CSV import and credential encryption. |
| `frontend` | React source for the console. `npm install && npm run build` writes into the API's `wwwroot`. |
| `kb-templates` | CSV templates for the data dictionary, relations, rules and playbooks. |

## 2. Build

The code was written without a .NET SDK in the build environment, so run the first compile on a developer machine:

```
cd backend
dotnet build CareFix.sln -c Release
dotnet test CareFix.sln
dotnet publish CareFix.Api -c Release -o ..\publish\server
dotnet publish CareFix.Agent -c Release -o ..\publish\agent
```

The agent publishes as one self-contained `CareFix.Agent.exe`; hospitals do not need .NET installed. Sign the exe with Caresoft's code-signing certificate before sending it to hospitals.

`dotnet test` must pass before the pilot: those tests cover the checks that stand between the AI and hospital data. Fix any compile warnings the team's analyzers raise as well. The front end is already built; rebuild it only after changing the React code.

## 3. CareFix server (Windows + IIS)

1. Install the .NET 10 Hosting Bundle.
2. Create database `CS_CAREFIX` and run `sql/01_control_db.sql`, `sql/03_agent_channel.sql`, `sql/04_learning_and_integration.sql` and `sql/06_ai_usage.sql` in that order.
3. Create a SQL login `carefix_app` with `db_datareader`, `db_datawriter` on `CS_CAREFIX` only.
4. Create an IIS site pointing at the publish folder, HTTPS only, with an app pool on "No Managed Code".
5. Set these as environment variables on the app pool (never in appsettings.json):

| Variable | Value |
|---|---|
| `CareFix__ControlDb` | Connection string for `CS_CAREFIX` using `carefix_app` |
| `CareFix__MasterKey` | Base64 of 32 random bytes, e.g. PowerShell: `[Convert]::ToBase64String((1..32 \| % {Get-Random -Max 256}) -as [byte[]])`. **Back this up.** Losing it makes every saved hospital password unreadable. |
| `CareFix__Jwt__SigningKey` | Random string of 48+ characters |
| `CareFix__Claude__ApiKey` | Caresoft's Anthropic API key |
| `CareFix__Claude__Model` | Default `claude-sonnet-5`; change without redeploying |
| `CareFix__Claude__MonthlyBudgetUsd` | Default 300. New AI work pauses when the month's spend reaches it. See `docs/COSTS.md`. |
| `CareFix__Claude__MaxUsdPerTicket` | Default 1.50. One ticket cannot run away. |
| `CareFix__Retention__*` | Days each kind of record is kept. Defaults: query results 90, query log 365, AI transcripts 180, row snapshots 180, uploaded ticket history 180. **Set these to match what you signed in the hospital addendum.** |
| `CareFix__Maintenance__HourUtc` | Nightly housekeeping hour, UTC. Default 19, about 00:30 IST. |
| `CareFix__Maintenance__SchemaRecaptureDays` | Recapture a hospital's schema when the snapshot is older than this. Default 30. |
| `CareFix__BootstrapAdmin__Username` / `__Password` | Used only on first start when no users exist. Remove after the first sign-in. |
| `CareFix__Integration__InboundKey` | Optional. Random 40+ character key your helpdesk uses to create tickets. Leave empty to keep the API off. |
| `CareFix__Integration__WebhookUrl` / `__WebhookSecret` | Optional. Where CareFix posts ticket status changes, and the secret used to sign them. See `docs/INTEGRATION.md`. |

6. Restrict the site to the office and VPN IP ranges at the firewall. Only outbound HTTPS to `api.anthropic.com` is needed from this server, plus SQL access to hospital databases for Direct channel hospitals.
7. Sign in as the bootstrap admin, change the password, turn on two-step sign-in, and create named users. Every Lead, Product Owner and Head should turn on two-step sign-in.

## 4. Adding a hospital

Choose the channel first. **Direct** is for cloud-hosted hospitals or any hospital whose SQL Server the CareFix server can reach (VPN or whitelisted IP). **On-prem agent** is for everyone else. No port is opened at the hospital, and the SQL passwords stay on the hospital's server.

Before a hospital goes live, the addendum in `docs/HOSPITAL_ADDENDUM.md` must be signed and the allow-list agreed with them.

### 4a. Direct

1. The hospital DBA (or Caresoft's DBA with permission) edits `sql/02_hospital_setup.sql`: replace `<HIS_DB>` and set two strong passwords. Run it.
2. Populate `carefix.CF_ALLOWLIST` with only the columns support is allowed to correct at this hospital. Anything not listed is refused by the database itself, even if CareFix is misconfigured.
3. In the console, go to **Hospitals**, add the hospital, save the connection with both logins, then **Test connection** and **Capture schema**.
4. Assign the hospital's engineers under **Users**.
5. Capture the schema again after every HIS upgrade at that hospital.

### 4b. On-prem agent

1. DBA steps 1 and 2 above, on the hospital's own SQL Server.
2. In **Hospitals**, set the channel to "On-prem agent", save, and click **Create agent key**. Copy the key; it is shown once.
3. Send the hospital IT team `CareFix.Agent.exe` and `docs/AGENT_INSTALL.md`, and share the key over a separate channel (phone or WhatsApp, not the same e-mail).
4. When the agent shows Online, click **Test through agent**, then **Capture schema**.

How the agent protects the hospital:
- It polls CareFix over outbound HTTPS every few seconds. Nothing connects in to the hospital.
- Its key is stored only as a hash on the server, and its SQL passwords are encrypted with Windows DPAPI for that machine.
- It re-checks every query with the same SELECT-only parser, accepts only seven known job types, and uses the hospital's own limits.
- Writes still go only through `carefix.usp_CF_UpdateRow` and the hospital's allow-list.
- A job not picked up within 45 seconds expires and can never run later. Query results are wiped from the server as soon as they are read.
- If the agent goes offline, CareFix says so immediately instead of waiting.

## 4c. Monitoring and housekeeping

- `GET /api/health` needs no login and returns 200 when the service and its database are up. Point your monitoring tool at it.
- **System** in the console (Admin only) shows open work, agents online and offline, fixes waiting for approval, helpdesk backlog, AI spend against budget, and when housekeeping last ran. It refreshes every 30 seconds. Watch this daily during the pilot.
- Housekeeping runs nightly: it clears query results, transcripts, snapshots and old learning uploads past their retention periods, and recaptures up to 25 stale schema snapshots (this is how a HIS upgrade gets picked up). **Run housekeeping now** on the System screen does the same on demand.
- `CF_AUDIT` is never purged automatically; the append-only trigger prevents it. Archiving it is a deliberate DBA task.
- Back up `CS_CAREFIX` nightly. It holds the audit trail and the snapshots a rollback needs.

## 5. Rehearsal on the demo hospital

Before any real hospital is connected, run the team through `docs/DEMO.md`. It creates a generated HIS-shaped database with six deliberate faults (duplicate pharmacy issue, receipt on the wrong patient, wrong discharge year, stuck OPD visit, stuck lab order, and an unapproved discount on a settled Tally-posted bill) and walks through diagnosis, approval, execution, verification and rollback, including the checks that should fail: approving your own ticket, running a High-risk fix with no consent file, and changing data between approval and execution.

## 6. Building the playbook library from past tickets

1. Export past database tickets to CSV with the columns in `kb-templates/ticket-history.csv`: `TicketNo, Module, IssueText, Resolution, SqlUsed`. The more tickets that include the actual SQL, the better the drafts.
2. In the console, open **Learn from tickets** and upload the file (up to 3,000 tickets per upload). Phone, Aadhaar, ABHA and e-mail patterns are masked on upload.
3. The AI groups the tickets into recurring problems. Each draft shows how many past tickets it covers, so review the biggest first.
4. A Lead, Head or Admin edits each draft and accepts or discards it. Only accepted drafts become playbooks the AI can use.

## 7. How a change is protected (defence in depth)

1. The AI has no write tool. It can only propose.
2. Every SELECT the AI writes is parsed with Microsoft's T-SQL parser: one plain SELECT, dbo tables known for that hospital, no cross-database access, no OPENROWSET. It then runs as `carefix_ro` with a 30-second timeout, a 5-second lock timeout and a 200-row cap.
3. Patient-identifying columns and phone, Aadhaar, ABHA and e-mail patterns are masked before the AI or the query log sees them.
4. The server re-reads every "before" value itself, blocks key columns and dictionary-locked columns, runs the lock checks, and sets the risk tier.
5. Approvals: Low needs a Lead; Medium needs a Lead and a Product Owner (two different people); High needs the Head and the hospital's consent file. The engineer who raised the ticket can never approve it.
6. At run time the before values are checked again, full rows are snapshotted, and all steps run in one transaction through `carefix.usp_CF_UpdateRow` as `carefix_rw`, which can do nothing else. The procedure re-checks the allow-list and the expected old value per row and writes the hospital's own change log.
7. The AI verifies the result. The Head can roll back while the values are still as CareFix left them.
8. `CF_AUDIT` is append-only.

## 8. Pilot checklist (Phase 1 to 2)

- [ ] Control DB and IIS site live, secrets set as environment variables, MasterKey backed up
- [ ] `dotnet test` green
- [ ] Named users created, approvers on two-step sign-in
- [ ] Demo hospital rehearsal completed by every engineer, lead and approver (`docs/DEMO.md`)
- [ ] Data dictionary imported for the top 50 to 80 tables, with `IsPii`, `Editable` and `RiskLevel`
- [ ] Relations and lock rules imported (Tally posted, bill settled, discharge finalised, claim submitted)
- [ ] Past ticket history uploaded under **Learn from tickets**, and the top 15 to 25 drafts reviewed
- [ ] Sanitised test copy of a HIS database connected, and 20 past tickets replayed against it
- [ ] Two pilot hospitals (ideally one Direct, one on-prem agent): scripts run, allow-list agreed with the hospital, schema captured
- [ ] Addendum signed with each pilot hospital (`docs/HOSPITAL_ADDENDUM.md`), authorised person named for consents
- [ ] AI budget set and understood (`docs/COSTS.md`)
- [ ] Weeks 1 to 2: engineers use diagnosis only; fixes run the old way for comparison
- [ ] Week 3 onward: Low and Medium fixes through CareFix; High fixes with consent

## 9. Not in this release

- Automatic agent updates. For now, replace `CareFix.Agent.exe` and restart the service; the console shows each agent's version.
- Helpdesk-specific connectors. The generic API and webhook in `docs/INTEGRATION.md` work with any helpdesk that can call an API or run an automation rule.
- Auto-approval of proven Low-risk playbooks (Phase 4). This needs a few months of pilot data showing those fixes never needed rollback.
- Row inserts and deletes. Wrong entries are cancelled through the table's status flag. This is deliberate.
- MySQL products (Phase 4).