Database setup

Brautcloud uses PostgreSQL as its primary database for storing users, events, images, and authentication tokens.

Prerequisites

Install PostgreSQL

Install PostgreSQL 12 or higher. Verify your installation:

psql --version

You should see PostgreSQL 12.x or higher.

Start PostgreSQL service

Ensure the PostgreSQL service is running:

sudo systemctl start postgresql
sudo systemctl enable postgresql

Create database

Create a dedicated database for Brautcloud:

# Connect to PostgreSQL as superuser
psql -U postgres

Then create the database and user:

-- Create user
CREATE USER brautcloud_user WITH PASSWORD 'your_secure_password';

-- Create database
CREATE DATABASE brautcloud OWNER brautcloud_user;

-- Grant privileges
GRANT ALL PRIVILEGES ON DATABASE brautcloud TO brautcloud_user;

-- Connect to the database
\c brautcloud

-- Grant schema privileges
GRANT ALL ON SCHEMA public TO brautcloud_user;

Replace your_secure_password with a strong, randomly generated password. Store this securely as you’ll need it for the backend configuration.

Database schema

The application automatically creates tables on startup when spring.sql.init.mode=always is set. The schema includes four main tables:

Users table

Stores wedding couple accounts:

CREATE TABLE IF NOT EXISTS users (
  id SERIAL PRIMARY KEY,
  last_name VARCHAR(255),
  first_name_couple_one VARCHAR(255),
  first_name_couple_two VARCHAR(255),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  email VARCHAR(255) UNIQUE NOT NULL,
  email_verified BOOLEAN DEFAULT FALSE,
  password VARCHAR(255) NOT NULL,
  role VARCHAR(50) DEFAULT 'ROLE_USER' NOT NULL
);

Key fields:

id - Auto-incrementing primary key
email - Unique email address for authentication
password - Bcrypt hashed password
role - User role (default: ROLE_USER)
first_name_couple_one / first_name_couple_two - Names of the couple

Events table

Stores wedding events created by users:

CREATE TABLE IF NOT EXISTS events (
  id SERIAL PRIMARY KEY,
  event_name VARCHAR(255) NOT NULL,
  location VARCHAR(255),
  date TIMESTAMP,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  password VARCHAR(255),
  qr_code TEXT,
  user_id INTEGER NOT NULL,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

Key fields:

id - Auto-incrementing primary key
event_name - Name of the wedding event
password - Optional password for guest access
qr_code - QR code for easy event sharing
user_id - Foreign key to users table (cascade delete)

Images table

Stores metadata for uploaded images:

CREATE TABLE IF NOT EXISTS images (
  id SERIAL PRIMARY KEY,
  image_key TEXT NOT NULL,
  is_visible BOOLEAN DEFAULT TRUE,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  event_id INTEGER NOT NULL,
  FOREIGN KEY (event_id) REFERENCES events(id) ON DELETE CASCADE
);

Key fields:

id - Auto-incrementing primary key
image_key - S3 object key for the stored image
is_visible - Visibility flag for moderation
event_id - Foreign key to events table (cascade delete)

Actual image files are stored in AWS S3. The database only stores metadata and S3 object keys.

Refresh tokens table

Stores JWT refresh tokens for authentication:

CREATE TABLE IF NOT EXISTS refresh_tokens (
  id BIGSERIAL PRIMARY KEY,
  user_id BIGINT NOT NULL UNIQUE,
  token VARCHAR(255) NOT NULL UNIQUE,
  expires_at TIMESTAMP NOT NULL,
  CONSTRAINT fk_refresh_tokens_users 
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

Key fields:

id - Auto-incrementing primary key
user_id - One-to-one relationship with users (unique)
token - Unique refresh token string
expires_at - Token expiration timestamp

Database initialization

The Spring Boot application automatically initializes the database using the schema.sql file when started:

Automatic schema creation: Tables are created if they don’t exist
JPA entity mapping: Spring Data JPA validates entity mappings against the schema
Migration safe: Uses CREATE TABLE IF NOT EXISTS to prevent errors on restart

The spring.sql.init.mode=always setting runs schema initialization on every startup. For production, consider using a dedicated migration tool like Flyway or Liquibase.

Connection configuration

Configure the database connection in your backend environment variables:

export POSTGRES_URL="jdbc:postgresql://localhost:5432/brautcloud"
export POSTGRES_USER="brautcloud_user"
export POSTGRES_PW="your_secure_password"

Connection URL format

The JDBC URL follows this format:

jdbc:postgresql://[host]:[port]/[database]?[parameters]

Common parameters:

ssl=true - Enable SSL connection
sslmode=require - Require SSL
currentSchema=public - Set default schema
ApplicationName=brautcloud - Set application name for monitoring

Example with SSL:

jdbc:postgresql://db.example.com:5432/brautcloud?ssl=true&sslmode=require

Database migrations

Current approach

The application uses Spring Boot’s built-in schema initialization:

spring.sql.init.mode=always

This executes src/main/resources/schema.sql on every startup.

Production migration strategy

For production environments, implement a proper migration strategy:

Flyway
Liquibase

Add Flyway dependency to pom.xml:

<dependency>
    <groupId>org.flywaydb</groupId>
    <artifactId>flyway-core</artifactId>
</dependency>

Create migration files in src/main/resources/db/migration/:

V1__initial_schema.sql

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  -- ... table definition
);

Configure in application.properties:

spring.flyway.enabled=true
spring.flyway.baseline-on-migrate=true
spring.sql.init.mode=never

Add Liquibase dependency to pom.xml:

<dependency>
    <groupId>org.liquibase</groupId>
    <artifactId>liquibase-core</artifactId>
</dependency>

Create changelog in src/main/resources/db/changelog/:

db.changelog-master.xml

<?xml version="1.0" encoding="UTF-8"?>
<databaseChangeLog
    xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog
    http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-4.0.xsd">

    <include file="db/changelog/v1-initial-schema.xml"/>
</databaseChangeLog>

Performance optimization

Create indexes

Add indexes for frequently queried columns:

-- Index on user email for login queries
CREATE INDEX idx_users_email ON users(email);

-- Index on event user_id for user's events lookup
CREATE INDEX idx_events_user_id ON events(user_id);

-- Index on image event_id for event gallery queries
CREATE INDEX idx_images_event_id ON images(event_id);

-- Index on refresh token for authentication
CREATE INDEX idx_refresh_tokens_token ON refresh_tokens(token);

Configure connection pooling

Optimize database connections in application.properties:

# HikariCP connection pool settings
spring.datasource.hikari.maximum-pool-size=10
spring.datasource.hikari.minimum-idle=5
spring.datasource.hikari.connection-timeout=20000
spring.datasource.hikari.idle-timeout=300000
spring.datasource.hikari.max-lifetime=1200000

Enable query logging (development)

Monitor SQL queries during development:

spring.jpa.show-sql=true
spring.jpa.properties.hibernate.format_sql=true
logging.level.org.hibernate.SQL=DEBUG

Backup and recovery

Create backup

# Backup entire database
pg_dump -U brautcloud_user -h localhost brautcloud > backup.sql

# Backup with compression
pg_dump -U brautcloud_user -h localhost brautcloud | gzip > backup.sql.gz

# Backup schema only
pg_dump -U brautcloud_user -h localhost --schema-only brautcloud > schema.sql

Restore backup

# Restore from backup
psql -U brautcloud_user -h localhost brautcloud < backup.sql

# Restore from compressed backup
gunzip -c backup.sql.gz | psql -U brautcloud_user -h localhost brautcloud

Always test your backup and restore procedures before relying on them in production.

Monitoring

Check database size

SELECT 
  pg_size_pretty(pg_database_size('brautcloud')) as database_size;

Check table sizes

SELECT 
  schemaname,
  tablename,
  pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) AS size
FROM pg_tables
WHERE schemaname = 'public'
ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC;

Check active connections

SELECT 
  count(*) as connections,
  state
FROM pg_stat_activity
WHERE datname = 'brautcloud'
GROUP BY state;

Troubleshooting

Connection refused

If the backend can’t connect to PostgreSQL:

Verify PostgreSQL is running: systemctl status postgresql
Check the connection URL format
Verify firewall rules allow connections on port 5432
Check pg_hba.conf allows connections from your application

Authentication failed

If authentication fails:

Verify username and password are correct
Check user exists: SELECT * FROM pg_user WHERE usename = 'brautcloud_user';
Ensure user has database permissions
Review pg_hba.conf authentication method

Schema not found

If tables aren’t created:

Check spring.sql.init.mode=always is set
Verify schema.sql is in src/main/resources/
Review application startup logs for initialization errors
Ensure user has CREATE privileges

Next steps

Backend deployment

Deploy the Spring Boot backend with database configuration

Frontend deployment

Deploy the Angular frontend application

Get Started

Core Features

Deployment

Prerequisites

Create database

Database schema

Users table

Events table

Images table

Refresh tokens table

Database initialization

Connection configuration

Connection URL format

Database migrations

Current approach

Production migration strategy

Performance optimization

Backup and recovery

Create backup

Restore backup

Monitoring

Check database size

Check table sizes

Check active connections

Troubleshooting

Connection refused

Authentication failed

Schema not found

Next steps

Backend deployment

Frontend deployment

Build docs developers (and LLMs) love

Get Started

Core Features

Deployment

​Prerequisites

​Create database

​Database schema

​Users table

​Events table

​Images table

​Refresh tokens table

​Database initialization

​Connection configuration

​Connection URL format

​Database migrations

​Current approach

​Production migration strategy

​Performance optimization

​Backup and recovery

​Create backup

​Restore backup

​Monitoring

​Check database size

​Check table sizes

​Check active connections

​Troubleshooting

​Connection refused

​Authentication failed

​Schema not found

​Next steps

Backend deployment

Frontend deployment

Build docs developers (and LLMs) love

Prerequisites

Create database

Database schema

Users table

Events table

Images table

Refresh tokens table

Database initialization

Connection configuration

Connection URL format

Database migrations

Current approach

Production migration strategy

Performance optimization

Backup and recovery

Create backup

Restore backup

Monitoring

Check database size

Check table sizes

Check active connections

Troubleshooting

Connection refused

Authentication failed

Schema not found

Next steps