build-plan.md

PauseAI Everything App — Build Plan

Living document. Last updated: 2026-03-26.

Tech decisions

Decision	Choice	Rationale
Framework	Next.js (App Router)	Best AI coding support, huge ecosystem
Language	TypeScript	Type safety, AI-friendly
ORM	Drizzle	Close to SQL, good JSONB support (Prisma's is limited)
Database	PostgreSQL	Relational + JSONB, scales easily
Job queue	graphile-worker	Postgres-backed, mature, good cron support
UI components	shadcn/ui + @base-ui/react	Composable, unstyled primitives, works with Tailwind
Table	AG Grid Community	Inline editing, filtering, bulk paste, free
Auth	NextAuth.js / Auth.js v5	Simple, supports Google OAuth
Email	Mailersend API	Already in use
Hosting	Railway	Web + worker + Postgres, git-push deploys

Build phases

Phase 1: Scaffold & data layer ✅

• Create GitHub repo
• Initialize Next.js project with TypeScript + Tailwind CSS
• Set up Drizzle ORM + Postgres schema
• Set up Vitest testing infrastructure
• Define schema: contacts, field_definitions, interactions, users, tags, emails, segments, campaigns, scripts, script_runs, automation_rules
• Set up shadcn/ui + @base-ui/react components
• Docker Compose for local Postgres
• Drizzle push (schema sync, no migration files needed for dev)
• Basic app layout (sidebar nav, header, content area)
• NextAuth.js with Google OAuth

Phase 2: Contacts CRUD ✅

• API: GET/POST/PUT/DELETE /api/contacts
• API: GET/POST/PUT/DELETE /api/fields
• Contacts table view with AG Grid (columns from field_definitions)
• Inline cell editing in table
• Contact detail page with dynamic form
• Search (name, email, any field)
• Filtering by any field

Phase 3: Interactions & tags ✅

• Schema: interactions, tags, contact_tags
• API: interactions CRUD
• API: tags CRUD + assign/remove from contacts
• Interaction timeline on contact detail page
• "Log interaction" form (type, notes, date)
• Tag management UI
• Bulk tag actions in table view
• Tags column in contacts table

Phase 4: Intake & import ✅

• Tally webhook endpoint (POST /api/webhooks/tally)
• CSV import: upload, column mapping, preview, import
• Quick-add modal (minimal form: name + email)

Phase 5: User management & permissions ✅

• is_admin boolean on users table
• Admin invite flow
• Role-based access control (admin vs non-admin) on all API endpoints
• User management admin page (Settings > Users)
• API key generation for machine-to-machine access (Settings > API Keys)
• ADMIN_EMAILS env var auto-promotes emails to admin on first sign-in

Phase 6: Field management UI ✅

• Admin page: list all field definitions (Settings > Fields)
• Create/edit/delete fields
• Reorder fields (sort order)
• Manage select/multi_select options
• Field types: text, number, date, email, url, select, multiselect, boolean

Phase 7: Segmentation & email ✅

• Schema: segments, campaigns, emails
• Segment query builder UI (AND/OR, all field types, tags)
• Segment preview (count + sample contacts)
• Save/load segments
• Mailersend integration: send single email, send batch
• Broadcast email: select segment → compose → send now or schedule
• Campaign scheduling (save scheduledAt, worker dispatches at the right time)
• Preview email (send test to any address)
• Campaign detail view with sent email list
• Inline campaign editing
• Email history on contact timeline (via emails table)

Phase 8: Background jobs & automations ✅

• graphile-worker set up (Postgres-backed job queue)
• Separate worker process (src/worker/index.ts)
• Job: send_campaign — sends campaign to segment contacts via Mailersend
• Job: dispatch_campaigns (cron: every minute) — enqueues scheduled campaigns
• Job: detect_churn (cron: daily 6am UTC) — flags dormant contacts
• Job: run_script — executes user-defined JS in a VM sandbox
• Job: dispatch_scripts (cron: every minute) — enqueues scripts on their cron schedule
• Script engine with ctx SDK (contacts.find/update, tags, email.send, interactions.create)
• Script editor UI with CodeMirror, cron presets, run history, templates
• Automation rules engine (if/then rules, runs on schedule)
• Deployed to Railway (web + worker + Postgres)

Phase 8b: Communication preferences & unsubscribe ✅

• Schema: communication_categories table, app_settings table
• Schema: contacts.communication_preferences JSONB column
• Schema: campaigns.category_id FK to communication_categories
• Seed default categories (newsletter, events, action-alerts)
• HMAC-SHA256 stateless unsubscribe tokens (src/lib/unsubscribe-tokens.ts)
• Communication categories CRUD (lib + API + Zod schemas)
• Campaign send flow: filter opted-out contacts, generate unsubscribe URLs
• {{unsubscribe}} merge variable in campaign email bodies
• Campaign UI: category dropdown in create/edit forms
• Campaign recipients preview: show "Unsubscribed" badge for opted-out contacts
• Public unsubscribe page (/unsubscribe) with preference center
• Public unsubscribe API (POST /api/unsubscribe, GET /api/unsubscribe/preferences)
• Mailersend webhook: handle activity.unsubscribed → update contact preferences
• Per-contact subscription status in contact detail page
• Subscription status column in contacts table
• Admin UI for managing email categories (Settings > Email Categories)
• App settings system with UI toggle for RFC 8058 List-Unsubscribe header
• Unsubscribe token tests

Phase 8c: External data sync (Connections) ✅

• Schema: connections, sync_configurations, sync_runs tables
• Connector abstraction (Connector interface with testConnection, listResources, getSchema, fetchRecords)
• Airtable connector (PAT auth, cursor-based pagination, schema introspection)
• Notion connector (integration token, database queries, property mapping)
• Demo connector (fake data generator, dev only)
• Connection management UI: create, test, delete connections
• Sync configuration UI: resource picker, field mapping, schedule, duplicate strategy
• Target-centric field mapping: external field sources + constant value sources
• Sync engine (src/lib/sync-engine.ts): fetch, deduplicate by email, create/update contacts
• Worker tasks: run_sync (on-demand) + dispatch_syncs (cron, every minute)
• Sync runs with full statistics (fetched, created, updated, skipped, errored) and log
• Schema validation: detect external field changes, set sync to needs_repair
• Sync provenance on contacts: sync_configuration_id + synced_fields columns
• UI: "Synced" badge in contacts table, read-only synced fields
• UI: Attribution banner in contact detail (connection + sync links, last synced timestamp)
• UI: Repair button for broken syncs on connection detail page
• Batch contact deletion: checkbox selection + contextual action bar (up to 10k)
• AG Grid Infinite Row Model for 10k–100k contacts (server-side pagination, search, sort)
• Custom header checkbox for select-all on current page
• CSV export via full server-side fetch (not limited to cached rows)

Phase 9: Dashboard & reporting ✅

• Dashboard page with overview stats cards
  • Total contacts / new this month / active / dormant
  • Contacts by lifecycle stage (donut chart)
  • Contacts by country (top 10 horizontal bar chart)
• Intake trend chart (6-month bar chart of new contacts over time)
• Recent activity feed (last 20 interactions with contact links)
• Campaign performance metrics (sent, delivered, opened, clicked, bounced counts + open rate)
• CSV export from contacts table and any segment view
• Mailersend webhook tracking (delivery/open/click/bounce/unsubscribe events → emails table status updates + campaign aggregate recalculation)

Phase 10: Workspaces (Multi-Tenancy) ✅

• Schema: workspaces table (id, name, slug, type: global/chapter, defaultLanguage)
• Schema: user_workspaces junction table (userId, workspaceId, role)
• Schema: contact_workspaces junction table (contactId, workspaceId, subscriptionStatus)
• Schema: workspace_id columns on tags, segments, campaigns, communication_categories, connections, sync_configurations
• Schema: field_definitions scope system (core, global_internal, workspace)
• Workspace context resolution: cookie (pauseai_workspace), header (X-Workspace-Id), query param
• Server-side workspace helpers: getServerWorkspaceId(), isServerWorkspaceGlobal() (via cookies)
• API workspace context: getActiveWorkspaceId(request), requireWorkspaceAdmin()
• Client-side workspace provider: WorkspaceProvider, useWorkspace(), useWorkspaceId(), useWorkspaceFetch()
• Two-layer role system: global role + workspace role, effective = max(both)
• Client-side effective role: useEffectiveRole(), useHasRole() hooks
• Server-side effective role: getEffectiveRole() in workspaces.ts
• Workspace switcher in sidebar (hidden if user has only one workspace)
• Workspace-scoped contacts: API filters by contact_workspaces junction
• Workspace-scoped tags: tags have workspace_id, API filters by workspace
• Workspace-scoped segments: segments belong to workspace, preview/query scoped
• Workspace-scoped campaigns: campaigns belong to workspace, recipient resolution workspace-aware
• Workspace-scoped communication categories: categories have workspace_id, API filters by workspace
• Workspace-scoped custom fields: scope system (core=all, global_internal=global only, workspace=specific)
• Workspace-scoped user management: users page shows only workspace members, role changes per-workspace
• Add-contact flow: detects existing contacts (409) and offers "Add to Workspace" button
• Workspace management UI: Settings > Workspaces page (global admin only) — create, edit, delete chapter workspaces
• Dev login: Credentials provider with preset users, workspace selector dropdown, auto-creates workspace memberships
• Settings layout: uses effective role (not just global role) to grant workspace admin access
• Communication preference keys namespaced by workspace: workspaceId:categoryName
• Segment tag filter: workspace-scoped tag matching with NULL fallback for legacy data
• Segment builder: field change handler correctly resets operator per field type (e.g., "has" for tags)
• Unsubscribe flow: workspace-aware preference center with per-workspace sections
• Workspace-scoped automations: scripts and rules CRUD, execution, and UI all filtered by workspace
• Script engine workspace isolation: ctx.contacts.find and tag operations scoped to script's workspace
• Subscription table display: cell renderer uses workspace-namespaced preference keys (workspaceId:categoryName)
• Contacts table auto-refresh after contact creation (custom event → AG Grid cache purge)
• Campaign segment update fix: segmentId preserved through stripNulls (same pattern as categoryId)
• Connection detail pages redirect to connections list on workspace mismatch
• Connections promoted to top-level sidebar item (admin-only, with PlugIcon) — moved from Settings sub-menu

Phase 11: Documentation & Support ✅

• In-app documentation system: renders docs/*.md files at /dashboard/docs
  • Runtime markdown rendering with react-markdown + remark-gfm + rehype-highlight
  • @tailwindcss/typography prose styling with code syntax highlighting
  • Docs manifest defines navigation structure (sections + pages)
  • Left sidebar nav within docs layout, highlights active page
  • Server-side file reading with generateStaticParams for build optimization
• Support ticket system: cross-workspace open forum with voting and notifications
  • Schema: support_tickets, ticket_replies, ticket_upvotes, ticket_subscriptions tables (cross-workspace, FK to users)
  • Zod schemas: CreateTicketInput, UpdateTicketInput, CreateTicketReplyInput
  • Business logic: CRUD, pagination, upvoting (toggle per user, sort by most voted), subscriptions (per-ticket + global), email notifications
  • Full REST API: GET/POST /api/support-tickets, GET/PUT/DELETE /api/support-tickets/:id, GET/POST /api/support-tickets/:id/replies, POST /api/support-tickets/:id/vote, POST/DELETE /api/support-tickets/:id/subscribe, GET/POST /api/support-tickets/subscribe-all, GET /api/support-tickets/unsubscribe, GET /api/support-tickets/stats
  • Auth: all users see all tickets; admins manage status/priority/delete; owners edit title/desc on open tickets
  • Voting: one upvote per user, toggle on/off, sort by most voted
  • Subscriptions: auto-subscribe on create/reply, global subscribe-all toggle (on by default for admins), per-ticket subscribe/unsubscribe
  • Email notifications: Graphile Worker task send_ticket_notification for new replies and status changes, HMAC unsubscribe tokens, one-click email unsubscribe
  • UI: ticket list with upvote counts + vote indicator, sort toggle, subscribe-all button, create form, detail page with upvote button + subscribe toggle + admin controls + reply thread
  • "Staff" badge on admin replies, closed ticket reply lockout
• Sidebar nav items: "Support" (LifeBuoyIcon) and "Documentation" (BookOpenIcon), accessible to all roles
• API docs generation script updated with support ticket endpoints
• Zod schema tests for all ticket validation (12 test cases), unsubscribe token tests (7 test cases)
• Settings → Integrations page (global admin only): MailerSend API key + from-email configurable in UI
  • DB-stored values override env vars; takes effect immediately, no redeploy needed
  • API key masked in GET /api/settings response
  • resolveMailerSendKey() / resolveFromEmail() helpers used throughout (web + worker)

Phase 12: Personal Email Integration (My Email Contacts) ✅

• Schema: email_connections table (user-scoped, provider, encrypted OAuth tokens, sync settings, status)
• Schema: email_contact_settings table (per-contact sync and visibility toggles)
• Schema: interactions table additions — email_connection_id FK, provider_message_id dedup index, visible_to_team boolean
• AES-256-GCM token encryption (src/lib/encryption.ts, EMAIL_ENCRYPTION_KEY env var)
• Gmail API client (src/lib/gmail.ts): OAuth flow, message fetching, address parsing
• Zod validation schemas for email connections and contact settings
• Gmail OAuth flow: GET /api/auth/gmail (initiate) + GET /api/auth/gmail/callback (exchange + encrypt + store)
• Email connections CRUD: list, delete (with token revocation), update default settings
• Gmail contacts list: browse everyone user has emailed, with CRM match status
• Contact import: add Gmail contacts to the active workspace
• Manual refresh: trigger on-demand sync via worker job
• Per-contact settings: sync interactions on/off, visible to team on/off, bulk update
• Worker task sync_email_interactions: fetch Gmail messages, match to CRM contacts, create interactions (subject + snippet only)
• Worker task dispatch_email_syncs: cron every minute, enqueue connections whose sync interval has elapsed
• Interaction visibility filtering: own synced emails always visible; others see only visible_to_team = true
• UI: "My Email Contacts" page (/dashboard/my-email-contacts) with connect/disconnect, contacts table, import, settings
• UI: Gmail badge + "Private" indicator on contact interaction timeline
• UI: Sidebar nav item "My Email Contacts" (InboxIcon, visible to all roles)
• Provider-agnostic schema design (supports future Outlook/IMAP)

---

Testing strategy

Every phase ships with tests. The core data layer and API must be robust.

Unit tests (Vitest):

• Data validation logic
• Segment query builder → SQL translation ✅
• Script engine sandbox ✅
• Business logic (lifecycle transitions, deduplication)

Integration tests (Vitest + real Postgres): ⚠️ Not yet implemented

• API endpoints: CRUD operations, error cases, auth checks
• Webhook handlers: Tally intake, Mailersend events
• Background jobs: campaign sending, churn detection

Test infrastructure needed:

• Test database with reset between suites
• Factory functions for test data
• API test helpers for authenticated requests

Rule: No API endpoint or background job ships without tests covering happy path + key error cases.

---

What "done" looks like per phase

• After Phase 2: You can browse, search, edit contacts in a table. Replaces Airtable for viewing data.
• After Phase 4: New joiners flow in automatically. You can import your Airtable data. The system is live.
• After Phase 5: Team can log in with their own accounts. Permissions enforced.
• After Phase 7: You can send targeted emails to segments. Full Airtable+Mailersend replacement.
• After Phase 8b: Contacts can manage their email subscriptions. Compliant unsubscribe system.
• After Phase 8c: External data flows in automatically. Airtable and Notion contacts sync on schedule with provenance tracking. Table scales to 100k contacts.
• After Phase 9: You have visibility into how the org is doing. Full v1.
• After Phase 10: Multi-tenant with workspaces. Each chapter operates independently.
• After Phase 11: Self-documented with in-app docs and built-in feedback loop via support tickets.
• After Phase 12: Users can connect their personal Gmail to discover contacts, import them, and auto-log email interactions. ← we are here