Database Prisma

Database Operations Agent

Purpose

Manages all database operations for SYNTHEX including Prisma schema validation, migration safety, query optimisation, and data integrity enforcement on the Supabase/PostgreSQL backend.

When to Use

Activate this skill when:

• Modifying the Prisma schema (prisma/schema.prisma)
• Creating or reviewing database migrations
• Optimising database queries (N+1, missing indexes)
• Debugging database connection or query issues
• Validating foreign key constraints or cascade behaviour

When NOT to Use This Skill

• When creating API endpoints (use api-testing for testing, code for building)
• When building frontend components (use design or ui-ux)
• When writing raw SQL migrations outside Prisma (use migrations.skill.md directly)
• When configuring Supabase auth or storage (use supabase.skill.md)
• Instead use: api-testing for endpoint work, migrations.skill.md for raw SQL

Tech Stack

• ORM: Prisma 5.x
• Database: PostgreSQL (Supabase)
• Hosting: Supabase managed instance
• Local Dev: Supabase CLI

Instructions

1. Assess change scope — Determine if schema change, migration, or query work
2. Back up current state — Tag with git tag -a "backup-db-YYYY-MM-DD"
3. Validate schema changes — Check relationships, indexes, and type consistency
4. Generate migration — Run npx prisma migrate dev --name <name>
5. Review migration SQL — Inspect generated SQL for destructive operations
6. Test locally first — Run migration against local Supabase instance
7. Check for breaking changes — Verify no dropped columns without data backup
8. Optimise queries — Identify N+1 patterns, suggest proper include/select
9. Validate data integrity — Check foreign keys, cascades, enum sync
10. Generate Prisma client — Run npx prisma generate after schema changes

Input Specification

Output Specification

Error Handling

Safety Rules

• NEVER drop columns without data backup
• ALWAYS test migrations locally first
• REQUIRE explicit confirmation for destructive operations
• MAINTAIN migration history integrity
• NEVER auto-apply migrations to production

Models Overview

# Core Models
- User, Account, Session
- Team, TeamMember
- ScheduledPost, ScheduledReport

# Analytics Models
- AnalyticsEvent, EngagementMetric
- SentimentAnalysis, ContentPerformance

# Competitor Models
- TrackedCompetitor, CompetitorSnapshot
- CompetitorPost, CompetitorAlert

Key Files

• prisma/schema.prisma — Database schema
• prisma/migrations/ — Migration history
• lib/prisma.ts — Prisma client instance

Commands

supabase start                       # Start local Supabase
npx prisma generate                  # Generate Prisma client
supabase db push                     # Apply migrations
supabase db reset                    # Reset database (destructive)
npx prisma migrate dev --name <name> # Create migration

Integration Points

• Works with api-testing for data validation
• Coordinates with code-review for query patterns
• Supports client-retention with metrics storage
• References migrations.skill.md for raw SQL patterns

Capability Uplift — Override Defaults

NEVER use npx prisma db push for schema changes in any environment — it bypasses migration history and will silently break production. Never add a non-nullable column without a default value (breaks existing rows). Never drop or rename a column without explicit human approval (data loss risk).

INSTEAD every schema change follows:

# 1. Validate first
npx prisma validate

# 2. Generate SQL diff
npx prisma migrate diff \
  --from-schema-datasource \
  --to-schema-datamodel prisma/schema.prisma \
  --script > migration.sql

# 3. Review the SQL manually

# 4. Execute
npx prisma db execute --file migration.sql --schema prisma/schema.prisma

Every new column is either nullable or has a @default(...). Every query on a multi-tenant model includes where: { organizationId }.

REFERENCE .claude/skills/synthex-standards/references/code-standards.md