Project 01
CaseTrack
92% of nonprofits run on budgets under $1M and manage client data in spreadsheets. Enterprise platforms like Bonterra charge $50-150 per user per month, pricing that excludes exactly the organizations that need help most. CaseTrack is the free, open-source alternative: a full case management system built in 24 hours, with seven AI features that automate the most time-consuming parts of casework.
The data model is multi-tenant by design. Data isolation is enforced by Supabase RLS at the database layer, not the application layer. The query is rejected by Postgres before it reaches the app, so no buggy middleware can cause a data leak.
Semantic search runs on pgvector with Gemini text-embedding-004 (768 dimensions), indexed with HNSW. Staff can query "clients who mentioned housing instability" across years of free-text case notes and get results in milliseconds. The lookup runs on a request, no background job, no indexing delay.
Voice-to-structured case notes uses ElevenLabs Scribe v1 for transcription and Gemini 2.5 Flash for structuring the output into a typed object: summary, action items, risk level, and follow-up date. ElevenLabs TTS reads case notes and summaries aloud in 99 languages. The audio blob is cached after the first call so play, pause, rewind, and seek cost zero additional API credits.
The audit log never stores raw PII. Every before/after record state is stored as a SHA-256 hash of the serialized JSON. AI routes run a PII masking utility before any data reaches Gemini. Every API route is validated with Zod using safeParse, not parse, so validation errors return typed 400 responses rather than unhandled exceptions.
Seven AI features for under $20/month. The cost constraint forced real architecture decisions: 3-key Gemini rotation, audio caching at the blob level, and embeddings generated at write time rather than query time.
What I Learned
- RLS is not optional for multi-tenant systemsApplication-layer guards can be bypassed by a missing middleware, a forgotten check, or a future refactor that adds a new route. RLS enforced at the database layer means no application code path can accidentally expose cross-org data. The constraint is structural, not conditional.
- HNSW vs IVFFlat for approximate nearest-neighbor searchIVFFlat divides the vector space into clusters at build time and searches only the relevant clusters at query time. Fast at million-scale, but requires specifying the number of clusters upfront and degrades when data distribution shifts. HNSW builds a layered navigable graph with better recall at moderate scale, no cluster count to tune, and no full rebuild needed when distribution changes.
- Free-tier multiplication via API key rotationThree Gemini API keys = 3M free tokens/day. The rotation logic is about 15 lines. This pattern works for any quota-limited API and significantly delays the need for a paid plan. The tricky part is backpressure: you need to detect a rate-limit response and retry on the next key, not the same one.
- Audit logs should hash, not storeStoring actual before/after field values in an audit log makes the log itself a PII liability. A breach of the audit log is a breach of the data. SHA-256 hashing the serialized record gives tamper-evidence and change verification without any recoverable user data in the log table.
Tech Stack