Databases — MongoDB + Postgres

Municipal Permit Index Benchmark

Generate 300k synthetic permit applications with district, category, submittedAt, riskScore, and status; seed identical records into Postgres and Mongo. Measure equality, range, and sorted queries before/after indexing, report rows examined versus returned and wall time, and explain one case where ignoring the index is correct.

Festival Schedule Index Design

List six real festival-schedule queries: events by day, venue timetable, accessibility filter, performer lookup, text search, and upcoming changes. Break down equality/sort/range needs, design the smallest supporting index set, justify every index, and record one deliberately omitted index.

Clinic Waitlist Slow-Query Postmortem

Diagnose three slow clinic-waitlist reads: next patient per service, overdue triage cases, and clinician queue. Capture each plan, state a hypothesis, apply one fix, and compare examined/returned rows and wall time in Postgres and Mongo; leave one acceptable query unchanged and defend that choice.

Field Survey Archive on Mongo (in progress)

Build a Mongo archive for field expeditions, sites, observations, specimens, and review notes with deliberate embedding/reference choices, supporting indexes, one aggregation, and one integrity-sensitive transaction. Finish and review it on Day 74.

Field Survey + Collection APIs (checkpoint)

Notes API + Blog backend, both on real Mongo Atlas, indexed, with an aggregation and a transaction. Next: give a relational database the same treatment.

Volunteer Registry Table, End to End

Add a real Postgres volunteer registry to the Phase 5 Express service with Drizzle: schema, configuration, migration, db singleton, and validated create/list endpoints. Print the generated SQL and explain the uniqueness, status, and availability constraints.

Equipment Booking Schema in Drizzle

Model equipment, locations, borrowers, bookings, bookingItems, and maintenance holds relationally. Include an enum status, non-overlap/inventory constraints you can defend, explicit cascade rules, and indexes for availability and history. Derive insert types and Zod schemas from tables.

Accessible Venue Feed, Two Query Styles

Build an accessible-venue feed twice against a venue/access-feature/inspection schema: once with relational queries and once with explicit joins plus reshape. Inspect SQL and plans, choose one implementation with evidence, then replace offset pagination with a stable `(verifiedAt, id)` cursor.

Seed-Exchange Ledger: Migrated and Transactional

Create versioned migrations for seed varieties, members, offers, and exchanges; add a column through expand/backfill/contract and write an idempotent seed. Complete an exchange plus inventory decrement transactionally, prove rollback by throwing halfway, and map a duplicate offer key to 409.

Mutual-Aid Inventory Lineage API — DEPLOYED

Build a Postgres/Drizzle API that traces donated goods from intake batch through storage, reservation, handoff, and adjustment. Support full-text search, faceted filters, and cursor pagination; make every multi-record handoff transactional, enforce invariants in the database, seed realistic batch histories, test against a real database, and defend the relational model.

• Claims link to sources and tags through explicit join tables; every response preserves provenance.
• Search, faceted filters, keyset pagination, deliberate indexes, and an EXPLAIN ANALYZE plan you can explain.
• Versioned Drizzle migrations, an idempotent seed script, and integration tests against a real Postgres.
• One transactional ingest that is provably all-or-nothing, and constraint violations mapped to correct HTTP status codes.