Project Overview — Database

CivicPulse India runs on a single self-hosted Postgres instance — no separate search engine, cache, or queue service. The schema, migrations, and seed data all live in packages/db, a workspace package shared by the NestJS API (apps/api) and the background worker (apps/worker). Tables are defined with Drizzle ORM (drizzle-orm / drizzle-kit), and PostGIS geometry columns are modeled with small custom Drizzle column types rather than a separate geospatial library.

Schema files

Every schema file lives under packages/db/src/schema/. The barrel file, packages/db/src/schema/index.ts, re-exports all of them and is the authoritative list of what's actually wired up — drizzle.config.ts points drizzle-kit at this file, so any table defined outside it is invisible to migrations.

File	Tables it defines	What it covers
_types.ts	(no tables — shared column types)	Two custom Drizzle column types, pointGeom and multiPolygonGeom, mapped to PostGIS geometry(Point,4326) / geometry(MultiPolygon,4326). Read and written as WKT/EWKT text at the application layer. Not re-exported from index.ts — imported directly by the schema files that need it.
geography.ts	cities, wards, pincodes	Multi-city tenancy root (cities, one row per city — Mumbai is status: 'live', others are rollout/waitlist), BMC-style wards (with a nullable PostGIS geom boundary column, since ward boundary polygons aren't loaded yet), and pincodes, a pincode→ward lookup used as the routing fallback until ward polygons exist.
accountability.ts	jurisdictions, officials, departments, policeStations, channels, contacts	The "who's responsible" directory: ward-level jurisdiction/constituency mapping, elected and administrative officials (an office's name is stored but withheld from the public API by default via nameWithheld — the accountability model targets the office, not the individual), municipal departments per category, policeStations, per-category official reporting channels, and a flat multi-city contacts directory.
issues.ts	issues, issueStatusEvents, issueVotes, issueMedia	The core civic-issue table (issues), paired with its issueStatusEvents log — an append-only activity trail by convention (the schema comment describes the log itself this way; there's no DELETE-prevention mechanism on the issues row), per-voter "I'm affected too" issueVotes, and issueMedia metadata (the media blobs themselves live in object storage such as S3/R2; Postgres only holds metadata and GPS/EXIF).
reporters.ts	reporters	The citizen-reporter directory backing the reporters index, individual profile pages, and leaderboards.
events.ts	events, eventAttendees, nominations	Community events (clean-ups, plantation drives, awareness walks), their attendee rosters (eventAttendees doubles as the join table — there's no separate users table yet), and citizen-suggested nominations for future events.
admin.ts	admins, adminSessions, auditLog	The admin-operator identity realm, kept separate from any future citizen-facing users table. admins holds the four admin roles (see below); adminSessions follows the Lucia auth-library pattern of storing only a SHA-256 hash of the session token server-side; auditLog is an immutable trail written on every admin-console mutation.
admin-feed.ts	issueAdminState, flags, duplicateClusters	A mutable moderation overlay on top of the append-only issue tables — issueAdminState holds one row per issue (created lazily on first moderation action) for force-escalate/pause-SLA/redact-media/pin/hide flags without touching the public audit trail. flags holds user-reported content awaiting moderation review. duplicateClusters groups near-duplicate issue reports for merge review.
channels.ts	channelConfigs, channelMentions, messageTemplates, deliveryLogs, outboundConfig, routingKeywords	Inbound/outbound channel integration (WhatsApp, Instagram, X, and the native "Platform" channel): per-channel config and health counters (channelConfigs), inbound social mentions (channelMentions), outbound message templates (messageTemplates) and their send logs (deliveryLogs), a singleton outbound rate-limit policy (outboundConfig), and the keyword→category auto-routing taxonomy (routingKeywords).
admin-config.ts	featureFlags, slaConfig, scoringConfig, taxonomyItems, statusTransitions, cityBranding, reporterApplications, publicSubmissions, scoreOverrides, chainOverrides	The backing store for the admin Configuration screen: global/per-city featureFlags; a singleton slaConfig (SLA window + per-role escalation timers); a singleton scoringConfig (ward/official scoring weights and grade thresholds); taxonomyItems, a generic ordered chip-list store behind the four Configuration → Taxonomy lists (categories, priorities, flag reasons, event types); statusTransitions, the allowed issue-status transition matrix; cityBranding overrides; reporterApplications and publicSubmissions intake queues; scoreOverrides for manual score annotations; and chainOverrides for per-category responsibility-chain overrides.
admin-notifications.ts	broadcasts, announcements, pushTemplates, pushSubscriptions	broadcasts (web-push broadcast requests queued from the admin console), announcements (in-app banners shown to citizens per city), pushTemplates (reusable message templates), and pushSubscriptions (browser web-push registrations).
index.ts	(barrel — no tables of its own)	Re-exports every schema module above except _types.ts. This is the single file drizzle-kit reads to compute schema diffs.

A handful of type aliases live alongside their tables and are worth knowing when reading API code: AdminRole (admin.ts) is 'super_admin' | 'city_admin' | 'reviewer' | 'auditor'; FlagReason, FlagStatus, and FlagTargetType (admin-feed.ts) type the moderation-flag fields; ChannelKey and ConnectionStatus (channels.ts) type the channel-integration fields; and ApplicationStatus/SubmissionStatus/TaxonomyListKey (admin-config.ts) type the Configuration-screen intake tables.

Migrations

Migrations are managed by drizzle-kit, configured in packages/db/drizzle.config.ts: it points at ./src/schema/index.ts as the schema source, writes generated SQL to ./drizzle, and connects using DATABASE_URL_DIRECT (falling back to DATABASE_URL) since schema changes need a direct/session connection rather than a pooled one. As of this writing the drizzle/ directory contains eleven migrations (0000_cooing_vulture.sql through 0010_chemical_justice.sql), each with a corresponding snapshot in drizzle/meta/.

The day-to-day loop, run from the repo root:

# 1. Edit table definitions under packages/db/src/schema/*.ts

# 2. Generate a new SQL migration from the schema diff
pnpm db:generate

# 3. Review the generated SQL under packages/db/drizzle/ — drizzle-kit
#    can't always distinguish a rename from a drop+add, so check it before applying.

# 4. Apply pending migrations to the target database
pnpm db:migrate

These map to workspace-filtered scripts defined in the root package.json:

"db:generate": "pnpm --filter @civicpulse/db generate",
"db:migrate": "pnpm --filter @civicpulse/db migrate"

db:generate runs drizzle-kit generate directly. db:migrate runs packages/db/src/migrate.ts via tsx, which does more than call the Drizzle migrator:

1. Loads .env from the repo root.
2. If invoked with --reset (see below), drops and recreates the public schema and drops the drizzle schema (Drizzle's own migration-tracking table lives there, separate from public — it has to be dropped too, or the migrator sees migrations as already applied and skips recreating the tables that DROP SCHEMA public just removed).
3. Idempotently creates the Postgres extensions the schema depends on: postgis, pg_trgm, btree_gin, pgcrypto, unaccent. It also attempts pg_cron, but tolerates that extension being unavailable — pg_cron requires shared_preload_libraries to be set at the Postgres server level, so local dev environments without it fall back to the worker process scheduling jobs itself.
4. Runs Drizzle's migrate() against the packages/db/drizzle folder.

drizzle.config.ts also sets extensionsFilters: ['postgis'], so drizzle-kit doesn't try to manage (or drop) the PostGIS extension's own internal tables/views when diffing the schema.

Resetting a local database

pnpm db:reset

This runs tsx src/migrate.ts --reset && tsx src/seed.ts — it drops both schemas, reapplies every migration from scratch, and then reseeds. It's the standard "wipe and start over" command for local development; it is destructive and should never be pointed at a database anyone depends on.

Other database scripts

"db:seed": "pnpm --filter @civicpulse/db seed",
"db:studio": "pnpm --filter @civicpulse/db studio",
"db:bootstrap-admin": "pnpm --filter @civicpulse/db bootstrap-admin"

• pnpm db:studio launches drizzle-kit studio, a browser-based table viewer for the connected database.
• pnpm db:bootstrap-admin runs packages/db/src/bootstrap-admin.ts, a one-time, idempotent script that creates the first super_admin account from SUPERADMIN_EMAIL / SUPERADMIN_PASSWORD / SUPERADMIN_NAME environment variables (password hashed with argon2). It does nothing if an admin with that email already exists, so it's safe to re-run. This is the only way an admins row is created outside of the admin console itself — seed.ts never inserts into admins.

Seed data

pnpm db:seed

runs packages/db/src/seed.ts via tsx. The script is idempotent: every insert uses Drizzle's .onConflictDoNothing(), so re-running it against an already-seeded database is a no-op rather than a duplicate-key error. It reads from two kinds of sources:

• Vendored reference-data files in packages/db/seed-data/ — a mix of JSON and CSV: accountability-data.json (jurisdictions/officials/departments/police stations across multiple cities), mumbai_ward_offices_pincodes.csv, civic_channels_10_cities.csv, india_civic_contacts_with_ward_pincode_min.csv, plus ward-scoring and ward-code-crosswalk JSON files (ward24-scores.json, rwards-leaderboard.json, ward-shortcode-crosswalk.json) that reconcile the frontend's ward-naming convention with the accountability dataset's own ward codes.
• Frontend-ported demo content — frontend-issues.json, frontend-reporters.json, frontend-events.json, frontend-nominations.json. Per a comment in seed.ts, these are vendored straight from the original frontend's static TypeScript data modules (via packages/db/scripts/extract-frontend-seed.ts), so the first live API returned the exact same issues/reporters/events the static frontend used to render, just served from Postgres instead of a bundled JS module.

seed.ts builds its rows in memory (deriving cities from the union of every source file, matching wards across the CSV and JSON inputs by a normalized ward code, parsing lat/lon out of strings like "19.0596° N, 72.8295° E" into PostGIS WKT), then inserts them in a fixed order inside main(). As of this writing, the tables seed.ts populates are: cities, wards, pincodes, jurisdictions, officials, departments, police_stations, channels, contacts, reporters, issues, issue_status_events, events, event_attendees, nominations, flags, channel_configs, message_templates, routing_keywords, feature_flags, sla_config, scoring_config, taxonomy_items, status_transitions, city_branding, push_templates, and announcements.

Honest placeholders: tables seeded empty on purpose

Not every table gets rows. A recurring convention in this codebase — referred to here as an honest placeholder — is a table that is fully and correctly shaped for a feature, but is deliberately left at zero rows because the real write path that would populate it doesn't exist yet. The alternative (backfilling it with invented rows so a dashboard "looks populated") would violate the project's house rule against ever fabricating operational data, so the seed simply leaves these empty and any count against them is honestly 0 rather than a plausible-looking lie:

• reporterApplications (admin-config.ts) — apply-to-report submissions. Starts empty because there's no public "apply to be a reporter" write flow yet.
• publicSubmissions (admin-config.ts) — citizen-suggested corrections to ward/official data. Same reason: no citizen submission entry point exists yet.
• broadcasts (admin-notifications.ts) — web-push broadcast requests from the admin console. No broadcast has ever actually been sent, so this stays empty; note also that its status column can only ever be 'queued' or 'scheduled' in the current code, never 'sent', since no delivery pipeline exists to set that value.
• pushSubscriptions (admin-notifications.ts) — browser web-push registrations. The public site has no service worker and never prompts for push permission yet, so this table is correctly shaped for future use but genuinely has nothing to hold today.

A related but distinct convention shows up in some admin-config.ts tables that are seeded with rows — featureFlags and scoringConfig are populated, editable through the admin Configuration screen, and even audited on change, but as of this writing the application's runtime logic doesn't read them yet (feature flags don't gate any behavior, and the scoring algorithm is still hardcoded elsewhere in the API). These are "stored but not yet consumed," which is a different situation from an empty honest-placeholder table — the data is real and admin-editable, it just isn't wired into the code path that would act on it.

Finally, some tables are simply never touched by seed.ts at all because they're naturally populated by application activity rather than reference data — issueVotes, issueMedia, issueAdminState, duplicateClusters, channelMentions, deliveryLogs, outboundConfig, adminSessions, auditLog, scoreOverrides, chainOverrides (per-category responsibility-chain overrides — the chain-of-responsibility logic is still hardcoded elsewhere in the API), and admins (created separately via pnpm db:bootstrap-admin, see above) all start out empty after a fresh pnpm db:seed and fill in as the system is actually used.