Database Setup

Overview

Home Manager uses PostgreSQL as its database and Prisma as the ORM (Object-Relational Mapping) tool. This guide covers database setup, schema management, and migrations.

Database Requirements

Minimum Requirements:

PostgreSQL 12 or higher
At least 100MB storage (scales with usage)
Support for UUID generation

PostgreSQL Setup

Option 1: Local PostgreSQL Installation

Install PostgreSQL

brew install postgresql@15
brew services start postgresql@15

Create Database

# Connect to PostgreSQL
psql -U postgres

# Create database
CREATE DATABASE homemanager;

# Create user (optional)
CREATE USER homemanager_user WITH PASSWORD 'your_password';
GRANT ALL PRIVILEGES ON DATABASE homemanager TO homemanager_user;

# Exit psql
\q

Set DATABASE_URL

.env.local

DATABASE_URL="postgresql://postgres:password@localhost:5432/homemanager"

Option 2: Cloud PostgreSQL Providers

Supabase
Neon
Railway
Render

Create Project

Sign up at supabase.com
Click New Project
Choose your organization
Enter project details and password
Select a region close to your users

Get Connection String

Go to Project Settings → Database
Copy the Connection string under Connection info
Replace [YOUR-PASSWORD] with your database password

DATABASE_URL="postgresql://postgres:[YOUR-PASSWORD]@db.example.supabase.co:5432/postgres"

Enable Connection Pooling (Recommended)

For production, use Supabase’s connection pooler:

DATABASE_URL="postgresql://postgres:[YOUR-PASSWORD]@db.example.supabase.co:6543/postgres?pgbouncer=true"

Supabase offers a generous free tier with 500MB database storage.

Create Project

Sign up at neon.tech
Click Create Project
Choose project name and region
Select PostgreSQL version

Get Connection String

On the project dashboard, find Connection Details
Copy the connection string

DATABASE_URL="postgresql://user:[email protected]/neondb"

Neon offers serverless PostgreSQL with automatic scaling and branching for development.

Create PostgreSQL Service

Sign up at railway.app
Click New Project → Provision PostgreSQL
Database is automatically provisioned

Get Connection String

Click on the PostgreSQL service
Go to Variables tab
Copy the DATABASE_URL value

DATABASE_URL="postgresql://postgres:[email protected]:7432/railway"

Create PostgreSQL Database

Sign up at render.com
Click New → PostgreSQL
Enter database name and region
Select instance type (free tier available)

Get Connection String

On database dashboard, find Connections
Copy Internal Database URL (for apps on Render)
Or copy External Database URL (for external access)

DATABASE_URL="postgresql://user:[email protected]/database"

Prisma Schema Overview

Home Manager’s database schema includes the following models:

prisma/schema.prisma

generator client {
  provider = "prisma-client-js"
}

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

model ShoppingItem {
  id           String   @id @default(uuid())
  householdId  String
  name         String
  category     String
  position     Int      @default(0)
  checked      Boolean  @default(false)
  createdAt    DateTime @default(now())
}

model BillsItem {
  id           String   @id @default(uuid())
  householdId  String
  name         String
  amount       Float
  dueDate      DateTime
  category     String
  position     Int      @default(0)
  checked      Boolean  @default(false)
  createdAt    DateTime @default(now())
}

model ChoresItem {
  id           String   @id @default(uuid())
  householdId  String
  name         String
  assignee     String
  description  String
  position     Int      @default(0)
  checked      Boolean  @default(false)
  recurrence   String   @default("none")
  createdAt    DateTime @default(now())
}

model MaintenanceItem {
  id           String   @id @default(uuid())
  householdId  String
  title        String
  category     String
  description  String
  position     Int      @default(0)
  checked      Boolean  @default(false)
  recurrence   String   @default("none")
  createdAt    DateTime @default(now())
}

model Notification {
  id           String   @id @default(uuid())
  householdId  String
  type         String
  title        String
  body         String
  read         Boolean  @default(false)
  createdAt    DateTime @default(now())
}

model Household {
  id        String             @id @default(cuid())
  name      String
  members   HouseholdMember[]
  createdAt DateTime           @default(now())
}

model HouseholdMember {
  id           String     @id @default(cuid())
  householdId  String
  userId       String?
  invitedEmail String?
  role         String
  status       String?    @default("pending")
  Household    Household  @relation(fields: [householdId], references: [id])

  @@unique([householdId, userId])
}

model AuditLog {
  id        String   @id @default(cuid())
  userId    String
  userName  String
  action    String
  itemType  String
  itemName  String
  createdAt DateTime @default(now())
}

Key Models

ShoppingItem - Shopping list items
BillsItem - Bill tracking with amounts and due dates
ChoresItem - Household chores with assignees
MaintenanceItem - Home maintenance tasks
Notification - User notifications and activity feed
Household - Household groups
HouseholdMember - Members and invitations
AuditLog - Activity tracking and audit trail

Database Initialization

First-Time Setup

Install Dependencies

npm install

This automatically runs prisma generate via the postinstall script.

Push Schema to Database

For development and quick setup:

npx prisma db push

This command:

Creates all tables based on your schema
Updates existing tables if schema changed
Generates Prisma Client
Does NOT create migration files

db push is great for development but should not be used in production. Use migrations instead.

Verify Database

npx prisma studio

Opens Prisma Studio at http://localhost:5555 where you can:

View all tables
Inspect data
Manually add/edit records

Using Migrations (Production)

For production deployments, use migrations to track schema changes:

Create Initial Migration

npx prisma migrate dev --name init

This command:

Creates a prisma/migrations directory
Generates SQL migration files
Applies the migration to your database
Generates Prisma Client

Apply Migrations in Production

npx prisma migrate deploy

Use this in your production deployment:

Applies pending migrations
Does NOT generate new migrations
Safe for CI/CD pipelines

Common Prisma Commands

# Generate Prisma Client after schema changes
npx prisma generate

Build Script Integration

Home Manager’s package.json includes Prisma in the build process:

package.json

{
  "scripts": {
    "dev": "next dev --turbopack",
    "build": "prisma generate && next build",
    "start": "next start",
    "postinstall": "prisma generate"
  }
}

build: Generates Prisma Client before building Next.js
postinstall: Auto-generates client after npm install

No manual Prisma commands needed during normal development. The scripts handle it automatically.

Production Deployment Workflow

Set Environment Variables

Configure DATABASE_URL in your hosting platform.

Apply Migrations

npx prisma migrate deploy

Add this to your deployment script or CI/CD pipeline before starting the application.

Build Application

npm run build

Prisma Client is automatically generated during the build.

Start Application

npm start

Database Connection Pooling

For production environments with high traffic, use connection pooling:

Using PgBouncer (Recommended)

datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
  directUrl = env("DIRECT_DATABASE_URL") // Optional: for migrations
}

Most cloud providers (Supabase, Neon, Railway) offer built-in connection pooling.

Troubleshooting

Cannot Connect to Database

Error: Can't reach database server at host:portSolutions:

Check DATABASE_URL is correct
Verify database server is running
Check firewall/security group settings
For cloud databases, ensure IP is allowlisted
Test connection with psql or database client

Migration Issues

Error: Migration failed to applySolutions:

Check database permissions
Ensure no conflicting schema changes
Review migration SQL files in prisma/migrations
Use prisma migrate resolve for stuck migrations
Reset database with prisma migrate reset (development only)

Prisma Client Generation Fails

Error: Prisma Client could not be generatedSolutions:

Run npm install to ensure dependencies are installed
Check prisma/schema.prisma syntax
Verify DATABASE_URL is set
Delete node_modules/.prisma and regenerate
Clear npm cache: npm cache clean --force

Schema Drift

Error: Database schema is out of syncSolutions:

Run prisma db pull to sync schema from database
Run prisma migrate dev to apply pending migrations
Use prisma migrate deploy in production
Check migration history: prisma migrate status

Best Practices

Use Migrations in Production

Always use prisma migrate deploy instead of db push for production databases to maintain schema history.

Connection Pooling

Enable connection pooling for production to handle multiple concurrent requests efficiently.

Regular Backups

Set up automated backups for your PostgreSQL database. Most cloud providers offer this built-in.

Monitor Performance

Use database monitoring tools to track query performance and connection usage.

Next Steps

Environment Variables

Configure all required environment variables

Deployment Guide

Deploy your application to production

Get Started

Core Features

User Guide

Deployment

Overview

Database Requirements

PostgreSQL Setup

Option 1: Local PostgreSQL Installation

Option 2: Cloud PostgreSQL Providers

Prisma Schema Overview

Key Models

Database Initialization

First-Time Setup

Using Migrations (Production)

Common Prisma Commands

Build Script Integration

Production Deployment Workflow

Database Connection Pooling

Using PgBouncer (Recommended)

Troubleshooting

Cannot Connect to Database

Migration Issues

Prisma Client Generation Fails

Schema Drift

Best Practices

Use Migrations in Production

Connection Pooling

Regular Backups

Monitor Performance

Next Steps

Environment Variables

Deployment Guide

Build docs developers (and LLMs) love

Get Started

Core Features

User Guide

Deployment

​Overview

​Database Requirements

​PostgreSQL Setup

​Option 1: Local PostgreSQL Installation

​Option 2: Cloud PostgreSQL Providers

​Prisma Schema Overview

​Key Models

​Database Initialization

​First-Time Setup

​Using Migrations (Production)

​Common Prisma Commands

​Build Script Integration

​Production Deployment Workflow

​Database Connection Pooling

​Using PgBouncer (Recommended)

​Troubleshooting

​Cannot Connect to Database

​Migration Issues

​Prisma Client Generation Fails

​Schema Drift

​Best Practices

Use Migrations in Production

Connection Pooling

Regular Backups

Monitor Performance

​Next Steps

Environment Variables

Deployment Guide

Build docs developers (and LLMs) love

Overview

Database Requirements

PostgreSQL Setup

Option 1: Local PostgreSQL Installation

Option 2: Cloud PostgreSQL Providers

Prisma Schema Overview

Key Models

Database Initialization

First-Time Setup

Using Migrations (Production)

Common Prisma Commands

Build Script Integration

Production Deployment Workflow

Database Connection Pooling

Using PgBouncer (Recommended)

Troubleshooting

Cannot Connect to Database

Migration Issues

Prisma Client Generation Fails

Schema Drift

Best Practices

Next Steps