LP Internal AI — V1

An internal AI intelligence layer for Launchpad that lets team members query Claude with live organizational data — student records, program outcomes, certifications, competency scores, finances, donations, and communications.

System Architecture

Four logical layers: data sources → connectors → storage → MCP server + Claude, with the HQ dashboard running alongside.

┌─────────────────────────────────────────────────────────────────────┐
│                         DATA SOURCES                                │
│                                                                     │
│  Google Sheets   Google Drive   BigQuery   GiveButter   Aplos       │
│  (students,      (docs, notes,  (BI data)  (donations)  (accounting)│
│   outcomes,       student info)                                     │
│   attendance,                             Slack         Roam        │
│   finances)                               (channels)    (chat)      │
│                                           Notion                    │
│                                           (meeting transcripts)     │
└───────────────────────┬─────────────────────────────────────────────┘
                        │  scheduled syncs (AWS EventBridge)
                        │  manual: pnpm sync:<name>
                        ▼
┌─────────────────────────────────────────────────────────────────────┐
│                     CONNECTORS  (9)                                 │
│                                                                     │
│  Each connector runs sync() → calls runSync() → writes to          │
│  sync_runs table. Errors are captured; HQ dashboard shows status.  │
│                                                                     │
│  google-sheets  google-drive  bigquery  givebutter  aplos           │
│  slack          roam          notion                                │
└───────────────────────┬─────────────────────────────────────────────┘
                        │
                        ▼
┌─────────────────────────────────────────────────────────────────────┐
│                        STORAGE                                      │
│                                                                     │
│  Postgres 16 (AWS RDS / local Docker)                               │
│  ├── Structured tables (Prisma ORM)                                 │
│  │     students, staff, entity_aliases                              │
│  │     student_phase_outcomes, student_certifications               │
│  │     attendance_records, enrollment_snapshots                     │
│  │     finance_snapshots                                            │
│  │     donor_contacts, donor_gifts, donor_pipeline, donor_grants    │
│  │     student_employment, student_postsecondary                    │
│  │     sync_runs, usage_logs, aws_resource_jobs                     │
│  │     mcp_users, oauth_clients, tool_permissions                   │
│  └── Vector store (pgvector extension)                              │
│        document_chunks  ←  OpenAI text-embedding-3-large            │
│        (Google Drive docs + Slack + Roam → 1536-dim embeddings)     │
│                                                                     │
│  Entity Resolution: entity_aliases table deduplicates the same     │
│  person across all sources using exact + fuzzy (pg_trgm) matching. │
└───────────────────────┬─────────────────────────────────────────────┘
                        │
                        ▼
┌─────────────────────────────────────────────────────────────────────┐
│                   MCP SERVER  (16 tools)                            │
│                                                                     │
│  Exposes structured Prisma queries + pgvector semantic search       │
│  as Model Context Protocol tools.                                   │
│                                                                     │
│  Transport A: stdio  → Claude Desktop (local)                       │
│  Transport B: Streamable HTTP → AWS ECS Fargate behind ALB (production) │
│                                                                     │
│  All tool calls are logged to usage_logs for adoption tracking.     │
│  All tools return structured { error: { code, message } } on fail. │
└───────────────────────┬─────────────────────────────────────────────┘
                        │  MCP protocol
                        ▼
┌─────────────────────────────────────────────────────────────────────┐
│                    CLAUDE (Anthropic)                               │
│                                                                     │
│  Receives tool definitions + calls tools at query time.            │
│  Composes answers from structured data + semantic search results.   │
│  Team members interact via Claude Desktop or any MCP-capable client.│
└─────────────────────────────────────────────────────────────────────┘

                    ┌──────────────────────┐
                    │   HQ DASHBOARD       │
                    │   (Next.js 14)       │
                    │                      │
                    │  /        freshness  │
                    │  /sync    sync runs  │
                    │  /tools   tool logs  │
                    │                      │
                    │  Auth: NextAuth v5   │
                    │  Google OAuth        │
                    │  @launchpadphilly.org│
                    └──────────────────────┘

At query time, Claude calls MCP tools → the server runs Prisma queries or pgvector similarity search → results are returned and synthesized into a natural-language answer. At sync time, EventBridge (or pnpm sync:<name>) triggers a connector → it upserts rows via Prisma and, for document sources, generates embeddings via OpenAI → every run writes a result to sync_runs.

Tech Stack

Application layer

Concern	Technology	Notes
Language	TypeScript 5 (strict)	`noUncheckedIndexedAccess`, `exactOptionalPropertyTypes`
Monorepo	pnpm workspaces	17 workspace packages
HQ Dashboard	Next.js 14 (App Router)	Standalone Docker image
Auth	NextAuth v5 (Auth.js)	Google OAuth, domain-gated to `@launchpadphilly.org`
MCP Server	`@modelcontextprotocol/sdk`	stdio + Streamable HTTP transports
Embeddings	OpenAI `text-embedding-3-large`	1536 dimensions, batch + retry helpers
ORM	Prisma	All queries; `$queryRaw` only for pgvector + `percentile_cont`
Database	Postgres 16 + pgvector + pg_trgm	Local Docker; AWS RDS in production
Validation	Zod	All external data and env vars
Tests	Vitest	Unit + live-DB integration + spawned MCP server integration
CI	GitHub Actions	pgvector service container; full suite on every push

Infrastructure & tooling

Concern	Technology
App hosting	AWS ECS Fargate behind ALB
Scheduling	AWS EventBridge
Secrets	AWS Secrets Manager (prod) / `.env` (local)
Monitoring	Sentry
BI dashboards	Metabase (self-hosted on AWS)
Workflow automation	n8n (self-hosted on AWS)
Data warehouse	Athena over S3
Ingestion tooling	Airbyte (self-hosted; future non-Sheets sources)

Folder Structure

lp-internal-ai-v1/
├── apps/
│   ├── hq/                          # Next.js 14 HQ dashboard
│   │   ├── app/
│   │   │   ├── page.tsx             # / — data freshness overview
│   │   │   ├── dashboard/page.tsx   # /dashboard — analytic dashboard
│   │   │   ├── sync/page.tsx        # /sync — connector sync run history
│   │   │   ├── tools/page.tsx       # /tools — MCP tool call log
│   │   │   ├── admin/page.tsx       # /admin — MCP OAuth + tool permissions
│   │   │   ├── aws-jobs/[id]/       # /aws-jobs/:id — resource job details
│   │   │   └── api/health/route.ts  # GET /api/health (unauthenticated)
│   │   ├── auth.ts                  # NextAuth v5 config + Google provider
│   │   ├── middleware.ts            # Route guard
│   │   └── Dockerfile
│   ├── mcp-server/                  # MCP server — 16 tools
│   │   ├── src/
│   │   │   ├── index.ts             # Entry: stdio (Claude Desktop)
│   │   │   ├── serve-http.ts        # Entry: Streamable HTTP (ECS Fargate)
│   │   │   ├── make-server.ts       # Tool registration
│   │   │   ├── usage-log.ts         # Logs every tool call
│   │   │   └── tools/               # One file per MCP tool (16 files)
│   │   └── Dockerfile
│   ├── aws-mcp-server/              # AWS resource management MCP server
│   │   └── Dockerfile
│   └── sync/                        # One-off Fargate task runner for scheduled syncs
│       └── Dockerfile
├── packages/
│   ├── db/                          # @lp-ai/lib-db — Prisma client, schema, seed
│   ├── config/                      # @lp-ai/lib-config — typed env loader
│   └── embedding/                   # @lp-ai/lib-embedding — OpenAI batch helpers
├── connectors/                      # One package per source (9 total)
│   ├── google-sheets/               # ✅ Live — 12 sheet syncs, 26K+ records
│   ├── givebutter/                  # ✅ Live — REST client, donors/gifts/pipeline
│   ├── aplos/                       # ✅ Live — RSA auth, 16K+ records
│   ├── notion/                      # ✅ Live — meeting transcript sync + embeddings
│   └── google-drive|bigquery|slack|roam/   # skeletons
├── infra/
│   ├── postgres-init/               # SQL: CREATE EXTENSION pgvector, pg_trgm
│   └── iam/                         # AWS IAM policy templates
├── docs/
│   ├── architecture.md              # Detailed system overview
│   ├── database-schema.md           # All 30 Prisma models with columns + indexes
│   ├── mcp-server-spec.md           # All 16 tool definitions (input/output schemas)
│   ├── entity-resolution.md         # Cross-source deduplication strategy
│   ├── setup/                       # Phase-by-phase AWS setup guides (00–22)
│   ├── runbooks/                    # local-dev.md, credentials-checklist.md, aws-permissions.md
│   ├── decisions/                   # Architecture Decision Records
│   └── data-sources/                # Per-connector specs
├── docker-compose.yml               # Local Postgres 16 + pgvector
├── vitest.config.ts
├── tsconfig.base.json               # Shared TS config (ES2022, NodeNext, strict)
├── pnpm-workspace.yaml              # Workspace roots: apps/*, packages/*, connectors/*
├── package.json                     # Root scripts: db:*, sync:*, build, test
└── .env.example                     # Authoritative inventory of all env variables

Workspace Packages

pnpm resolves workspace:* references to local source at install time — no publishing required. Build order follows this graph:

@lp-ai/lib-config          (no internal deps)
@lp-ai/lib-db              (no internal deps)
@lp-ai/lib-embedding       (no internal deps)
      │
      ├── @lp-ai/connector-google-sheets   → lib-config, lib-db
      ├── @lp-ai/connector-bigquery        → lib-config, lib-db
      ├── @lp-ai/connector-givebutter      → lib-config, lib-db
      ├── @lp-ai/connector-aplos           → lib-config, lib-db
      ├── @lp-ai/connector-google-drive    → lib-config, lib-db, lib-embedding
      ├── @lp-ai/connector-slack           → lib-config, lib-db, lib-embedding
      ├── @lp-ai/connector-roam            → lib-config, lib-db, lib-embedding
      ├── @lp-ai/connector-notion          → lib-config, lib-db, lib-embedding
      ├── @lp-ai/hq                        → lib-config, lib-db
      ├── @lp-ai/mcp-server                → lib-config, lib-db, lib-embedding
      └── @lp-ai/aws-mcp-server            → lib-config, lib-db

Getting Started

Full walkthrough: docs/runbooks/local-dev.md — Credential requirements: docs/runbooks/credentials-checklist.md

Prerequisites: Node ≥ 20, pnpm ≥ 9, Docker Desktop running.

pnpm install                                              # all 17 packages
cp .env.example .env                                      # fill in values you have
pnpm db:up                                                # start Postgres + pgvector
pnpm db:generate                                          # generate Prisma client
pnpm --filter @lp-ai/lib-db push --skip-generate          # apply schema
pnpm db:seed                                              # 3 students, donors, finance, certs

pnpm -r typecheck && pnpm test                            # verify: 42 tests

pnpm --filter @lp-ai/hq dev                               # HQ → http://localhost:3000
pnpm --filter @lp-ai/mcp-server build
pnpm --filter @lp-ai/mcp-server start                     # MCP stdio (Claude Desktop)
pnpm --filter @lp-ai/mcp-server start:http                # MCP HTTP → http://localhost:8080

Only DATABASE_URL is required to boot — it defaults to the local Docker Postgres in .env.example. All API keys are optional at load time and validated at first use.

Onboarding Resources

New contributor checklist: docs/reference/new-developer-playbook.md
Connector maturity and verification map: docs/reference/connector-capability-matrix.md
Skills workflow reference: HOW-SKILLS-WORK.md

Package Management

All commands run from the repository root. Never use npm or yarn.

Installing dependencies

# Add to a specific workspace
pnpm add <package> --filter @lp-ai/<name>
pnpm add -D <package> --filter @lp-ai/<name>

# Add to root (shared tooling only: vitest, eslint, prettier, typescript, prisma CLI)
pnpm add -D -w <package>

Updating dependencies

pnpm update -r --interactive              # interactive update across all packages
pnpm update -r <package>                  # update one package everywhere
pnpm update --filter @lp-ai/<name>        # update all deps in one workspace

After updating, commit all changed package.json files and pnpm-lock.yaml together.

Removing dependencies

pnpm remove <package> --filter @lp-ai/<name>

Adding a new internal dependency

Add the workspace:* reference manually in the consuming package's package.json, then run pnpm install:

// e.g. connectors/givebutter/package.json
{
  "dependencies": {
    "@lp-ai/lib-config": "workspace:*",
    "@lp-ai/lib-db": "workspace:*"
  }
}

After editing the Prisma schema

pnpm db:generate                                          # regenerate Prisma client
pnpm --filter @lp-ai/lib-db push --skip-generate          # local dev (no migration file)
pnpm db:migrate                                           # production/staging (tracked migration)

Use push for local iteration. Use migrate for any change that must be tracked and replayed on the production RDS instance.

MCP Tools

All tools return structured JSON. Errors use { error: { code, message } }.

Tool	Description
`get_student_info`	Student profile + aliases from Google Drive seed doc
`get_entity_brief`	Composite: profile + phase progression + certs + recent mentions + donor history
`get_finance_brief`	Composite: budget summary + actuals + grants + giving pipeline
`query_students`	Population stats + filtered student lists
`query_outcomes`	Phase progression (Foundations → 101 → Lightspeed → LiftOff)
`query_enrollment`	Enrollment stats by phase, school, cohort, race
`query_certifications`	PCEP exam results and scores
`query_competency`	Per-student Beacon competency scores
`query_attendance`	Unified attendance across three cohort formats
`query_employment`	Post-program employment data (employer, wages, hours, exit codes)
`query_postsecondary`	College enrollment tracking (National Student Clearinghouse)
`query_finances`	Budgets, actuals, forecasts, stipends, CRM giving/pipeline/grants
`query_donors`	Building21 Development CRM donor lookup
`search_conversations`	Semantic search over Drive docs (Slack/transcripts in V0.2)
`search_by_person`	Scoped document search by student or staff name
`search_documents`	Raw document chunk search with optional entity filter

Full input/output schemas: docs/mcp-server-spec.md

Connectors

Connector	Source	Destination	Status
`google-sheets`	Launchpad Dashboard + Outcomes sheets (12 spreadsheets)	Postgres	✅ Live — all 12 syncs ported; 26K+ records
`google-drive`	Drive docs folder	Postgres + pgvector	Skeleton — creds available, implementation pending
`bigquery`	`lp-internal-ai` BigQuery project	Postgres	Skeleton — creds available, implementation pending
`givebutter`	GiveButter donation platform	`donor_contacts`, `donor_gifts`, `donor_pipeline`	✅ Live — REST client syncing
`aplos`	Aplos nonprofit accounting	Postgres (finance snapshots)	✅ Live — RSA-decryption auth; 16K+ records
`notion`	Notion meeting transcripts database	`document_chunks` (pgvector)	✅ Live — meeting transcripts with embeddings
`slack`	Designated Slack channels	pgvector	Skeleton — awaiting `SLACK_BOT_TOKEN`
`roam`	Roam chat / messaging	pgvector	Skeleton — awaiting `ROAM_API_KEY`

Each connector's sync() is wrapped by runSync(), which writes a success/error row to sync_runs on every run — visible in the HQ /sync page.

Current Status

Area	State
Typecheck	✅ All 17 packages pass
Tests	✅ 42 tests, 6 files (Vitest)
Local DB	✅ Postgres 16 + pgvector via Docker Compose
MCP tools	✅ All 16 wired to real Prisma queries
HQ dashboard	✅ Renders against live Postgres; auth gated; analytic dashboard ported
Google Sheets connector	✅ All 12 sheet syncs live; 26K+ records
GiveButter connector	✅ REST client syncing donors, gifts, pipeline
Aplos connector	✅ RSA-decryption auth; 16K+ records
Notion connector	✅ Meeting transcript sync with embeddings
OpenAI embeddings	✅ `text-embedding-3-large` verified and live
AWS account	✅ Account 851725317896, IAM user configured, us-east-1
Remaining connectors	🟡 Skeletons — google-drive, bigquery, slack, roam
AWS production	🟡 Docker images + ECS task defs built; deployment in progress
CI	✅ GitHub Actions with pgvector service container

See docs/runbooks/credentials-checklist.md for credential requirements. See docs/setup/README.md for the phase-by-phase AWS production setup status.

LP Internal AI MCP Server