Case study
Public IDs: Enumerable but Safe
Every person gets a readable, org-scoped ID, and the attendance-roster leak path was closed before any code was exposed to non-staff.
- Project
- Online tutoring platform
- Industry
- EdTech / SaaS
- Role
- Allocator, visibility rule, rollout
- Python
- FastAPI
- SQLAlchemy event hooks
- Alembic
- PostgreSQL
- Next.js
- TypeScript
The context
Human-readable, org-scoped IDs for every person (STU-, TUT-, PAR-, EMP-), searchable by staff and visible to end users, with a strict rule on who may see which code.
The problem
Staff identified people by name or internal numeric ID, which made support lookups slow and ambiguous.
Once codes became sequential per organisation and prefix, so they could be remembered and dictated, they also became enumerable. Exposed carelessly, a convenience would have become a data-harvesting tool.
My responsibility
- Designed the allocator and constraints
- Designed the visibility rule
- Rolled the IDs out to emails and dashboards
Architecture
- Membership row insertedafter_insert hook on the user-organisation table
- Sequential allocatorCounter per (org, prefix, last issued)
- user_public_idsUNIQUE(org_id, public_id)
- Visibility check, per recipientStaff see all; anyone else sees their own row and linked children only
Key decisions
Prefix frozen at issuance
A student later hired as staff keeps STU-.
Sequential per (org, prefix)
This replaced a random draw and made global uniqueness impossible by design, so the constraint narrowed to UNIQUE(org_id, public_id), after verifying no call site needed global uniqueness.
Issue from a hook, not 11+ call sites
Four of those sites attach an existing user to a new org, which a per-site helper structurally cannot reach. An after_insert hook on the membership table catches every path.
Reject bad input, never "correct" it
The alphabet excludes ambiguous characters, so there is no legal remap target, and guessing could navigate to the wrong person.
Per-recipient rendering
One post-class email fanned out to the tutor, admins, every student, and every parent with a full attendance roster. The work was split into load-once and render-per-recipient, clamped to each recipient's visible set.
Technical challenges
1.An irreversible data migration
Renumbering existing rows can't be undone. The downgrade() is a loud, logged no-op, because raising broke unrelated round-trip migration tests that walk every intermediate downgrade.
2.Hook registration under test
The hook had to register at module import, because the event setup is skipped under the test runner.
3.Timing
A code minted by the hook doesn't exist until the membership row commits, so emails composed earlier rendered None. A test now asserts the real allocated code reaches the email.
4.Security review of the leak path
Confirmed there is no self-service route to create a parent-child link, so "claim a stranger's child to harvest their code" has no entry point.
Results
- Attendance-roster leak path closed before any code was exposed to non-staff
- Staff search, command palette, invoices (web and PDF), and account pages all resolve the codes
- Existing users renumbered in a single migration: [X rows], with 0 collisions
- Support lookup by ID instead of name: [X% faster / X fewer ambiguous matches]
What I learned
- Convenience identifiers are access-control surfaces. Decide who may see them first, then where to put them.
- Put invariants where every path must pass, such as a DB hook, not at call sites.
- Make irreversible migrations loud.