Dev log · 2026-07-07

Offline HRIS for a community police office 👮

A detour from plastron — exploring CRDT SQLite for offline merge before we add it to the main substrate (which is also meant to be offline capable). This proof-of-concept runs as one self-contained HTML file: no server, no network, encrypted at rest. The demo is skinned for a fictional Juárez Police Department; the use case is any small community policing unit that cannot depend on enterprise cloud HR.

Try the demo → Source on GitHub How-to guide
Proof of concept only. The app has not completed a Privacy Impact Assessment, threat/risk assessment, or formal security authorization. The Juárez roster is entirely synthetic. Do not load real personnel data until governance requirements (below) are satisfied.

A detour from plastron

The main line of work here is plastron — a polyglot reactive substrate (spreadsheet kernel, desktop shell, hosted at plastron.ca). This HRIS prototype is a detour: a self-contained experiment in CRDT SQLite (cr-sqlite) for offline-first data that must merge cleanly after a day of disconnected edits.

Plastron is hosted at plastron.ca with server-backed persistence today; the zero-server, offline-capable single-file build is still the goal (no OPFS in offline plastron yet). Before bolting CRDT SQLite into the full kernel, we wanted a smaller, adversarial test case: Protected B personnel rows, encrypted portable dumps, multi-operator convergence, and no network egress. Lessons from this build (changeset export, merge ritual, audit tables, CSP-safe inlined WASM) are intended to feed back into plastron as a future cr-sqlite integration, not to replace the spreadsheet model.

Think of offline HRIS as a field exercise for a capability plastron will need; the Juárez skin is just a narrative wrapper for the demo.

The problem

A community police office still has to plan staffing: which positions exist, who holds them, where the gaps are, what payroll pressure looks like. Enterprise HRIS products assume always-on connectivity, identity federation, and a vendor security boundary. A neighbourhood command post, a borrowed laptop, or a planning cell working from USB sticks does not get that luxury.

The design question was blunt: can several planners maintain one consistent personnel picture without a server, without the public internet, and without ever writing plaintext rows to disk?

The idea

Ship one file — a built index.html — that embeds React, SQLite, and the cr-sqlite CRDT extension as inlined WebAssembly. Operators double-click it. The live database exists only in memory. The only artifacts that touch disk are encrypted blobs: a morning truth.hrisdump and evening lastname.hrischanges deltas.

Content Security Policy sets connect-src 'none', so even if script execution were compromised, the page has no fetch, WebSocket, or beacon path off the machine. That is a deliberate trade: perfect for an air-gapped ritual, hostile to SaaS analytics.

The demo skin says Juárez PD — Zona Centro, Anapra, fictional badge numbers, invented salaries — because the original spreadsheet model came from a Canadian police HR layout and we wanted a clearly non-production narrative. The mechanics are jurisdiction-agnostic; only labels and seed data change.

The daily ritual

Think of it as git for staffing data, except the merge commit is encrypted and the coordinator may have no admin rights beyond a browser.

Morning — one truth

  1. The coordinator distributes truth-YYYYMMDD.hrisdump and the day's org passphrase (out of band — never bundled with the file).
  2. Each planner opens the HTML file locally, decrypts the truth, and passes the user gate (pick identity from a pre-provisioned operator list).
  3. The app records a session_open event and stamps a morning_db_version watermark.

Day — offline work

Evening — converge

  1. Each planner exports my changes → encrypted .hrischanges (not a full database dump).
  2. The coordinator opens the same HTML file, loads the morning truth, and uses Merge operator changes on the Data & Security screen to apply every delta.
  3. cr-sqlite merges in an order-independent way; conflicting edits converge under CRDT rules instead of last-writer-wins file replacement.
  4. Export the merged truth for tomorrow. Optional Node script merge-hris.mjs exists for automation; it is not required to operate.
CRDT solves data convergence. It does not automatically solve accountability — that is why change_event and session_log are first-class tables that sync with the same merge machinery.

Protected B and CSE cryptography

Personnel files (names, assignments, compensation) are not open data. In Government of Canada terms they typically land at Protected B: unauthorized disclosure could cause serious injury outside the national interest — see TBS Standard on Security Categorization (Appendix J) and the 2023 direction on personal information in the aggregate. A community police office handling officer PII faces the same injury model even when the formal framework is municipal privacy law rather than Treasury Board policy.

What the app satisfies technically

For encryption algorithms, CSE publishes ITSP.40.111 (Cryptographic algorithms for UNCLASSIFIED, Protected A, and Protected B information). The dump format uses the strongest practical stack from that document for file-at-rest:

Storage and transit expectations for sensitive electronic media are articulated in Directive on Security Management, Appendix B §B.2.3.4–§B.2.3.6 (protect data at rest, in transit, and on portable media commensurate with sensitivity; encrypt on public or risky networks). This app addresses at-rest for portable dumps; coordinators still need a procedure for USB handoff or approved encrypted channels.

What the app enforces in software

What still requires organizational "soft" requirements

Protected B is not a checkbox you get from AES alone. Before operational use with real officers, a jurisdiction would still need documented controls the browser cannot implement:

We split these explicitly in the project docs: in-app build requirements vs governance procedure requirements (the latter intentionally deferred until after the prototype stabilizes).

Why a browser tab at all?

A community office may not be allowed to install server software or database engines on managed PCs. A static HTML file with inlined WASM is weird, but it has properties enterprise stacks rarely offer together: offline-first, auditable merge semantics, ciphertext-only persistence, and zero network egress enforced by CSP. The GitHub Pages host (demo) is convenience for evaluation; the intended deployment is download-and-run locally on a controlled machine — file:// with no backend.

Status

The repository rheophile10/offline-Protected-B-HRIS includes Playwright user stories, an automated CRDT convergence test, and the screen-recorded walkthroughs below (also on the standalone how-to guide). It is an exploration of how far you can push personnel HRIS into a single encrypted, mergeable, offline artifact — not a production authorization, and not a fork of the product roadmap.

Next for plastron: absorb the CRDT SQLite patterns here into the substrate so cels, sheets, and shared state can survive offline edits and reconcile the same way. Next for this detour (if it continues): governance documentation for a real pilot, optional PIN-per-operator, jurisdiction-specific legal review.

How to use it

Each clip below is a Playwright test from tests/hris.spec.ts screen-recorded against the real built app with 800 ms slow-motion between actions so each step is easy to follow — the same videos shipped in the repo's how-to guide. User stories are documented in USER-STORIES.md; setup and build notes live in the README.

One file. Download the built index.html, double-click it, and everything below works offline — no server, no install, no admin rights. Export your changes before locking or closing; unsaved work is lost by design.

Start a session US-1

Open the app, load today's data behind a passphrase, pick your operator identity.

  1. Open the file — locked gate first.
  2. Keep Demo data, enter a passphrase, Start session.
  3. Pick an operator; dashboard shows 27 officers and staffing gaps.

Add an officer US-2

Add a new hire; roster and dashboard update with audit attribution.

  1. Officers → + Add officer, badge + name, Save.
  2. Headcount rises; change is attributed and audited.

Assign an officer to a position US-3

Keep staffing levels and vacancy deficits current.

  1. Assignments → + Assign officer, pick officer + position, Assign.
  2. Staffing views recompute automatically.

Query data & export encrypted CSV US-4

Run ad-hoc SQL; share figures without plaintext ever touching disk.

  1. SQL Console → write a query → Run.
  2. Export produces .csv.enc only — decrypt later from Data & Security.

Export changes & merge (no Node) US-5

The coordinator merges everyone's offline edits inside the same HTML file.

  1. Operator: Export my changes → encrypted .hrischanges.
  2. Coordinator: open morning truth → Merge operator changes → select deltas.
  3. Export merged truth for tomorrow. CRDT converges divergent edits.

Recruit & hire an applicant US-7

Run a hiring pipeline and convert a successful applicant into an officer.

  1. Recruitment kanban — add applicants, move through stages to Offer.
  2. Hire → creates officer record + active assignment; headcount updates.

Training & compliance US-8

See what's expiring and renew certifications.

  1. Compliance — Expired / Expiring / Valid counts and firearms-current ratio.
  2. Filter by KPI; Renew or + Record certification.

Leave & absence US-9

Track requests, approvals, and who's off today.

  1. Leave — on-leave today, pending, upcoming counts.
  2. + Request leave; Approve or Deny pending rows.

Workforce planning US-10

Project vacancies ahead of retirements.

  1. Planning — projected deficit at +1/+2/+3/+5-year horizons.
  2. Officers nearing pension milestone listed for hire-ahead decisions.

Audit trail US-11

See who changed what — attribution that survives the merge.

  1. Audit — change log (operator, entity, action) + session events.
  2. Logs are CRRs; they travel with each changeset into the merge.

Officer file US-12

Emergency contacts, performance reviews, and conduct in one place.

  1. Officer File → pick an officer.
  2. View contacts, reviews, conduct; add a review or update a disposition.

Equipment register US-13

Track who holds each firearm, vehicle, radio, or camera.

  1. Equipment — in-service / issued / available counts.
  2. Issue or Return assets; mark Maintenance or Retired.

Lock the session US-6

Wipe decrypted data from memory when you step away.

  1. Lock session — returns to gate, clears DB + key + identity from memory.
  2. Export changes first; unsaved work is lost by design.

Full standalone guide: rheophile10.github.io/offline-Protected-B-HRIS/how-to.html

References

More from the dev log