Security-Hardened Form-to-Database Intake
A form-to-database pipeline handling special-category personal data, rebuilt after an independent security review returned a "do not activate as designed" verdict. The hardened design verifies webhook signatures over raw bytes, grants the automation layer execute permission on exactly one database function — no connection string, no table access, no schema privileges — and resolves submissions through opaque per-record invitation tokens, quarantining anything that cannot be matched safely rather than guessing. Idempotency and revision handling make replays and corrections safe, and durable-write-before-acknowledge means the platform retries rather than silently dropping. Proven end to end, and deliberately held inactive pending formal data-protection sign-off.
The problem
The business needs each child's information before a course starts — health, SEND, consent, and potentially safeguarding disclosures. That is special-category personal data about children under UK GDPR Article 9. A naive form-to-database integration here isn't merely insecure; it's unlawful.
What was built
An adversarial review of the first version returned "needs rework — do not activate as designed", with six findings. Each maps to a specific fix in the rebuild:
| Finding | Fix |
|---|---|
| Unsafe matching on email + cohort + child name | Opaque high-entropy token per booking, stored as a hash bound to the booking with an expiry; quarantine on no-match or ambiguity |
| Over-powered database credential | EXECUTE on exactly one function — no connection string, no table privileges, no DDL — with sanity-check queries proving the restriction |
| A whole data-lifecycle obligation, not a storage decision | A governance section: DPIA, lawful basis, Article 9 condition, retention across every system including logs and backups, privacy-notice update, processor terms, audited role-based access, separation of safeguarding data from teaching accommodations, and a subject-rights route with an SLA |
| Replay and corrections | Unique constraint on form + response token; same token with a different payload hash quarantines; a genuine correction becomes a revision with a supersedes pointer |
| Recovery from missed deliveries | 200 only after a durable write; 503 on transient database errors so the platform retries |
| Schema drift from question edits | Answers keyed by stable field refs, not question titles; form ID allowlisted |
The hard part
Two details that separate a design that looks secure from one that is.
HMAC verified over raw bytes. Verifying a re-serialised body changes the bytes — key order, whitespace, escaping — so you're checking a signature against something the sender never signed. It's the classic way HMAC verification breaks while still passing in testing.
Least privilege, demonstrated rather than asserted. The automation role holds EXECUTE on one function and nothing else, and the SQL ends with queries confirming that the role cannot read bookings, insert, or run DDL. All resolution logic lives inside the database function, which is what makes that grant sufficient.
What can be verified
- A real test submission flowed through end to end, authenticated first time and mapped 100% of fields, and stored correctly.
- Six review findings each mapped to an implemented fix.
- The system remains inactive by design.
The workflows
(select to enlarge)
(select to enlarge)
On numbers: every figure above is an artefact count or a measured technical value. No business-outcome metric, whether time saved, revenue or conversion, was captured on these engagements, so none is claimed.
On status: reflects repository evidence and platform backups, not a live systems check.