Local Development Guide

This guide explains how to set up your local development environment for working with tenant database schemas and migration scripts.

Repository Access

• The database repository is hosted in Bitbucket.
• For access, please contact the Aventian Team.

Repository Structure

The repo contains folders for each product schema under the migrations directory.

Folder structure:

├── .env
├── docker-compose.yml
├── ...
├── migrations/
│   ├── cleversort/
│   │   ├── R__create_update_functions.sql
│   │   ├── V2__create_tables.sql
│   │   ├── V3__insert_master_data.sql
│   │   ├── V6__create_mvw.sql
│   │   └── R__create_update_views.sql
│   ├── jotgo/
│   │   └── ...
│   ├── aventian/
│   │   ├── V1__init.sql
│   │   ├── V2__add_roles.sql
│   │   └── R__functions.sql
│   └── ...
└── ...

Note: Development work is mainly done inside the migrations folder, where each product schema maps to its own directory.

Local Setup

Prerequisites

• Docker installed and running.
• Repo cloned locally.

Steps

1. Clone the repo:

   git clone https://<bitbucket-username>@bitbucket.org/digitalavenues/tenant-db-src.git
   cd db-repo

2. Start local database replica:

   docker-compose up --build

   • This creates a local replica of the tenant DB.

   • Uses the credentials defined in the .env file inside the repo.

3. Connect using your preferred IDE (e.g., DBeaver, DataGrip, VSCode).

Optional: Connect to Hosted Tenant DB

⚠️ Strongly Not Recommended: Do not make schema or master data changes directly on hosted tenant DBs. Use this only for debugging.

Prerequisites

• kubectl installed.

• Access to Aventian GKE cluster (contact Aventian Team).

Steps

Run the following commands (one by one):

1. Run kubectl get pods -n postgres
2. Identify the tenant pod name which should look like tenant-<tenant-key>-postgres-0 Example - tenant-daipl-postgres-0
3. Run kubectl port-forward svc/postgres-service 5433:5432 -n <pod-name> Example - kubectl port-forward svc/postgres-service 5433:5432 -n tenant-daipl-postgres-0
4. You can now connect to the DB via localhost:5433 using admin credentials.

Development Guidelines

All schema and data changes must be written as migration scripts.
These migrations are applied across all tenant databases using Flyway.

Types of Migration Scripts

1. Versioned Scripts (V)

   • Used for CREATE, ALTER, INSERT, UPDATE, DELETE.

   • Naming convention:

     V1__init.sql V2__add_roles.sql V3__update_permissions.sql

   • Rules:

     • Prefix with V<number>__.

     • Numbers must be sequential (no duplicates).

     • Existing scripts must not be modified (Flyway will fail).

     • If version conflict occurs (e.g., two devs create V4), later dev must bump theirs to the next number (V5).

     • If Developer A is working on version V4 and Developer B selects V5, and V5 gets deployed, then Developer A should update their version to V6 to maintain the incremental order.

     • Teams should maintain a script version tracking sheet to avoid conflicts.

Example Template

Developer	Issue Link	Feature/Branch	Version Name	File Name	Logged At	In Progress	Notes
Dev 1	CL25-351	feature/CL25-341	V4	V4_create_table.sql	2025-07-20	Complete	New table - tbl_custom_field_type_count for tracking count
Dev 2	No Story	release/qa	V5	V5_insert_meta_assoc.sql	2025-07-21	Complete	Insert meta associations
Dev 2	No Story	release/qa	V6	V6_create_mvw.sql	2025-07-22	Complete	Create materialized view
Dev 1	CL25-1180	feature/CL25-1180	V7	V7_alter_custom_meta_options.sql	2025-07-25	Complete	New columns added in tbl_custom_meta and options
Dev 1	CL25-1281	feature/CL25-1281	V8	V8_create_uq_index_mvw_roles.sql	2025-08-01	Complete	Added unique index on mvw.roles for concurrently refreshing
Dev 3	CL25-1132	feature/CL25-1132	V9	V9_create_constraint_custom_meta.sql	2025-08-04	Complete	Altering custom_meta and custom_meta_options, added constraint on custom_meta (name, module_id, sub_id)
Dev 1	CL25-1281	feature/CL25-1281	V10	V10_create_uq_index_mvw_privileg.sql	2025-08-05	Complete	Added unique index on mvw_role_privilege_assoc for concurrently refreshing

2. Repeatable Scripts (R)

   • Used for functions and stored procedures.

   • Naming convention:

     R__functions.sql R__triggers.sql

   • Rules:

     • Can be re-applied by Flyway.

     • New functions are appended, existing functions are overwritten.

Release Pipeline

• Once code is pushed to release/qa branch:

  • The CI/CD pipeline is triggered.

  • Flyway automatically runs the migration scripts.

  • Changes are applied across all tenant databases in the system.

Notes

• Always test migrations locally before pushing.

• Never modify existing versioned migration scripts.

• Keep functions and procedures inside repeatable scripts.

• Contact Aventian Team for hosted DB access or master data changes.