Skip to main content

Admin Console — Implementation Plan

:::note Original plan, not a record of what was built This page reproduces the admin console implementation plan as it was written before any admin console code existed. It documents the architecture decisions, data model, milestone breakdown, and open questions that shaped the build going in. Real implementation work sometimes diverges from a plan written up front — new information surfaces, defaults get revisited, sequencing shifts. This page does not track any of that. For what was actually built, in what order, and where it differs from what follows, see the Build Log. :::

This plan turns two source documents into a buildable, milestoned roadmap against CivicPulse's actual stack: NestJS + Postgres/Drizzle (apps/api, packages/db) plus a new Vite/React/TypeScript frontend styled with CSS Modules. The two source documents — a product spec covering the admin console's 12 functional sections, and a visual design prototype (a static HTML/JS mockup covering the same 12 screens with mock data) — are referenced throughout. The product spec is now part of this docs site — see the Spec — but the visual-design-prototype HTML bundle is not.

The admin console's milestones are numbered A0–A4, distinct from the public site's M0/M1/M2 track. The two tracks proceed in parallel against the same backend.

1. Research inputs​

Before writing this plan, three research passes were done: a full read of the 2,611-line design prototype, a full read of an earlier prototype of this project — referred to throughout this plan as "the old repo," a separate DynamoDB/Express codebase that predates this monorepo — and a full read of that old repo's channel integration code (X/Instagram/WhatsApp). The findings below materially shaped the plan.

The design prototype​

The design bundle matches the product spec's 12 sections almost exactly — the same information architecture and the same recurring screen archetypes: KPI dashboard, review queue with a detail drawer, data table with CRUD (create/read/update/delete) actions, a configuration form with a mandatory audit note, an integration card, an issue inspector, an audit timeline, and an analytics dashboard. It is a dense, dark-mode-first, utilitarian console — a genuinely different visual system from the public site (CSS custom-property theming with light/dark support, versus the public site's hardcoded light-only palette).

Critically, the prototype is pure mock data with zero live wiring — every screen is driven by conditionally-shown static markup, with no fetch() call anywhere in it.

The old repo's admin panel​

The old repo's admin panel turned out to be a checklist to work from, not a codebase to port. It actually contained three admin surfaces of very different quality:

  1. A real, tested /admin single-page app with its own JWT auth, its own DynamoDB admins table, and 20+ endpoints — flags, site config, donation config plus a donor wall, ward/officials CRUD with CSV import/export, a submission-review queue, accountability-entity CRUD, admin account management, and push broadcast.
  2. A second, broken prototype embedded inside the citizen-facing app (AdminPanel.jsx / AccountabilityAdmin.jsx) — its role check trusted the client with no server-side verification, its "moderation actions" were pure setState calls with no fetch() behind them, and one component read a localStorage key that nothing else in the repo ever set.
  3. A third, orphaned-but-real layer: routes/index.js contained working, tested, RBAC-gated (see §2 for what RBAC means here) moderation endpoints — bulk status update, issue assignment, reopen-request approval, user-role change — that no frontend in that repo ever called. This is, ironically, the most "shovel-ready" reference for issue-moderation endpoint shapes precisely because it was never wired to a UI that could go stale.

None of the implementation survives the stack change — the old repo used DynamoDB single-table primary/sort keys, flat-JSON-file persistence for two of its biggest CRUD surfaces, and Express/jsonwebtoken auth, none of which carries over to this project's Postgres/Drizzle/NestJS stack. What is genuinely reusable is the feature checklist and endpoint taxonomy — this plan's milestone breakdown leans on it.

The X (Twitter) API integration specifically​

The old repo's backend/services/xService.js is a real, working X API v2 integration: it polls GET https://api.twitter.com/2/tweets/search/recent with a query=@{handle} parameter and a Bearer token, falls back to five hardcoded demo mentions when unconfigured or when the API returns 401/402/403, and is exposed via GET /api/x/mentions (public) and POST /api/x/mentions/:tweetId/convert (Bearer-auth, creates an issue). This is a poll/search model, not a webhook — X's free/basic API tier has no mentions webhook, so "connecting X" in the real build means storing a bearer token and handle and running a scheduled poll, not registering a webhook URL.

Category and city detection in the old repo live in a shared utility, textDetection.js — pure, dependency-free, framework-agnostic functions, verified directly by reading the file. This is described in the plan as the single cleanest portable asset in the entire old repo.

Everything else channel-related is less reusable:

  • igService.js and whatsappService.js are independently shaped — there is no common interface across the three channel services in the old repo.
  • configService.js does not store channel credentials — it holds feature flags and site config, a different concern.
  • messageService.js is not a channel-message layer — it implements per-issue comment threads.

In other words, there is no existing credential-storage layer or message-normalization layer in the old repo at all. Both need to be designed fresh. The full integration design is in §5.

2. Architecture decisions​

  • Separate frontend app, same backend. A new apps/admin package: Vite + React + TypeScript + CSS Modules + React Router, matching apps/web's existing conventions exactly (path aliases, ESLint/Prettier, lazy-loaded routes). It gets its own package.json, its own dev port, and eventually its own deploy target (admin.civicpulseindia.com). It does not share UI components with apps/web, since the design bundle confirmed a genuinely different design system — but it can share DTOs (data transfer objects) derived from packages/db where useful.
  • Same NestJS app, new namespace. All admin endpoints live inside the existing apps/api NestJS application under @Controller('admin/...'), giving every admin route an /api/admin/* prefix. There is no second NestJS instance and no second deploy for the API — this matches the product spec's requirement that the admin console be "backed by the same backend under an /admin API namespace."
  • Admin auth is entirely new. As of this plan, there is no authentication of any kind in the codebase — the earlier public-site milestones shipped a read-only API with no login anywhere, and Lucia (a session-based auth library chosen earlier in the project's backend architecture) had never actually been implemented. That makes admin milestone A0 the first real authentication implementation in this project. The plan's recommendation is to use Lucia with a separate admins principal table, distinct from any future citizen users table — same session mechanism, isolated identity. This satisfies the product spec's "separate admin authentication" requirement without inventing a second auth stack the way the old repo did (a separate JWT secret and separate middleware, duplicated across two route files). Two-factor authentication (2FA) is planned via TOTP (time-based one-time passwords, e.g. via otplib or an equivalent library), with the secret stored encrypted on the admins row and checked after password verification.
  • Role-based access control (RBAC) via a NestJS guard and decorator — @Roles('super-admin', 'city-admin', 'reviewer', 'auditor') — rather than per-route hand-rolled checks. The old repo's biggest auth wart was duplicating requireAdmin/requireSuperAdmin checks across routes/admin.js and routes/notifications.js; this plan avoids repeating that.
  • Every mutation is audited. A single audit_log table (actor, action, target, before/after JSON, note, timestamp) is written to by a shared AuditService.record(...) call from every admin mutation, rather than ad hoc logging scattered per module. The design prototype's mandatory "audit note required to save" pattern on Configuration screens is meant to be enforced server-side too — config mutations without a note should be rejected — and, per a gap flagged during design review, that same discipline is extended to Integrations → routing rules, which the prototype otherwise lets you edit with no note at all.
  • City scoping is a first-class concern. Every admin table that varies per city gets a cityId column, and every list endpoint takes a cityId filter — mirroring the pattern already established in the public site's geography and scores modules.

3. Data model additions (packages/db)​

The tables below are new Drizzle schema, grouped by the milestone that introduces them. Column lists here describe the intended shape of each table, not exact DDL (data definition language) — precise types, defaults, and constraints are a build-time detail worked out when each milestone is actually implemented.

A0 — foundations​

  • admins — id, email, passwordHash (argon2), name, role enum, cityScope (nullable array; null means all-cities access), totpSecret (nullable, encrypted), active, lastLoginAt, createdAt, createdBy.
  • admin_sessions — managed by Lucia.
  • audit_log — id, actorId (nullable — system-initiated actions have no human actor), actorType (admin or system), action, targetType, targetId, before (JSON), after (JSON), note, createdAt. Indexed on (targetType, targetId) and on createdAt to support the audit-timeline screen.

A1 — feed moderation​

  • issue_admin_state — an overlay table keyed by issueId: forced (bool), slaPaused (bool), closed (bool), hidden (bool), pinned (bool), redactedMediaIds (array). This mirrors the design's issueMeta overlay pattern — mutable admin state is kept in a separate table from the append-only issue and issue_status_events tables that already exist from the public site's second milestone, preserving that append-only invariant.
  • flags — id, targetType (issue | comment | media | reporter), targetId, reason enum, reportedBy, detail, status (open | dismissed | warned | removed | banned), resolvedBy, resolvedAt.
  • issue_comments — does not exist yet at all. The old repo's per-issue comment thread (messageService.js) has no analog in the current schema; this table would only be added if comment moderation is in scope for A1 — flagged as an open decision, see §6, item 6.
  • duplicate_clusters — id, cityId, category, ward, windowStart/windowEnd, memberIssueIds (array), canonicalIssueId (nullable, set when issues are merged).

A2 — configuration & reference data​

  • feature_flags — key, cityId (nullable = global), value (JSON or bool), updatedBy, updatedAt.
  • sla_config — cityId, windowHours (default 48 — the target turnaround time for an issue, i.e. its service-level agreement, or SLA), perStepTimers (JSON), autoEscalate (bool), businessHours (JSON), holidays (array), notificationTriggers (array).
  • scoring_config — cityId, weights (JSON — volume/speed/resolution/recurrence, must sum to 100; the design prototype only validates this client-side, so this plan validates it server-side too), gradeThresholds (JSON), refreshCadence enum.
  • taxonomy_categories / taxonomy_priorities / flag_reasons / event_types — small lookup tables that use stable IDs, not bare strings. The design prototype's chip-list editors are string-array-only with no ID — flagged as a design gap worth not replicating, since renaming a category string in a string-only scheme would silently orphan every issue that referenced the old string.
  • status_transitions — (fromStatus, toStatus, allowed) — a real finite-state-machine table the backend enforces on every status-change mutation, not just a UI-level decoration.
  • city_branding — cityId, name, subdomain, helpline, donationUpiId, landingHeadline.
  • reporter_applications — id, name, beat, pitch, idType, idDocumentRef (an object-storage pointer, not a blob column — the old repo's pattern of storing a QR code as base64 in a column is explicitly called out as not worth repeating), status, reviewedBy, reviewedAt.
  • public_submissions — citizen-suggested ward/official corrections: id, submitterId (nullable — submissions can be anonymous), type, detail (JSON), status, reviewedBy, reviewedAt.

A3 — integrations (channels)​

Full design in §5.

  • channel_configs — channel enum (whatsapp | instagram | x | platform), cityId, enabled, demoMode, credentials (JSON, encrypted at rest), connectionStatus enum (connected | degraded | disconnected | needs-reauth — the design prototype never models anything but "connected," a gap this table is meant to fix), lastCheckedAt, lastError, lastReceivedAt, errorRateRolling.
  • channel_mentions — id, channel, externalId (unique per channel — this fixes a copy-paste bug in the old repo, where Instagram's convert route stored its media ID in a field literally named tweetId), rawPayload (JSON), text, fromHandle, categoryHint, cityHint, mediaRefs (JSON), convertedIssueId (nullable foreign key), createdAt.
  • message_templates — key, channel, body (with {{placeholder}} tokens), active.
  • delivery_logs — id, channel, toMasked, templateKey, status (sent | failed | retrying), createdAt.
  • routing_rules — category-to-office mapping overrides. The base mapping already exists as domain constants (ROLE_DEFS and related) from the public site's second milestone — this table holds only per-city overrides, not a full duplicate of that mapping.

A4 — notifications, analytics, ops​

  • broadcasts — audience (all or a specific cityId), message, scheduledAt (nullable), sentAt, status.
  • announcements — cityId, title, body, active.
  • push_subscriptions — VAPID (Voluntary Application Server Identification — the standard used to authenticate web-push subscriptions) records. The old repo's implementation here is considered genuinely reusable, since browser push isn't stack-specific.
  • No new table is planned for the "queue monitor" screen — it is meant to surface whatever job-queue table the core backend eventually builds for SKIP LOCKED-based background jobs (a Postgres row-locking pattern used to implement a simple job queue without external infrastructure). This is a cross-dependency rather than new admin-specific schema — see §6, item 7.

4. Milestones​

Each admin milestone (A0–A4) is meant to end with the same discipline as the public site's milestones: build, smoke-test every endpoint directly, then do an exhaustive browser verification pass.

A0 — Foundations: auth, RBAC, audit, admin shell​

  • Schema: admins, admin_sessions, audit_log.

  • NestJS: Lucia wiring (the first real authentication implementation in this codebase), AdminAuthGuard, the @Roles() decorator, AuditService, and three endpoints:

    POST /api/admin/auth/login
    GET  /api/admin/auth/me
    POST /api/admin/auth/logout

  • Bootstrap: a seed script for the first super-admin account, mirroring the old repo's seed-superadmin.js — idempotent and driven by environment variables.

  • Frontend: the apps/admin scaffold (Vite/TypeScript/CSS Modules/React Router), a dark-first theme system (a data-cp-theme attribute driving CSS custom properties, per the design bundle), and a set of shared atoms built once and reused across nearly every screen: PageHeader, Sidebar, a TopBar with a city switcher/search/notifications/profile menu, StatusPill, GradeBadge, ToggleSwitch, DataTable, ReviewQueueRow, DetailDrawer. The design review confirmed these atoms recur identically on nearly every screen, so building them first was expected to pay off immediately.

  • Also in scope: the login page, a protected-route wrapper, and a roles-and-permissions matrix screen (mostly static per the design — 4 roles × 7 categories, read-only in the prototype).

A1 — Feed Management & Review​

  • Schema: issue_admin_state, flags, duplicate_clusters (plus issue_comments, pending the open decision in §6).
  • Endpoints, built on top of the public site's existing issues domain: status change (validated against status_transitions), force-escalate, pause-SLA, reassign, close/reopen, unpublish/hide, pin/feature, and a media-redact toggle — every one of these mutations routed through AuditService.
  • A flags queue (dismiss/warn/remove/ban) and duplicate-cluster merge-to-canonical.
  • Explicit cross-dependency: the design's "Intake triage" queue has nothing to show until issues can arrive un-reviewed — that only becomes real once the public site's write paths (its third milestone) or admin milestone A3's channel ingestion exists. The plan calls for building the triage UI and endpoints now, against A3's channel_mentions turning into provisional issues, but not expecting a populated queue until one of those two dependencies lands.
  • Frontend: the Feed Review screen with its five sub-tabs, and the shared detail drawer in both its triage-edit and issue-inspector modes (chain/activity/media tabs, matching the design).

A2 — Configuration, Geography & Reference, Reporters (applications), Admins & Access​

  • Schema: feature_flags, sla_config, scoring_config, the taxonomy tables, status_transitions, city_branding, reporter_applications, public_submissions.
  • All configuration endpoints require an audit note server-side, not just as client-side UI discipline.
  • Geography: cities CRUD (the design shows this as read-only despite the spec calling for create/manage — this plan builds real CRUD, resolving that disagreement in the spec's favor), wards CRUD with GeoJSON boundary upload (feeding the PostGIS-based routing already planned in the core backend architecture), pincode-to-ward mapping, and a public-submissions review queue.
  • Officials & Accountability: real CRUD (the design shows read-only tables with toggles only — this plan builds full CRUD per the spec, since the public site's second milestone already has the read-side domain model to extend), responsibility-chain overrides (currently just a badge in the design with no edit UI — this plan adds an actual override editor), department/police-station CRUD, a score-recompute trigger, and an override/annotation/dispute UI (present in the spec, absent from the design — built here per spec).
  • Reporters: an applications/KYC (know-your-customer) queue, where approving an application grants a row in the existing reporters table plus a verified badge, and reporter management (suspend/reactivate) on top of that same table.
  • Admins & Access: admin account CRUD with an invite flow, a roles matrix (read-only, per the design), and a citizen-user view (stubbed/read-only until citizen accounts exist from the public site's third milestone).

A3 — Integrations (X / Instagram / WhatsApp / Platform)​

Full design in §5. Summary of deliverables:

  • Schema: channel_configs, channel_mentions, message_templates, delivery_logs, routing_rules.

  • Port textDetection.js verbatim to TypeScript, at apps/api/src/channels/text-detection.ts, remapped to this project's actual six categories (Infrastructure / Garbage & filth / Traffic / Public transport / Safety / Corruption, from the existing CATS/CATC domain constants) — the old repo's keyword map targets a different category taxonomy (road/traffic/infrastructure/hygiene/ healthcare/systemic) that doesn't line up one-to-one with this project's categories.

  • A ChannelAdapter interface — new to this project, with no equivalent in the old repo:

    interface ChannelAdapter {
      fetchMentions(): Promise<InboundMention[]>;
    }

    with three implementations:

    • XAdapter — poll-based, X API v2 search/recent, Bearer token, with the demo-mode fallback preserved from the old xService.js almost verbatim (the fetch/format logic, not its storage), scheduled via pg_cron (Postgres's job-scheduling extension) instead of running per HTTP request.
    • InstagramAdapter — poll-based, against the Graph API's mentioned-media endpoint, using the same demo-mode convention as XAdapter.
    • WhatsAppAdapter — the one real webhook-based channel, using the Meta Cloud API. This includes a real inbound endpoint with HMAC-SHA256 signature verification — fixed to verify against the raw request body, not a re-serialized JSON string, which the plan identifies as a genuine bug in the old repo's implementation — plus two-phase media resolution (the webhook delivers a mediaId, and the URL is resolved asynchronously afterward) and genuine outbound-send capability (auto-reply, status notifications on issue updates).

  • An admin Integrations screen: channel connection cards (status/health/masked credentials, with a test-connection or reconnect action), a mention inbox with convert-to-issue, outbound templates with a delivery log and send-limits (quiet hours, rate caps), and a routing-rules editor that is audited, unlike the design prototype's version.

  • Connection status must model real degraded/disconnected/needs-reauth states. The design prototype only ever shows a green "CONNECTED" state, which this plan explicitly does not replicate.

A4 — Notifications & Broadcast, Analytics & Reports, System/Ops​

  • Schema: broadcasts, announcements, push_subscriptions.
  • A broadcast composer with its schedule field actually wired up — per §6 (open decisions), this was the design prototype's one completely unwired input — plus templates and announcement/banner toggles.
  • Analytics: extends the public site's scores/administrators read models with ward, official, and reporter leaderboards (the design drops reporter-impact despite the spec calling for it — this plan restores it), plus channel performance metrics, with CSV/PDF export reusing the reportExport.ts pattern already used in apps/web.
  • System/Ops: an audit-log viewer over audit_log, a queue monitor (pending the core job-queue table — see §6), and manual triggers for scheduled jobs (SLA sweep, score refresh — already scoped in the original backend architecture decision).

5. Integrations deep-dive: the X API piece, in detail​

This section is the concrete answer to "port the X API integration from the previous project":

  1. Port textDetection.js verbatim, with remapped category keys. It is pure, dependency-free, and framework-agnostic — exposing KEYWORD_CATEGORY_MAP, CITY_NAMES, detectCategory(), detectCity(), relativeTime(), and maskPhone(). It was verified directly by reading the file (93 lines, no database or HTTP coupling).

  2. Rebuild xService.js's fetch logic as XAdapter.fetchMentions() in apps/api, using the same request shape:

    GET https://api.twitter.com/2/tweets/search/recent?query=@{handle} -is:retweet

    with the same tweet.fields/expansions/user.fields/media.fields parameters, Bearer auth, and a 10-second timeout — and the same demo-mode fallback contract: no token configured, or a 401/402/403 response, returns curated demo mentions with mode: 'demo' and a human-readable reason on the paid-tier 402 case specifically (the old code handles that case thoughtfully and the plan keeps it). The difference from the old implementation: instead of returning mentions directly in an HTTP response on every request, a pg_cron-scheduled job calls this, dedups against channel_mentions.externalId, and persists new rows — the admin's "Mention inbox" reads from Postgres, not from a live API call on every page load.

  3. Credentials and connection state live in channel_configs, not in environment variables. The old repo has zero admin-configurable channel credentials and no health-check endpoints for channels — this is new work, not a port. The plan is for an admin to set an X_BEARER_TOKEN equivalent plus a handle through the UI; it is stored encrypted; and a "Test connection" action makes a real, lightweight API call and updates connectionStatus/lastCheckedAt.

  4. Instagram follows the same poll-and-adapter pattern — a different endpoint (the Graph API's mentioned_media), the same demo/live contract, and the same ChannelAdapter interface.

  5. WhatsApp is architecturally different and is meant to stay that way. It is the one channel with a real Meta Cloud API webhook (a GET handshake plus POST for inbound messages), HMAC-verified, with two-phase media resolution and genuine outbound-send capability. Its "Test connection" affordance in the admin UI is meant to be reframed as a health signal — "last webhook received at ⟨t⟩ / recent delivery success rate" — rather than a synchronous test-call button, since the real question for a webhook-based channel is "is Meta reaching us," not "can we reach Meta."

  6. Convert-to-issue reuses the exact same issue-creation path across all three channels. The old repo copy-pasted near-identical validators three times for this; the plan collapses it into one shared ConvertMentionDto (data transfer object) plus one service call, targeting whatever issue-creation endpoint exists once the public site's write paths land, or a dedicated admin-only creation call in the interim.

6. Open decisions (flagged by research, need a call before/while building)​

The design prototype and the written spec disagree in a few places, and the prototype itself has a few inert or dead spots. The defaults below are what this plan assumes going in:

  1. Cities/departments/police-stations/officials CRUD — the spec wants full CRUD, the design shows read-only tables. Default: build real CRUD per the spec (the public site's second milestone already has the read models to extend).
  2. Detail-drawer-everywhere vs. inline actions — the spec says the review-queue-with-drawer pattern is reused by six different queues; the design only actually builds it for two (intake triage, issue moderation) and uses cheaper inline expand/collapse for the rest (flags, applications, submissions, events, mentions). Default: follow the design's cheaper actual pattern for the MVP, and generalize to a shared drawer later only if the inline pattern proves insufficient — building six bespoke drawers upfront is a real cost the design itself didn't actually pay.
  3. Accountability scores — override/dispute UI — the spec wants it, the design has none. Default: build a minimal version (an annotation plus an override-value field per ward/official score) in A2.
  4. Donations/supporters — the spec says "include only if still wanted"; the design implements only an off-by-default feature flag with no management screen. Default: ship the flag only, with no dedicated screen, until this feature is confirmed to still be wanted at all.
  5. Routing-rule edits bypassing the mandatory audit-note flow — the design lets you edit keyword-to-category routing rules with no note attached, which is inconsistent with Configuration's save discipline elsewhere. Default: bring it under the same audited-save requirement described in §2.
  6. Issue comment threads — the old repo has these (messageService.js); the current schema doesn't. Default: out of scope for A1 unless per-issue citizen/reporter discussion is wanted as a feature — this changes A1's schema if so.
  7. Queue monitor's data source — the core backend architecture already calls for a SKIP LOCKED-based job queue, but it had not been built yet at the time of this plan (the public site's first two milestones didn't need it). A4's queue monitor has nothing to show until that table exists. Default: build the queue-monitor UI in A4 against whatever job table the core backend stands up first — this is a sequencing dependency to track, not a design decision to make.
  8. Admin-vs-citizen auth unification — covered in §2; the default is a separate admins table sharing the same Lucia session mechanism.

7. Suggested build order​

This sequencing matches the spec's own MVP phase-1 grouping, ordered by what unlocks what:

Phase 1 (MVP):

  1. A0 — foundations. Nothing else works without auth.
  2. A1 — feed moderation, one of the two "priority pillars."
  3. A3 — integrations, the other "priority pillar." Can run in parallel with A2 once A0 is done, since A2 and A3 touch disjoint schema.
  4. A2 — configuration, reference data, reporters, and RBAC screens.

Phase 2:

  • A4 — notifications, analytics, ops.
  • The deferred design gaps from §6 (comment threads, a generalized detail drawer, a donations screen), as needed.