| name | database-prisma |
| description | Synthex database operations enforcer. NEVER use prisma db push for schema changes — use migrate diff + db execute. NEVER add non-nullable columns without defaults. NEVER write Prisma queries without organizationId scope. ALWAYS use backward-compatible migrations and validate with prisma validate first. Activate on ANY request to change schema, write a query, create a migration, add a model, or modify database operations. |
| effort | high |
| metadata | {"author":"synthex","version":"2.0","engine":"synthex-ai-agency","type":"capability-uplift-code","triggers":["database","prisma","schema","migration","query optimisation","schema","migration","prisma","query","database","model","db change"],"requires":["database/migrations.skill.md"]} |
| context | fork |
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
- Assess change scope — Determine if schema change, migration, or query work
- Back up current state — Tag with
git tag -a "backup-db-YYYY-MM-DD"
- Validate schema changes — Check relationships, indexes, and type consistency
- Generate migration — Run
npx prisma migrate dev --name <name>
- Review migration SQL — Inspect generated SQL for destructive operations
- Test locally first — Run migration against local Supabase instance
- Check for breaking changes — Verify no dropped columns without data backup
- Optimise queries — Identify N+1 patterns, suggest proper include/select
- Validate data integrity — Check foreign keys, cascades, enum sync
- Generate Prisma client — Run
npx prisma generate after schema changes
Input Specification
| Parameter | Type | Required | Description |
|---|
| operation | string | yes | schema-validate, migration-create, query-optimise, integrity-check |
| target | string | no | File path or model name |
| migration_name | string | no | Name for new migration |
Output Specification
| Field | Type | Description |
|---|
| operation | string | Operation performed |
| status | pass/fail/warning | Result status |
| changes | array | List of schema/query changes |
| warnings | array | Potential issues detected |
| migration_sql | string | Generated SQL (if migration) |
Error Handling
| Error | Action |
|---|
| Migration conflict | Resolve manually, never auto-merge |
| Destructive operation detected | Block and require explicit confirmation |
| Schema validation failure | Report exact field/model with issue |
| Connection failure | Check Supabase status, verify env vars |
| Data type mismatch | Report expected vs actual types |
| Rollback failure | Preserve state, escalate immediately |
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
lib/db/ — Database utilities
Commands
supabase start
npx prisma generate
supabase db push
supabase db reset
npx prisma migrate dev --name <name>
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:
npx prisma validate
npx prisma migrate diff \
--from-schema-datasource \
--to-schema-datamodel prisma/schema.prisma \
--script > migration.sql
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