Database And Configuration

Database & Configuration

This document covers the database schema, data migrations, application configuration management, and external service configuration for the Effective Office backend system. It details the PostgreSQL database structure, Flyway migration setup, Spring Boot configuration profiles, and Google Calendar API integration setup.

For information about the actual booking workflows and calendar provider implementations, see [Booking Management](Booking Management). For deployment procedures and environment setup, see [Deployment and Operations](../Deployment and Operations).

Database Schema Overview

The system uses PostgreSQL as the primary database with Flyway for schema migrations. The database stores workspace information, user data, calendar integration details, and API authentication keys.

Core Database Schema

The schema supports the core office management features with workspace organization, user management, and calendar integration. The calendar_ids table maps workspaces to external calendar systems, while calendar_channels manages Google Calendar push notification subscriptions.

Database Migration System

The system uses Flyway for database migrations with the following configuration:

Property	Value	Purpose
spring.flyway.enabled	true	Enables Flyway migrations
spring.flyway.baseline-on-migrate	true	Creates baseline for existing databases
spring.flyway.locations	classpath:db/migration	Migration script location
spring.flyway.table	flyway_schema_history	Migration tracking table

Migration files follow the naming convention V{version}__{description}.sql. The initial migration V1__create_tables.sql creates the complete schema with proper indexes and constraints.

Database Configuration

Connection Configuration

Connection Pool Settings

Property	Default Value	Purpose
spring.datasource.hikari.connection-timeout	30000	Connection timeout in milliseconds
spring.datasource.hikari.maximum-pool-size	10	Maximum connections in pool
spring.datasource.hikari.minimum-idle	5	Minimum idle connections
spring.datasource.hikari.pool-name	EffectiveOfficeHikariCP	Pool identifier

JPA and Hibernate Configuration

The JPA configuration uses PostgreSQL-specific settings:

Property	Value	Purpose
spring.jpa.hibernate.ddl-auto	validate (prod), update (local)	Schema management strategy
spring.jpa.properties.hibernate.dialect	PostgreSQLDialect	Database-specific SQL generation
spring.jpa.properties.hibernate.format_sql	true	SQL formatting for readability
spring.jpa.open-in-view	false	Prevents lazy loading issues

Application Configuration Management

Configuration Profile Structure

Environment Variable Integration

The application supports .env file configuration through the spring-dotenv library.

Project-Level Configuration

Global project settings are defined in gradle.properties:

Property	Value	Purpose
group	band.effective.office	Maven group identifier
version	0.0.2	Application version
org.gradle.parallel	true	Enables parallel builds
org.gradle.caching	true	Enables build caching

Google Calendar Configuration

For comprehensive instructions on setting up Google Workspace and Google Calendar integration for your organization, see Google Workspace & Calendar Integration.

Calendar Provider Configuration

Google Calendar Properties

Property	Environment Variable	Purpose
calendar.provider	-	Selects calendar implementation (google or dummy)
calendar.application-name	CALENDAR_APPLICATION_NAME	Google API application identifier
calendar.delegated-user	CALENDAR_DELEGATED_USER	Email for domain-wide delegation
calendar.credentials.file	GOOGLE_CREDENTIALS_FILE	Path to service account credentials
calendar.default-calendar	DEFAULT_CALENDAR	Default calendar ID for bookings

OAuth2 Configuration

The GoogleCalendarConfig class creates the necessary beans for Google Calendar API access:

• GoogleCredentials: Handles OAuth2 authentication with calendar scopes
• HttpTransport: Provides HTTP transport for API calls
• Calendar: Main Google Calendar API client

The configuration supports domain-wide delegation through the delegated-user property, allowing the service account to impersonate users within a Google Workspace domain.

Calendar Subscription Configuration

Additional properties support the calendar subscription system for real-time updates:

Property	Environment Variable	Purpose
calendar.subscription.application-url	APPLICATION_URL	Webhook callback URL
calendar.subscription.calendars	CALENDARS	Calendar IDs to monitor
calendar.subscription.firebase-credentials	FIREBASE_CREDENTIALS	Firebase service account for push notifications

Exception Handling Configuration

The application includes global exception handling through the GlobalExceptionHandler class, which catches unhandled exceptions and returns standardized error responses using the ErrorDto data structure.

Deployment Configuration

Build and Deployment Tasks

The application includes Gradle tasks for automated deployment:

• deployProd: Deploys to production environment
• deployDev: Deploys to development environment with -dev suffix

Both tasks use rsync for efficient file transfer and support parameterized remote deployment.