Add Persistence Layer — SQLite + Prisma (Controller & Database)

[E-privo]

Add Persistence Layer — SQLite + Prisma (Controller & Database)
Description
This PR adds a full persistence layer to the Financial Data Analyst quickstart using Prisma ORM with SQLite, transforming it from a stateless demo into a production-ready application with data storage capabilities.
What's been added:
Database Schema (prisma/schema.prisma)
User — stores basic user identity, linked to sessions
Session — represents a conversation thread, linked to a user
Message — individual chat messages (user/assistant) within a session
Analysis — saved chart payloads + Claude's insights, tied to a session
Controllers (lib/db/controllers/)
userController.ts — full CRUD + upsert for user management
sessionController.ts — session lifecycle + message management, with bulk insert support
analysisController.ts — save, retrieve, update and delete chart analyses with automatic JSON serialization/deserialization
API Routes (app/api/)
GET/POST/PATCH/DELETE /api/users — user management endpoints
GET/POST/PATCH/DELETE /api/sessions — session and message endpoints
GET/POST/PATCH/DELETE /api/analyses — saved chart analysis endpoints
Infrastructure (lib/db/client.ts)
Singleton Prisma client with hot-reload safety for Next.js dev environment
Development query logging, production error-only logging
Quickstart
[ ] Computer Use Demo
[ ] Customer Support Agent
[x] Financial Data Analyst
[ ] N/A
Type of Change
[ ] Bug fix
[x] New feature
[ ] Documentation update
[ ] Code refactoring
[ ] Other (please describe):
Testing
Setup Steps to Reproduce

1. Install dependencies

npm install prisma @prisma/client

2. Add to .env.local

DATABASE_URL="file:./dev.db"

3. Run migrations

npx prisma migrate dev --name init

4. Start dev server

npm run dev
Manual API Tests
Create a user:
curl -X POST http://localhost:3000/api/users
-H "Content-Type: application/json"
-d '{"email": "test@example.com", "name": "Test User"}'
Create a session:
curl -X POST http://localhost:3000/api/sessions
-H "Content-Type: application/json"
-d '{"userId": "", "title": "Q1 Revenue Analysis"}'
Save a message:
curl -X POST http://localhost:3000/api/sessions
-H "Content-Type: application/json"
-d '{"sessionId": "", "role": "user", "content": "Analyze my revenue data"}'
Save an analysis (chart):
curl -X POST http://localhost:3000/api/analyses
-H "Content-Type: application/json"
-d '{
"sessionId": "",
"chartType": "line",
"chartData": {"labels": ["Q1","Q2","Q3"], "values": [100, 200, 150]},
"title": "Quarterly Revenue",
"insight": "Revenue peaked in Q2 with a 100% increase from Q1."
}'
Verified Behaviors
[x] User upsert works correctly (no duplicates on repeated email)
[x] Deleting a session cascades to its messages and analyses
[x] Chart data is correctly serialized to JSON string on save and deserialized on read
[x] Session updatedAt timestamp is updated when a new message is added
[x] Prisma singleton does not create multiple DB connections on hot reload
[x] All API routes return correct HTTP status codes (200, 201, 400, 404, 500)
[x] Tested manually in development environment
Screenshots
Database Schema (ERD)
User
└── Session (userId → User.id)
├── Message (sessionId → Session.id) [CASCADE DELETE]
└── Analysis (sessionId → Session.id) [CASCADE DELETE]
New File Structure
prisma/
schema.prisma ← DB models

lib/
db/
client.ts ← Prisma singleton
controllers/
userController.ts ← User CRUD
sessionController.ts ← Session + Message CRUD
analysisController.ts ← Chart Analysis CRUD

app/
api/
users/route.ts ← GET POST PATCH DELETE
sessions/route.ts ← GET POST PATCH DELETE
analyses/route.ts ← GET POST PATCH DELETE
Additional Notes
SQLite was chosen for local-first development with zero infrastructure setup. Switching to PostgreSQL (e.g. for Vercel/production) only requires changing DATABASE_URL and the provider in schema.prisma — no controller or route code changes needed.
Chart data is stored as a JSON string in SQLite (no native JSON column type) and automatically parsed back to an object by the analysis controller on every read.
No breaking changes — all existing chat and file upload functionality is untouched. The persistence layer is purely additive.
Next step suggestion: Wire sessionController.addMessage() into the existing app/api/chat/route.ts so conversations are auto-saved on every Claude response without any frontend changes.