Database Configuration

Overview

Hub uses PostgreSQL with PostGIS extension for spatial data support. The database stores venues, bookings, matches, users, and geographic data for location-based features.

PostGIS is required for geospatial queries like finding venues near a location.

Database Requirements

PostgreSQL: Version 16+ (recommended)
PostGIS: Version 3.4+
Extensions:
- postgis - Spatial data support
- pgcrypto - Cryptographic functions
- btree_gist - B-tree and GiST index support for constraints

Quick Start with Docker

The easiest way to run PostgreSQL with PostGIS is using Docker:

Configure environment

Create a .env file with database credentials:

.env

POSTGRES_DB=hub_db
POSTGRES_USER=hub_user
POSTGRES_PASSWORD=your_secure_password
DB_HOST=postgres
DB_PORT=5432
DB_NAME=hub_db
DB_USER=hub_user
DB_PASSWORD=your_secure_password
DB_SSL_MODE=disable

Start PostgreSQL

Use the provided Docker Compose configuration:

Terminal

docker-compose up -d postgres

This starts a PostgreSQL container with PostGIS pre-installed.

Verify connection

Check that PostgreSQL is running:

Terminal

docker ps | grep postgres
docker logs hub-postgres

Run migrations

Migrations run automatically when the backend starts. Check logs for:

Flyway migration completed successfully

Docker Compose Configuration

The docker-compose.yml includes a PostgreSQL service with PostGIS:

docker-compose.yml

services:
  postgres:
    image: postgis/postgis:16-3.4
    container_name: hub-postgres
    restart: unless-stopped
    environment:
      POSTGRES_DB: ${POSTGRES_DB}
      POSTGRES_USER: ${POSTGRES_USER}
      POSTGRES_PASSWORD: ${POSTGRES_PASSWORD}
    ports:
      - "5432:5432"
    volumes:
      - postgres-data:/var/lib/postgresql/data
    healthcheck:
      test: ["CMD-SHELL", "pg_isready -U ${POSTGRES_USER} -d ${POSTGRES_DB}"]
      interval: 5s
      timeout: 3s
      retries: 10

volumes:
  postgres-data:

Data is persisted in the postgres-data volume. To reset the database, run:

docker-compose down -v

Manual PostgreSQL Setup

If you’re not using Docker, follow these steps:

Install PostgreSQL

Install PostgreSQL 16+ on your system:

sudo apt update
sudo apt install postgresql-16 postgresql-contrib-16

Install PostGIS

Install the PostGIS extension:

sudo apt install postgresql-16-postgis-3

Create database and user

Connect to PostgreSQL and create the database:

SQL

-- Connect as postgres superuser
psql -U postgres

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

-- Create database
CREATE DATABASE hub_db OWNER hub_user;

-- Grant privileges
GRANT ALL PRIVILEGES ON DATABASE hub_db TO hub_user;

Enable extensions

Connect to the database and enable required extensions:

SQL

-- Connect to the hub database
\c hub_db

-- Enable extensions (Flyway will also run these)
CREATE EXTENSION IF NOT EXISTS postgis;
CREATE EXTENSION IF NOT EXISTS pgcrypto;
CREATE EXTENSION IF NOT EXISTS btree_gist;

-- Verify extensions
\dx

Environment Variables

Configure these environment variables for database connectivity:

DB_HOST

string

required

Database server hostnameLocal: localhostDocker: postgres (service name)Production: your-db-host.com

DB_PORT

string

required

Database server portDefault: 5432

DB_NAME

string

required

Database nameExample: hub_db, padelhub_production

DB_USER

string

required

Database user with read/write access

DB_PASSWORD

string

required

Database user password

Store securely and never commit to version control

DB_SSL_MODE

string

SSL/TLS mode for database connectionsOptions:

disable - No SSL (local development)
require - Require SSL, don’t verify certificate
verify-ca - Require SSL, verify certificate authority
verify-full - Require SSL, verify certificate and hostname

Default: disable

Use require or higher in production

Connection Pool Configuration

The application uses HikariCP for connection pooling:

application.yaml

spring:
  datasource:
    url: jdbc:postgresql://${DB_HOST}:${DB_PORT}/${DB_NAME}?sslmode=${DB_SSL_MODE:disable}&prepareThreshold=0
    username: ${DB_USER}
    password: ${DB_PASSWORD}
    hikari:
      auto-commit: false
      maximum-pool-size: 10
      minimum-idle: 2
      connection-timeout: 30000
      idle-timeout: 600000
      max-lifetime: 1800000

auto-commit: false improves performance by allowing explicit transaction management.

Database Schema

The schema is managed by Flyway migrations located in backend/src/main/resources/db/migration/.

Migration Files

V1__init.sql - Initial schema: cities with PostGIS spatial data
V2__users.sql - User accounts and profiles
V3__create_venue.sql - Venues (padel clubs)
V4__create_resource.sql - Resources (padel courts)
V5__create_booking.sql - Booking system
V6__create_payment.sql - Payment tracking
V7__create_match_request.sql - Match requests and invitations
V8__seed_dev_data.sql - Development seed data

Key Tables

city

Pre-populated table with Spanish cities and coordinates:

CREATE TABLE city (
  id           BIGSERIAL PRIMARY KEY,
  name         VARCHAR(100) NOT NULL,
  country_code VARCHAR(3) NOT NULL DEFAULT 'ES',
  latitude     DOUBLE PRECISION NOT NULL,
  longitude    DOUBLE PRECISION NOT NULL,
  location     geography(Point, 4326) NOT NULL
);

Contains 40 Spanish cities with PostGIS point geometries.

users

User accounts synchronized with Auth0:

auth0_id - Links to Auth0 user
email - User email
name - Display name
profile_picture - Cloudinary URL
skill_level - Padel skill level

venue

Padel clubs and facilities:

name - Venue name
city_id - References city table
location - PostGIS point for geospatial queries
photos - Array of Cloudinary URLs
manager_id - User who manages the venue

resource

Individual padel courts:

venue_id - Parent venue
name - Court name (e.g., “Court 1”)
surface_type - Court surface
is_indoor - Indoor/outdoor flag

booking

Court reservations:

resource_id - Court being booked
user_id - User making the booking
start_time - Booking start
end_time - Booking end
status - PENDING, CONFIRMED, CANCELLED
Constraint: No overlapping bookings per resource

Flyway Migrations

Flyway automatically manages database schema versions.

Configuration

application.yaml

spring:
  jpa:
    hibernate:
      ddl-auto: validate  # Ensures schema matches entities
  flyway:
    enabled: true
    locations: classpath:db/migration

Never set ddl-auto to create or update in production. Always use Flyway migrations.

Creating Migrations

To add a new migration:

Create SQL file

Create a new file following Flyway naming convention:

V{version}__{description}.sql

Example: V9__add_user_preferences.sql

Write migration

V9__add_user_preferences.sql

ALTER TABLE users ADD COLUMN notification_enabled BOOLEAN DEFAULT true;
ALTER TABLE users ADD COLUMN preferred_language VARCHAR(5) DEFAULT 'es';

CREATE INDEX idx_users_language ON users(preferred_language);

Test migration

Restart the application. Flyway will detect and run the new migration:

INFO : Migrating schema "public" to version "9 - add user preferences"
INFO : Successfully applied 1 migration

Migration Best Practices

Make migrations idempotent when possible
Use IF NOT EXISTS for CREATE statements
Test migrations on a copy of production data
Never modify existing migration files
Include rollback scripts for complex migrations

Geospatial Queries

PostGIS enables location-based features:

Find Venues Near Location

-- Find venues within 10km of coordinates
SELECT 
  v.id,
  v.name,
  ST_Distance(v.location, ST_MakePoint(-3.7038, 40.4168)::geography) as distance_meters
FROM venue v
WHERE ST_DWithin(
  v.location,
  ST_MakePoint(-3.7038, 40.4168)::geography,
  10000  -- 10km in meters
)
ORDER BY distance_meters;

Find Nearest City

-- Find closest city to coordinates
SELECT 
  c.name,
  ST_Distance(c.location, ST_MakePoint(-3.7038, 40.4168)::geography) as distance_meters
FROM city c
ORDER BY c.location <-> ST_MakePoint(-3.7038, 40.4168)::geography
LIMIT 1;

The <-> operator uses the spatial index for fast nearest-neighbor queries.

Database Tools

pgAdmin (Included with Docker)

A web-based PostgreSQL administration tool is included:

# Start pgAdmin
docker-compose up -d pgadmin

# Access at http://localhost:5050
# Email: admin@hub.com
# Password: admin

Connect to database:

Host: postgres (or host.docker.internal from host machine)
Port: 5432
Database: hub_db
Username: From POSTGRES_USER
Password: From POSTGRES_PASSWORD

Command Line Tools

docker exec -it hub-postgres psql -U hub_user -d hub_db

Production Configuration

Security Checklist

Enable SSL

Set DB_SSL_MODE=require or higher

Use strong passwords

Generate cryptographically secure passwords (min 32 characters)

Restrict network access

Use firewalls to limit database access to application servers only

Enable connection pooling

Configure appropriate pool sizes based on load:

hikari:
  maximum-pool-size: 20
  minimum-idle: 5

Regular backups

Implement automated backups with point-in-time recovery

Monitor performance

Use PostgreSQL’s pg_stat_statements extension to identify slow queries

Managed Database Providers

For production, consider managed PostgreSQL services:

AWS RDS for PostgreSQL (with PostGIS)
Azure Database for PostgreSQL
Google Cloud SQL for PostgreSQL
DigitalOcean Managed Databases
Supabase (includes PostGIS by default)

Ensure your managed database provider supports PostGIS extension.

Monitoring

Health Check

The application exposes a health check endpoint:

curl http://localhost:8080/actuator/health

Response:

{
  "status": "UP",
  "components": {
    "db": {
      "status": "UP",
      "details": {
        "database": "PostgreSQL",
        "validationQuery": "isValid()"
      }
    }
  }
}

Common Issues

Connection refused

Error: Connection to localhost:5432 refusedSolutions:

Check PostgreSQL is running: docker ps | grep postgres
Verify DB_HOST and DB_PORT are correct
Check firewall rules

Authentication failed

Error: password authentication failed for userSolutions:

Verify DB_USER and DB_PASSWORD match PostgreSQL user
Check pg_hba.conf authentication rules

PostGIS extension missing

Error: extension "postgis" does not existSolution:

CREATE EXTENSION postgis;

Migration checksum mismatch

Error: Validate failed: Migration checksum mismatchSolution: Never modify existing migrations. Create a new migration to fix issues.

Next Steps

Integrations

Configure Cloudinary and Brevo

API Reference

Explore API endpoints

Data Models

Understand data structure

Deployment

Deploy to production

Get Started

Core Features

User Guides

Configuration

Database Configuration

Overview

Database Requirements

Quick Start with Docker

Docker Compose Configuration

Manual PostgreSQL Setup

Environment Variables

Connection Pool Configuration

Database Schema

Migration Files

Key Tables

Flyway Migrations

Configuration

Creating Migrations

Migration Best Practices

Geospatial Queries

Find Venues Near Location

Find Nearest City

Database Tools

pgAdmin (Included with Docker)

Command Line Tools

Production Configuration

Security Checklist

Managed Database Providers

Monitoring

Health Check

Common Issues

Next Steps

Integrations

API Reference

Data Models

Deployment

Get Started

Core Features

User Guides

Configuration

​Overview

​Database Requirements

​Quick Start with Docker

​Docker Compose Configuration

​Manual PostgreSQL Setup

​Environment Variables

​Connection Pool Configuration

​Database Schema

​Migration Files

​Key Tables

​Flyway Migrations

​Configuration

​Creating Migrations

​Migration Best Practices

​Geospatial Queries

​Find Venues Near Location

​Find Nearest City

​Database Tools

​pgAdmin (Included with Docker)

​Command Line Tools

​Production Configuration

​Security Checklist

​Managed Database Providers

​Monitoring

​Health Check

​Common Issues

​Next Steps

Integrations

API Reference

Data Models

Deployment

Overview

Database Requirements

Quick Start with Docker

Docker Compose Configuration

Manual PostgreSQL Setup

Environment Variables

Connection Pool Configuration

Database Schema

Migration Files

Key Tables

Flyway Migrations

Configuration

Creating Migrations

Migration Best Practices

Geospatial Queries

Find Venues Near Location

Find Nearest City

Database Tools

pgAdmin (Included with Docker)

Command Line Tools

Production Configuration

Security Checklist

Managed Database Providers

Monitoring

Health Check

Common Issues

Next Steps