Database Setup

The Daily Tracker API uses PostgreSQL as its primary database with Flyway for schema migrations. This guide covers database setup, schema structure, and migration management.

Requirements

PostgreSQL

Version: 12 or higherFeatures Used:

TIMESTAMPTZ for timestamps
Foreign key constraints
Cascade deletes

Flyway

Version: Managed by Spring BootConfiguration:

Baseline on migrate: enabled
Baseline version: 0

Database Connection

Connection String Format

The application accepts PostgreSQL connection strings in two formats:

jdbc:postgresql://host:port/database?schema=public

The application automatically handles both formats. Spring Boot’s DataSource configuration normalizes Prisma-style URLs to JDBC format.

Example Connections

Local
Neon
Render

DATABASE_URL="postgresql://postgres:password@localhost:5432/daily_scrum?schema=public"

DATABASE_URL="postgresql://user:[email protected]/daily_scrum?sslmode=require"

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

Database Schema

The database uses PascalCase table names (e.g., "User", "Task") with quoted identifiers, inherited from the original Prisma schema.

Tables Overview

User

Stores user accounts with email/password and Google OAuth support

Task

Individual tasks with status, priority, and relationships

Project

Project containers for organizing tasks

TaskType

Custom task types within projects

RefreshToken

JWT refresh tokens for authentication

Schema Details

User
Task
Project
TaskType
RefreshToken

Table: "User"
Schema: public

Columns:
- id              SERIAL PRIMARY KEY
- email           VARCHAR(255) UNIQUE NOT NULL
- password        VARCHAR(255)                    -- Nullable for Google OAuth users
- googleId        VARCHAR(255) UNIQUE             -- Google OAuth identifier
- geminiKey       VARCHAR(512)                    -- Encrypted Gemini API key
- language        VARCHAR(10) NOT NULL DEFAULT 'pt-BR'

Relationships:
- tasks           One-to-Many with Task
- projects        One-to-Many with Project

Notes:

password is hashed using BCrypt
geminiKey is AES encrypted
googleId is populated for OAuth users

Table: "Task"
Schema: public

Columns:
- id              SERIAL PRIMARY KEY
- title           VARCHAR(255) NOT NULL
- description     TEXT
- status          VARCHAR(50) NOT NULL            -- e.g., 'TODO', 'IN_PROGRESS', 'DONE'
- priority        VARCHAR(10) NOT NULL DEFAULT 'MEDIUM'  -- 'LOW', 'MEDIUM', 'HIGH'
- position        INTEGER                         -- For custom ordering
- createdAt       TIMESTAMPTZ NOT NULL
- updatedAt       TIMESTAMPTZ NOT NULL
- userId          INTEGER NOT NULL REFERENCES "User"(id)
- projectId       INTEGER REFERENCES "Project"(id)
- taskTypeId      INTEGER REFERENCES "TaskType"(id)

Relationships:
- user            Many-to-One with User (required)
- project         Many-to-One with Project (optional)
- taskType        Many-to-One with TaskType (optional)

Notes:

Timestamps are managed by JPA @PrePersist and @PreUpdate
position enables drag-and-drop ordering

Table: "Project"
Schema: public

Columns:
- id              SERIAL PRIMARY KEY
- name            VARCHAR(255) NOT NULL
- color           VARCHAR(50) NOT NULL DEFAULT '#8b949e'
- userId          INTEGER NOT NULL REFERENCES "User"(id)

Relationships:
- user            Many-to-One with User
- tasks           One-to-Many with Task (cascade delete)
- taskTypes       One-to-Many with TaskType (cascade delete)

Notes:

Default color is set by @PrePersist hook
Deleting a project cascades to tasks and task types

Table: "TaskType"
Schema: public

Columns:
- id              SERIAL PRIMARY KEY
- name            VARCHAR(255) NOT NULL
- projectId       INTEGER NOT NULL REFERENCES "Project"(id)

Relationships:
- project         Many-to-One with Project
- tasks           One-to-Many with Task

Notes:

Task types are project-specific
Deleting a task type nullifies related tasks (via @PreRemove)

Table: "RefreshToken"
Schema: public

Columns:
- id              SERIAL PRIMARY KEY
- token           TEXT NOT NULL UNIQUE
- userId          INTEGER NOT NULL REFERENCES "User"(id) ON DELETE CASCADE
- expiryDate      TIMESTAMPTZ NOT NULL

Relationships:
- user            Many-to-One with User

Notes:

Tokens expire after 30 days (configurable)
Cascade delete when user is deleted

Flyway Migrations

The application uses Flyway to manage database schema changes. Migrations run automatically on application startup.

Configuration

spring:
  flyway:
    baseline-on-migrate: true
    baseline-version: 0
    locations: classpath:db/migration

Migration Files

All migrations are located in src/main/resources/db/migration/:

V1: Add Gemini Key to User

File: V1__add_gemini_key_to_user.sql

ALTER TABLE public."User"
    ADD COLUMN IF NOT EXISTS "geminiKey" VARCHAR(512);

Adds support for storing encrypted Gemini API keys per user.

V2: Create Refresh Token Table

File: V2__create_refresh_token_table.sql

CREATE TABLE "RefreshToken" (
    id SERIAL PRIMARY KEY,
    token TEXT NOT NULL UNIQUE,
    "userId" INTEGER NOT NULL REFERENCES "User"(id) ON DELETE CASCADE,
    "expiryDate" TIMESTAMPTZ NOT NULL
);

Implements JWT refresh token storage for long-lived sessions.

V3: Add Language to User

File: V3__add_language_to_user.sql

ALTER TABLE public."User"
    ADD COLUMN IF NOT EXISTS "language" VARCHAR(10) NOT NULL DEFAULT 'pt-BR';

Adds internationalization support with Portuguese as default.

V4: Add Priority to Task

File: V4__add_priority_to_task.sql

ALTER TABLE public."Task"
    ADD COLUMN IF NOT EXISTS priority VARCHAR(10) NOT NULL DEFAULT 'MEDIUM';

Enables task prioritization (LOW, MEDIUM, HIGH).

V5: Add Position to Task

File: V5__add_position_to_task.sql

ALTER TABLE public."Task"
    ADD COLUMN IF NOT EXISTS position INTEGER DEFAULT NULL;

Supports custom task ordering for drag-and-drop functionality.

Migration Naming Convention

Flyway migrations must follow this pattern:

V{version}__{description}.sql

Examples:

V1__initial_schema.sql
V2__add_user_settings.sql
V3__create_notifications_table.sql

Never modify a migration that has already been applied. Always create a new migration to make changes.

Hibernate Configuration

The application uses Hibernate with specific settings:

spring:
  jpa:
    hibernate:
      ddl-auto: validate  # Does NOT create or modify tables
    properties:
      hibernate:
        dialect: org.hibernate.dialect.PostgreSQLDialect
        physical_naming_strategy: org.hibernate.boot.model.naming.PhysicalNamingStrategyStandardImpl

ddl-auto: validate means Hibernate only checks that entities match the database schema. It will not create or alter tables. All schema changes must be done via Flyway migrations.

Physical Naming Strategy

The PhysicalNamingStrategyStandardImpl preserves exact entity names, including PascalCase and quotes:

@Entity
@Table(name = "\"User\"", schema = "public")
public class User { ... }

Maps to: public."User"

Database Setup Instructions

Local Development
Neon (Serverless)
Render PostgreSQL

Install PostgreSQL

Download and install PostgreSQL 12+ from postgresql.org

Create Database

# Connect to PostgreSQL
psql -U postgres

# Create database
CREATE DATABASE daily_scrum;

# Grant permissions
GRANT ALL PRIVILEGES ON DATABASE daily_scrum TO your_user;

Set Environment Variable

export DATABASE_URL="postgresql://postgres:password@localhost:5432/daily_scrum?schema=public"

Run Application

Flyway will automatically create tables on first startup:

./mvnw spring-boot:run

Create Neon Project

Sign up at neon.tech
Create a new project
Copy the connection string

Configure Connection

DATABASE_URL="postgresql://user:[email protected]/daily_scrum?sslmode=require"

Deploy Application

Migrations run automatically on startup. No manual setup needed.

Create PostgreSQL Database

Go to Render Dashboard
Click New + > PostgreSQL
Choose a plan and region
Click Create Database

Get Connection String

Copy the Internal Database URL from the database info page

Configure Application

Add the connection string to your web service environment variables:

DATABASE_URL=<internal-database-url>

Troubleshooting

Migration Failed

Problem: Flyway migration fails with SQL errorSolutions:

Check database user has CREATE, ALTER, INSERT privileges
Verify migration SQL syntax is correct
Check flyway_schema_history table for failed entries
Manually fix database state and mark migration as resolved

Table Not Found

Problem: Application throws “relation does not exist” errorSolutions:

Ensure migrations have run successfully
Check that table names use correct quoting: \"User\"
Verify schema is set to public
Check Flyway baseline version matches applied migrations

Connection Refused

Problem: Cannot connect to PostgreSQL databaseSolutions:

Verify database is running: pg_isready -h localhost -p 5432
Check connection string format
Ensure firewall allows connections on port 5432
For cloud databases, verify IP allowlist settings

Schema Validation Failed

Problem: Hibernate validation fails on startupSolutions:

Ensure all migrations have been applied
Check entity annotations match database schema
Verify column types match between entity and database
Set ddl-auto: none temporarily to skip validation

Best Practices

Version Control

Always commit migration files to version control before deploying.

Test Migrations

Test migrations on a staging database before applying to production.

Backup Before Migration

Always backup production database before running new migrations.

Rollback Plan

Have a rollback strategy for failed migrations (e.g., restore from backup).

Environment Variables

Configure database connection strings

Deployment

Deploy with database configuration

API Reference

Explore data model via API endpoints

Get Started

Guides

Requirements

PostgreSQL

Flyway

Database Connection

Connection String Format

Example Connections

Database Schema

Tables Overview

User

Task

Project

TaskType

RefreshToken

Schema Details

Flyway Migrations

Configuration

Migration Files

Migration Naming Convention

Hibernate Configuration

Physical Naming Strategy

Database Setup Instructions

Troubleshooting

Best Practices

Version Control

Test Migrations

Backup Before Migration

Rollback Plan

Environment Variables

Deployment

API Reference

Build docs developers (and LLMs) love

Get Started

Guides

​Requirements

PostgreSQL

Flyway

​Database Connection

​Connection String Format

​Example Connections

​Database Schema

​Tables Overview

User

Task

Project

TaskType

RefreshToken

​Schema Details

​Flyway Migrations

​Configuration

​Migration Files

​Migration Naming Convention

​Hibernate Configuration

​Physical Naming Strategy

​Database Setup Instructions

​Troubleshooting

​Best Practices

Version Control

Test Migrations

Backup Before Migration

Rollback Plan

​Related Documentation

Environment Variables

Deployment

API Reference

Build docs developers (and LLMs) love

Requirements

Database Connection

Connection String Format

Example Connections

Database Schema

Tables Overview

Schema Details

Flyway Migrations

Configuration

Migration Files

Migration Naming Convention

Hibernate Configuration

Physical Naming Strategy

Database Setup Instructions

Troubleshooting

Best Practices

Related Documentation