{"slug":"business/PRD_BACKEND_DEVELOPMENT","title":"PRD — Backend Layer Development (Complete)","section":"business","sectionLabel":"Business","status":"unclassified","type":"document","path":"business/PRD_BACKEND_DEVELOPMENT.mdx","bytes":14472,"description":null,"updated":null,"source":"integratewise/Integratewise-docs-wiki","content":"\n> **Date:** June 5, 2026 | **Authority:** Nirmal (Founder)\n> **Objective:** Make all backend layers development-complete before any frontend wiring.\n> **Reference:** `docs/tech/INTEGRATEWISE_PRODUCT_ARCHITECTURE.md` (Sections A–D)\n\n---\n\n## 1. Goal\n\nComplete the backend so that every projection endpoint returns real data from real schemas.\nNo stubs. No mocks. No \"TODO\" in production paths. Every layer works end-to-end independently\nbefore the frontend touches it.\n\n---\n\n## 2. Success Criteria\n\n```\nWhen this PRD is complete:\n  ✅ Any frontend store can call its projection endpoint and get real structured data\n  ✅ A user can sign up, connect a tool, and see real Spine data in D1\n  ✅ Memory loop captures → promotes → indexes → retrieves end-to-end\n  ✅ Governance board has tasks, proposals, decisions flowing through\n  ✅ Twin can assemble Entity 360 (Spine + Memory + Signals) from live data\n  ✅ All services compile, deploy, and respond to health checks\n```\n\n---\n\n## 3. Deliverables (Ordered by Dependency)\n\n### 3.1 D1 Schema — Complete Data Layer\n\nAll tables needed by the system must exist in D1 (integratewise-spine-cache).\n\n```\nMIGRATION 0001: tenants, tenant_users, workspaces                    ✅ DONE\nMIGRATION 0002: tenant_spine_config\n  - tenant_id TEXT PRIMARY KEY\n  - domains TEXT (JSON array)\n  - industry TEXT\n  - schema_version TEXT\n  - allowed_entity_types TEXT (JSON array)\n  - connector_configs TEXT (JSON object)\n  - connected_connectors TEXT (JSON array)\n  - subscription_tier TEXT DEFAULT 'free'\n  - created_at, updated_at\n\nMIGRATION 0003: spine_entities (the Spine in D1)\n  - id TEXT PRIMARY KEY\n  - tenant_id TEXT NOT NULL\n  - entity_type TEXT NOT NULL\n  - name TEXT NOT NULL\n  - source TEXT NOT NULL\n  - source_id TEXT NOT NULL\n  - health_score INTEGER\n  - health_trend TEXT\n  - arr INTEGER\n  - tier TEXT\n  - status TEXT\n  - data TEXT (JSON — all entity-specific fields)\n  - metadata TEXT (JSON)\n  - created_at, updated_at\n  - UNIQUE(tenant_id, source, source_id)\n  INDEXES: tenant_id, entity_type, tenant_id+entity_type\n\nMIGRATION 0004: signals\n  - id TEXT PRIMARY KEY\n  - tenant_id TEXT NOT NULL\n  - entity_id TEXT\n  - entity_name TEXT\n  - type TEXT NOT NULL\n  - severity TEXT NOT NULL\n  - title TEXT\n  - body TEXT\n  - evidence TEXT (JSON array)\n  - data TEXT (JSON)\n  - status TEXT DEFAULT 'active'\n  - created_at TEXT\n  INDEXES: tenant_id, tenant_id+entity_id, tenant_id+severity\n\nMIGRATION 0005: proposals\n  - id TEXT PRIMARY KEY\n  - tenant_id TEXT NOT NULL\n  - entity_id TEXT\n  - entity_name TEXT\n  - title TEXT NOT NULL\n  - body TEXT\n  - confidence REAL\n  - risk REAL\n  - category TEXT\n  - evidence TEXT (JSON array)\n  - state TEXT DEFAULT 'pending'\n  - proposed_by TEXT DEFAULT 'twin'\n  - reviewed_by TEXT\n  - reviewed_at TEXT\n  - created_at, updated_at\n  INDEXES: tenant_id+state, tenant_id+entity_id\n\nMIGRATION 0006: governance (tasks + decisions + outcomes)\n  tasks:\n  - id TEXT PRIMARY KEY\n  - tenant_id TEXT NOT NULL\n  - title TEXT NOT NULL\n  - description TEXT\n  - owner TEXT NOT NULL (human | twin | team)\n  - status TEXT DEFAULT 'pending'\n  - priority TEXT DEFAULT 'medium'\n  - entity_refs TEXT (JSON array)\n  - procedures TEXT (JSON array of {step, description, completed})\n  - knowledge_refs TEXT (JSON array)\n  - assignee TEXT\n  - due_date TEXT\n  - outcomes TEXT (JSON array)\n  - created_at, updated_at\n  INDEXES: tenant_id+status, tenant_id+owner\n\n  decisions:\n  - id TEXT PRIMARY KEY\n  - tenant_id TEXT NOT NULL\n  - title TEXT NOT NULL\n  - body TEXT\n  - approved_by TEXT\n  - evidence TEXT (JSON array)\n  - expected_outcome TEXT\n  - actual_outcome TEXT\n  - entity_refs TEXT (JSON array)\n  - source_proposal_id TEXT\n  - created_at TEXT\n  INDEXES: tenant_id\n\nMIGRATION 0007: memory_metadata (D1 mirror for human browse)\n  - id TEXT PRIMARY KEY\n  - tenant_id TEXT NOT NULL\n  - category TEXT NOT NULL\n  - title TEXT NOT NULL\n  - entity_refs TEXT (JSON array)\n  - confidence REAL\n  - governance_state TEXT DEFAULT 'staging'\n  - source TEXT\n  - r2_key TEXT (pointer to R2 content)\n  - vectorize_indexed INTEGER DEFAULT 0\n  - created_at, updated_at\n  INDEXES: tenant_id+category, tenant_id+governance_state\n\nMIGRATION 0008: connectors_d1 (connection status for D1-first users)\n  - id TEXT PRIMARY KEY\n  - tenant_id TEXT NOT NULL\n  - provider TEXT NOT NULL\n  - status TEXT DEFAULT 'connected'\n  - flow_type TEXT (A | B | C)\n  - nango_connection_id TEXT\n  - last_sync_at TEXT\n  - sync_error TEXT\n  - metadata TEXT (JSON)\n  - created_at, updated_at\n  - UNIQUE(tenant_id, provider)\n  INDEXES: tenant_id\n```\n\n### 3.2 Auth Flow — CF Edge JWT\n\n```\nENDPOINT: POST /api/v1/auth/signup\n  Body: { email, password, name }\n  Action: creates tenant + user in D1, issues JWT\n  Returns: { token, tenant_id, user_id }\n\nENDPOINT: POST /api/v1/auth/login\n  Body: { email, password }\n  Action: validates credentials (D1 lookup), issues JWT\n  Returns: { token, tenant_id, user_id }\n\nENDPOINT: POST /api/v1/auth/google\n  Body: { google_token }\n  Action: validates Google token, finds/creates user in D1, issues JWT\n  Returns: { token, tenant_id, user_id }\n\nENDPOINT: GET /api/v1/auth/me\n  Headers: Authorization: Bearer <jwt>\n  Returns: { user_id, tenant_id, email, name, role, plan }\n\nJWT STRUCTURE:\n  sub: user_id\n  tenant_id: tenant_id\n  role: owner | admin | member\n  plan: free | starter | pro | enterprise\n  iat, exp (24h)\n\nLOCATION: services/gateway/src/auth.ts (new file)\n  Uses: services/tenants D1 tables for user lookup\n  Uses: lib/jwt.ts for signJWT (already exists)\n```\n\n### 3.3 Onboarding Endpoint\n\n```\nENDPOINT: POST /api/v1/onboarding/initialize\n  Headers: Authorization: Bearer <jwt>\n  Body: {\n    domain: string,\n    industry: string,\n    department: string,\n    company_size: string,\n    workspace_name: string,\n    connectors: [{ provider, entityTypes, flowType }]\n  }\n  Action:\n    1. Write tenant_spine_config to D1\n    2. Update tenant name/settings in D1\n    3. For each connector: create connectors_d1 record\n    4. Return { tenant_config, next_step: 'connect' }\n\nENDPOINT: POST /api/v1/onboarding/connect\n  Headers: Authorization: Bearer <jwt>\n  Body: { provider: string }\n  Action:\n    1. Call Nango createConnectSession (gets session token)\n    2. Return { session_token, integration_id }\n    3. Frontend opens Nango Connect UI with token\n    4. After connect: Nango webhook → /nango/webhook → triggers creamy sync\n\nLOCATION: services/gateway/src/onboarding.ts (new file)\n  Or: extend services/tenants with these routes\n```\n\n### 3.4 Pipeline — Spine Write to D1\n\nThe Pipeline currently writes to Supabase. For initial users it must also write to D1.\n\n```\nCHANGE: services/pipeline (the 8-stage Normalizer output)\n  After normalization: write entity to D1 spine_entities table\n  Pattern:\n    INSERT INTO spine_entities (id, tenant_id, entity_type, name, source, source_id, data, ...)\n    ON CONFLICT (tenant_id, source, source_id) DO UPDATE SET ...\n\n  This is the Spine write path for D1-first users.\n  For promoted users: dual-write (D1 + Supabase).\n\nSIGNAL GENERATION:\n  After Spine write, if health_score changed or entity state changed:\n    INSERT INTO signals (id, tenant_id, entity_id, type, severity, ...)\n  Signal types: health_drop, renewal_approaching, engagement_drop, etc.\n  These are what /v1/signals returns and what /desk shows.\n```\n\n### 3.5 Service Route Verification\n\nEvery downstream service must respond correctly to the paths the projection layer calls.\n\n```\nINTELLIGENCE SERVICE:\n  GET /v1/signals?tenant_id=X&severity=Y&limit=Z           ✅ EXISTS (think)\n  GET /v1/proposals?tenant_id=X&status=Y&limit=Z           ✅ EXISTS (govern)\n  POST /v1/proposals (create proposal)                      ✅ EXISTS (govern)\n  POST /v1/proposals/:id/approve                            ✅ EXISTS (govern)\n  POST /v1/proposals/:id/reject                             ✅ EXISTS (govern)\n  POST /v1/triage (trigger TriageWorkflow)                  ✅ EXISTS\n\nPIPELINE SERVICE:\n  GET /v1/spine/entities/:entityId                          ⬜ NEEDS BUILDING\n    Read from D1 spine_entities WHERE id = ?\n    Return: full entity object with parsed JSON fields\n\n  POST /v1/spine/write                                      ⬜ NEEDS BUILDING\n    Accept normalized entity from Normalizer\n    Write to D1 spine_entities\n    Generate signal if state changed\n    Return: { written: true, entity_id, signal_generated: bool }\n\nKNOWLEDGE SERVICE:\n  GET /v1/memory/search?entity_id=X&limit=Y                ⬜ NEEDS BUILDING\n    Query Vectorize with entity_id filter\n    Return: top K memory entries (title, category, body snippet, confidence)\n\n  GET /v1/memory/org?category=X&limit=Y                    ⬜ NEEDS BUILDING\n    Query D1 memory_metadata WHERE category = X\n    Return: metadata list (for human browse)\n\nBFF/WORKFLOW SERVICE:\n  GET /v1/tasks?tenant_id=X&limit=Y                        ⬜ NEEDS BUILDING\n    Query D1 tasks table\n    Return: task list with parsed procedures/outcomes\n\n  POST /v1/tasks                                            ⬜ NEEDS BUILDING\n    Create task in D1\n\n  PATCH /v1/tasks/:id                                       ⬜ NEEDS BUILDING\n    Update task status, procedures, outcomes\n\n  GET /v1/decisions?tenant_id=X&limit=Y                    ⬜ NEEDS BUILDING\n    Query D1 decisions table\n    Return: decision list\n```\n\n### 3.6 Nango Live Connection\n\n```\nPREREQUISITE: NANGO_SECRET_KEY added to Cloudflare Secrets Store\n  Secret store ID: 1fd83fc5881e4cd296ae3c2fbe80693c\n  Secret name: NANGO_SECRET_KEY\n\nNANGO DASHBOARD:\n  Configure integrations: hubspot, jira (minimum for demo)\n  Set webhook URL: https://gateway.integratewise.ai/nango/webhook\n  Connection ID pattern: tenant_id\n\nVERIFICATION:\n  POST /internal/nango/session → returns { session_token }\n  Frontend opens Nango UI → user authorizes → webhook fires\n  Webhook → connector-sync triggers creamy sync\n  Pipeline writes to D1 spine_entities\n  GET /api/v1/projections/morning-context → shows real signals\n```\n\n### 3.7 Memory Loop End-to-End\n\n```\nWRITE PATH (code-complete from this session):\n  memory.write_conversational → Pipeline db-proxy → D1\n  memory.propose → D1 proposals table\n  TriageWorkflow → R2 + Vectorize + D1 memory_metadata (atomic write)\n\nREAD PATH (needs verification):\n  /v1/memory/search → Vectorize query → returns results\n  /v1/memory/org → D1 memory_metadata → returns metadata list\n\nINITIAL LOAD (needs trigger):\n  memory-repo sync script → batch → memory.write_conversational\n  → memory.propose (per doc, confidence 0.85)\n  → TriageWorkflow auto-approves\n  → R2 + Vectorize + D1\n\nVERIFICATION:\n  After initial load: Vectorize index has entries\n  Twin can query: \"what do we know about X\" → gets results from Vectorize\n  Entity 360: memory panel shows relevant entries for entity\n```\n\n### 3.8 R2 Spine Backup (A12 Mitigation)\n\n```\nAfter every Pipeline D1 write:\n  Append to R2: spine-backup/{tenant_id}/{YYYY-MM-DD}.jsonl\n  Content: one JSON line per entity written\n  Retention: 30 days daily, weekly beyond that (cleanup cron)\n\nIMPLEMENTATION:\n  services/pipeline — after D1 write, env.FILES.put() one JSONL line\n  R2 binding already exists on connector service (integratewise-files-prod)\n  Pipeline needs FILES binding added to wrangler.toml\n```\n\n---\n\n## 4. Dependency Order (Build Sequence)\n\n```\nSTEP 1: D1 Migrations (0002–0008)\n  No external dependency. Pure SQL.\n  Apply: wrangler d1 migrations apply integratewise-spine-cache --remote\n\nSTEP 2: Auth flow (signup/login/me)\n  Depends on: D1 tenants table (✅ done)\n  Depends on: lib/jwt.ts (✅ exists)\n\nSTEP 3: Onboarding endpoint\n  Depends on: D1 tenant_spine_config (step 1)\n  Depends on: Auth flow (step 2)\n\nSTEP 4: Pipeline D1 write path\n  Depends on: D1 spine_entities table (step 1)\n  Depends on: D1 signals table (step 1)\n\nSTEP 5: Service route endpoints (Pipeline, Knowledge, BFF)\n  Depends on: D1 tables (step 1)\n  Depends on: Pipeline write (step 4) for data to exist\n\nSTEP 6: Nango live connection\n  Depends on: NANGO_SECRET_KEY in secrets (manual)\n  Depends on: Pipeline D1 write (step 4)\n  Depends on: connector-sync triggering creamy (already wired)\n\nSTEP 7: Memory loop verification\n  Depends on: Vectorize pipeline (TriageWorkflow — ✅ wired this session)\n  Depends on: Knowledge /v1/memory/search endpoint (step 5)\n  Depends on: memory-repo sync script invocation (manual or cron)\n\nSTEP 8: R2 backup\n  Depends on: Pipeline D1 write (step 4)\n  Depends on: R2 binding on Pipeline (needs wrangler.toml addition)\n```\n\n---\n\n## 5. What Is NOT In Scope\n\n```\n✗ Frontend development (separate PRD: IW_FRONTEND_ARCHITECTURE.md)\n✗ External LLM MCP access (Phase 2)\n✗ Subscription/payment wiring (Phase 2)\n✗ mcp.integratewise.ai DNS setup (after memory loop verified)\n✗ Agent token issuance for Gemini/Claude/ChatGPT (after MCP DNS)\n✗ Advanced chunking strategy (DOC-09 §5.1 — can iterate after basic works)\n✗ Docs publication pipeline (Connection 5 — after memory loop)\n✗ Folder watcher automation (Connection 2 — after memory loop)\n```\n\n---\n\n## 6. Definition of Done\n\n```\nFor each step to be \"done\":\n  ✅ Code compiles (tsc --noEmit passes)\n  ✅ D1 migration applied (--remote)\n  ✅ Service responds to health check\n  ✅ Endpoint returns correct shape (matches TypeScript interface in IW_FRONTEND_ARCHITECTURE.md)\n  ✅ Committed to main with descriptive message\n  ✅ Tested with curl or wrangler dev (at minimum)\n```\n\n---\n\n## 7. Handoff Notes\n\n```\nTHIS SESSION COMPLETED:\n  ✅ Tenants → D1 migration (applied, Customer Zero seeded)\n  ✅ TriageWorkflow → R2 + Vectorize + D1 atomic write\n  ✅ BulkCleanupSwarmWorkflow binding enabled\n  ✅ Gateway projection endpoints (morning-context, entity360, governance, inbox)\n  ✅ Architecture docs (Sections A–D, Frontend Architecture)\n\nNEXT SESSION STARTS AT:\n  Step 1 — D1 migrations 0002–0008 (pure SQL, no external deps)\n  Then Step 2 — Auth flow\n  Then Step 4 — Pipeline D1 write\n\nREAD BEFORE STARTING:\n  docs/tech/INTEGRATEWISE_PRODUCT_ARCHITECTURE.md\n  docs/tech/IW_FRONTEND_ARCHITECTURE.md (Section 4 — API contract)\n  services/gateway/src/projections.ts (projection pattern)\n  services/tenants/migrations/0001_init_tenants.sql (schema pattern)\n```\n\n---\n\n_Backend layers complete = frontend can wire. Not before._\n_Build depth-first, not breadth-first._\n_Every endpoint must return real data from real schemas._\n"}