Database Setup - PhotoFlow

Overview

PhotoFlow uses PostgreSQL as its database and Prisma as the ORM (Object-Relational Mapping) layer. This guide covers database setup, schema management, and maintenance.

Database Architecture

PhotoFlow stores data in a PostgreSQL database with two main tables:

Tasks - Photography orders and project tasks
task_comments - Comments attached to tasks

Prisma handles all database interactions, providing type-safe queries and automatic migrations.

Prisma Schema

The database schema is defined in prisma/schema.prisma:

prisma/schema.prisma

// Prisma configuration
generator client {
  provider = "prisma-client-js"
}

datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}

// Comment model
model task_comments {
  id         Int      @id @default(autoincrement())
  created_at DateTime @default(now())
  task       Tasks    @relation(fields: [task_id], references: [id], onDelete: Cascade)
  task_id    Int
  comment    String

  @@map("task_comments")
}

// Task model
model Tasks {
  id                     Int             @id @default(autoincrement())
  created_at             DateTime        @default(now())
  dueAt                  DateTime
  task                   String
  additional_information String
  status                 String
  is_finished            Boolean         @default(false)
  task_comments          task_comments[]
  amount_of_comments     Int             @default(0)
  taskColumn             String          @default("1")

  @@map("Tasks")
}

Tasks Table

Field	Type	Description	Default
`id`	Integer	Unique identifier	Auto-increment
`created_at`	DateTime	Creation timestamp	now()
`dueAt`	DateTime	Due date	Required
`task`	String	Task name/title	Required
`additional_information`	String	Description/notes	Required
`status`	String	Current status	Required
`is_finished`	Boolean	Completion flag	false
`taskColumn`	String	Kanban column ID	”1”
`amount_of_comments`	Integer	Comment count	0
`task_comments`	Relation	Associated comments	[]

task_comments Table

Field	Type	Description	Default
`id`	Integer	Unique identifier	Auto-increment
`created_at`	DateTime	Creation timestamp	now()
`task_id`	Integer	Parent task ID	Required
`comment`	String	Comment text	Required
`task`	Relation	Parent task	-

Relationships

The schema defines a one-to-many relationship:

// One task has many comments
model Tasks {
  task_comments  task_comments[]
}

// Each comment belongs to one task
model task_comments {
  task     Tasks  @relation(fields: [task_id], references: [id], onDelete: Cascade)
  task_id  Int
}

Cascading delete: When a task is deleted, all associated comments are automatically deleted.

Initial Database Setup

Install PostgreSQL

Ensure PostgreSQL 15+ is installed and running. See Requirements for installation instructions.

Create Database User

Create a dedicated user for PhotoFlow:

sudo -u postgres psql

CREATE USER photoflow WITH PASSWORD 'photoflow_password';
CREATE DATABASE photoflow OWNER photoflow;
GRANT ALL PRIVILEGES ON DATABASE photoflow TO photoflow;
\q

Configure Environment

Set the database URL in .env:

.env

DATABASE_URL="postgresql://photoflow:photoflow_password@localhost:5432/photoflow"

Generate Prisma Client

Generate the type-safe database client:

npx prisma generate

Run Migrations

Create the database schema:

npx prisma migrate dev --name init

This creates the tables and applies all migrations.

Database Migrations

Prisma Migrate handles schema changes over time.

Development Migrations

When developing and modifying the schema:

# Create a new migration after schema changes
npx prisma migrate dev --name descriptive_name

# Examples:
npx prisma migrate dev --name add_priority_field
npx prisma migrate dev --name add_user_table

This:

Compares your schema with the database
Generates SQL migration files
Applies migrations to your database
Regenerates the Prisma client

Production Migrations

For production deployments:

npx prisma migrate deploy

Key differences:

Only applies existing migration files
Doesn’t generate new migrations
Safe for automated deployments
Used in Docker entrypoint script

Never use prisma migrate dev in production. Use prisma migrate deploy instead.

Migration Files

Migrations are stored in prisma/migrations/:

prisma/
├── schema.prisma
└── migrations/
    ├── 20260301000000_init/
    │   └── migration.sql
    ├── 20260302000000_add_priority/
    │   └── migration.sql
    └── migration_lock.toml

Each migration includes:

Timestamp-based folder name
SQL file with the migration
Descriptive name

Commit these files to version control so all team members and deployments use the same schema.

Prisma Studio

Prisma Studio provides a visual database browser:

npx prisma studio

Access at http://localhost:5555 to:

Browse Data

View all tasks and comments in a table format

Edit Records

Manually edit, create, or delete records

Filter and Search

Filter records and search for specific data

Explore Relations

Navigate between related records (tasks ↔ comments)

Prisma Studio is perfect for debugging data issues or manually fixing incorrect records.

Database Queries

PhotoFlow uses Prisma Client for all database operations:

Create Task

import prisma from '$lib/server/prisma';

const task = await prisma.tasks.create({
  data: {
    dueAt: new Date('2026-03-15'),
    task: 'Wedding Photography - Smith Family',
    additional_information: 'Outdoor ceremony, 100 guests',
    status: 'Scheduled',
    taskColumn: '1'
  }
});

Find Tasks

// Get all unfinished tasks
const activeTasks = await prisma.tasks.findMany({
  where: {
    is_finished: false
  },
  orderBy: {
    dueAt: 'asc'
  }
});

// Get tasks in a specific column
const columnTasks = await prisma.tasks.findMany({
  where: {
    taskColumn: '2',
    is_finished: false
  },
  include: {
    task_comments: true  // Include comments
  }
});

Update Task

await prisma.tasks.update({
  where: { id: 123 },
  data: {
    status: 'In Progress',
    taskColumn: '2'
  }
});

Delete Task

// Deletes task and all comments (cascading)
await prisma.tasks.delete({
  where: { id: 123 }
});

Create Comment

await prisma.task_comments.create({
  data: {
    task_id: 123,
    comment: 'Photos delivered to client',
    created_at: new Date()
  }
});

// Increment comment counter
await prisma.tasks.update({
  where: { id: 123 },
  data: {
    amount_of_comments: { increment: 1 }
  }
});

Database Maintenance

Backup Database

Regular backups are essential:

# Create backup
pg_dump -U photoflow -d photoflow > backup_$(date +%Y%m%d).sql

# Or with compression
pg_dump -U photoflow -d photoflow | gzip > backup_$(date +%Y%m%d).sql.gz

Docker backup:

docker compose exec postgres pg_dump -U photoflow photoflow > backup.sql

Restore Database

# From SQL file
psql -U photoflow -d photoflow < backup_20260301.sql

# From compressed file
gunzip -c backup_20260301.sql.gz | psql -U photoflow -d photoflow

Docker restore:

cat backup.sql | docker compose exec -T postgres psql -U photoflow photoflow

Reset Database

This deletes ALL data!

# Drop and recreate database
npx prisma migrate reset

This will:

Drop the database
Create a new database
Apply all migrations
Run seed scripts (if configured)

Vacuum and Analyze

Optimize database performance:

# Connect to database
psql -U photoflow -d photoflow

-- Vacuum to reclaim space
VACUUM FULL;

-- Update statistics for query planner
ANALYZE;

-- Or combine both
VACUUM ANALYZE;

Run periodically or when:

After deleting many records
After bulk imports
Query performance degrades

Performance Optimization

Indexes

Add indexes for frequently queried fields:

model Tasks {
  // ... fields ...
  
  @@index([taskColumn])
  @@index([is_finished])
  @@index([dueAt])
}

Then create a migration:

npx prisma migrate dev --name add_indexes

Connection Pooling

For production, configure connection pooling in DATABASE_URL:

DATABASE_URL="postgresql://user:pass@host:5432/db?connection_limit=10"

Query Optimization

Use select to fetch only needed fields:

const tasks = await prisma.tasks.findMany({
  select: {
    id: true,
    task: true,
    dueAt: true
    // Don't fetch additional_information if not needed
  }
});

Monitoring

Check Database Size

SELECT pg_size_pretty(pg_database_size('photoflow'));

Table Statistics

SELECT 
  schemaname,
  tablename,
  pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) AS size,
  n_live_tup AS row_count
FROM pg_stat_user_tables
ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC;

Active Connections

SELECT * FROM pg_stat_activity WHERE datname = 'photoflow';

Troubleshooting

Can't connect to database

Verify PostgreSQL is running:

sudo systemctl status postgresql  # Linux
brew services list                # macOS

Check connection string in .env
Test connection manually:

psql postgresql://photoflow:password@localhost:5432/photoflow

Check firewall and network settings

Migration failed

Check error message in terminal
View migration status:

npx prisma migrate status

If stuck, reset (WARNING: deletes data):

npx prisma migrate reset

Or manually fix the database and mark migration as applied

Prisma Client errors

If you see “Cannot find module ‘@prisma/client’”:

npx prisma generate
npm install

Slow queries

Add indexes for frequently queried fields
Use EXPLAIN ANALYZE to understand query plans
Run VACUUM ANALYZE to update statistics
Consider archiving old finished tasks

Next Steps

Environment Variables

Configure DATABASE_URL and other settings

Task Management

Learn how tasks are managed in the application

Production Checklist

Database backup and security best practices

Docker Deployment

Deploy with containerized PostgreSQL

Get Started

Installation

Core Features

Configuration

Deployment

​Overview

​Database Architecture

​Prisma Schema

​Tasks Table

​task_comments Table

​Relationships

​Initial Database Setup

​Database Migrations

​Development Migrations

​Production Migrations

​Migration Files

​Prisma Studio

Browse Data

Edit Records

Filter and Search

Explore Relations

​Database Queries

​Create Task

​Find Tasks

​Update Task

​Delete Task

​Create Comment

​Database Maintenance

​Backup Database

​Restore Database

​Reset Database

​Vacuum and Analyze

​Performance Optimization

​Indexes

​Connection Pooling

​Query Optimization

​Monitoring

​Check Database Size

​Table Statistics

​Active Connections

​Troubleshooting

​Next Steps

Environment Variables

Task Management

Production Checklist

Docker Deployment

Build docs developers (and LLMs) love

Overview

Database Architecture

Prisma Schema

Tasks Table

task_comments Table

Relationships

Initial Database Setup

Database Migrations

Development Migrations

Production Migrations

Migration Files

Prisma Studio

Database Queries

Create Task

Find Tasks

Update Task

Delete Task

Create Comment

Database Maintenance

Backup Database

Restore Database

Reset Database

Vacuum and Analyze

Performance Optimization

Indexes

Connection Pooling

Query Optimization

Monitoring

Check Database Size

Table Statistics

Active Connections

Troubleshooting

Next Steps