Skip to main content

Database Schema Overview

The FYI Automation Tool uses PostgreSQL as its database with Prisma ORM for type-safe database access. The schema is designed to support automation flows, device management, project integration, and AI-powered testing.

Database Configuration​

The application uses PostgreSQL with the following connection string format:

DATABASE_URL="postgresql://username:password@localhost:5432/database_name?schema=public"

Schema Overview​

Core Models​

  • Flow - Main automation flow definitions
  • Device - Android device registrations
  • FlowSettings - Global flow execution settings

AI & Automation​

Project Management Integration​

Media & Recording​

Relationships​

PlaneIntegration (1) ──── (N) Project
    │                        │
    │                        ├── (N) WorkItem
    │                        └── (N) Label
    │
Flow (1) ──── (N) FlowCoordinateCache
    │
    └── (1) ──── (N) Recording

Database Operations​

Prisma Commands​

# Generate Prisma client
npx prisma generate

# Apply schema changes
npx prisma db push

# View database in browser
npx prisma studio

# Create migration
npx prisma migrate dev --name migration_name

# Reset database
npx prisma migrate reset

Environment Variables​

# Database connection
DATABASE_URL="postgresql://user:password@localhost:5432/fyi"

# Optional: Database schema
DATABASE_SCHEMA="public"

Data Types​

Common PostgreSQL Types Used​

  • String - VARCHAR/TEXT fields
  • Int - INTEGER fields
  • BigInt - BIGINT fields (for large numbers)
  • Float - REAL/DOUBLE PRECISION fields
  • Boolean - BOOLEAN fields
  • DateTime - TIMESTAMP fields
  • Json - JSON/JSONB fields for complex data

Special Fields​

  • String[] - Array of strings (stored as PostgreSQL arrays)
  • Json[] - Array of JSON objects
  • @id @default(uuid()) - UUID primary keys
  • @unique - Unique constraints
  • @default() - Default values
  • @map() - Custom column names
  • @updatedAt - Auto-updating timestamp fields

Indexing Strategy​

Automatic Indexes​

Prisma automatically creates indexes for:

  • Primary keys (@id)
  • Unique constraints (@unique)
  • Foreign keys (relations)

Consider adding indexes on frequently queried fields:

  • Flow labels (for filtering)
  • Device fingerprints (for lookups)
  • Recording timestamps (for time-based queries)
  • Work item states (for status filtering)

Migration Strategy​

Development​

# Create and apply migration
npx prisma migrate dev --name add_new_feature

# Reset and reapply all migrations
npx prisma migrate reset

Production​

# Mark migration as applied (for schema changes)
npx prisma migrate deploy

# Push schema changes directly (for prototyping)
npx prisma db push

Performance Considerations​

Connection Pooling​

Configure connection pooling for production:

# In production, use connection pooling
DATABASE_URL="postgresql://user:password@localhost:5432/fyi?pgbouncer=true&connect_timeout=15"

Query Optimization​

  • Use select to fetch only needed fields
  • Implement pagination for large result sets
  • Use appropriate indexes for query performance
  • Consider database views for complex aggregations

Monitoring​

Monitor database performance using:

  • Prisma's query logging
  • PostgreSQL's pg_stat_statements
  • Connection pool metrics
  • Query execution times

Backup and Recovery​

Backup​

# Create database backup
pg_dump -U username -h localhost database_name > backup.sql

# Or using Docker
docker exec postgres-container pg_dump -U username database_name > backup.sql

Restore​

# Restore from backup
psql -U username -h localhost database_name < backup.sql

# Or using Docker
docker exec -i postgres-container psql -U username database_name < backup.sql

Security​

Connection Security​

  • Use SSL/TLS for production connections
  • Store credentials securely (environment variables)
  • Limit database user permissions
  • Use private networking when possible

Data Protection​

  • Encrypt sensitive data at rest
  • Use parameterized queries (handled by Prisma)
  • Implement proper access controls
  • Regular security updates for PostgreSQL