Database Schema & Migrations

Relevant source files

The following files were used as context for generating this wiki page:

• backend/src/workflows/infrastructure/models.py
• backend/alembic/versions/
• backend/src/core/database.py
• CRM_IMPLEMENTATION_REPORT.md
• FUNNELS_BACKEND_IMPLEMENTATION_REPORT.md
• SPEC-WFL-007-IMPLEMENTATION-REPORT.md
• README.md

The database system in the GHLClone project is built on PostgreSQL 16 using SQLAlchemy 2.0 as the asynchronous Object-Relational Mapper (ORM) and Alembic for version-controlled schema migrations. The architecture is designed to support a multi-tenant environment, ensuring strict account-scoped data isolation through row-level filtering and strategic indexing.

The schema is partitioned into modular domain-specific tables covering Workflows, CRM, Funnels, and Analytics. This modularity allows for scalable maintenance and independent evolution of features like automated marketing workflows and customer relationship management.

Sources: README.md, CRM_IMPLEMENTATION_REPORT.md, FUNNELS_BACKEND_IMPLEMENTATION_REPORT.md

Core Infrastructure & Multi-Tenancy

The project utilizes a centralized database configuration located in the core module. It leverages SQLAlchemy's async capabilities to handle high-concurrency requests, particularly for background jobs and real-time updates.

Multi-Tenant Isolation

Every table within the system includes an account_id field. This is a critical security measure to ensure that data remains isolated between different customers. Most queries include an implicit or explicit filter on this field, which is further supported by composite indexes to maintain high performance.

Sources: README.md, CRM_IMPLEMENTATION_REPORT.md, SPEC-WFL-007-IMPLEMENTATION-REPORT.md

Primary Data Structures

Table Component	Type / Constraint	Description
id	UUID (Primary Key)	Globally unique identifier for every record.
account_id	UUID (Foreign Key)	The tenant identifier used for data isolation.
created_at	TIMESTAMPTZ	Audit timestamp for record creation.
updated_at	TIMESTAMPTZ	Audit timestamp for the last modification.
version	INTEGER	Used for optimistic locking to prevent race conditions.

Sources: SPEC-WFL-007-IMPLEMENTATION-REPORT.md, CRM_IMPLEMENTATION_REPORT.md

Domain-Specific Schemas

The database is organized into several major modules, each containing specific tables that interact through foreign key relationships.

CRM Module

The CRM module manages contacts, companies, deals, and activities. It utilizes JSONB fields for "Custom Fields," allowing flexible data storage without requiring frequent schema migrations for user-defined attributes.

Sources: CRM_IMPLEMENTATION_REPORT.md, backend/src/crm/README.md

Workflows & Goal Tracking

The Workflows module manages automation logic, including triggers, actions, and goal tracking. The workflow_goals and workflow_goal_achievements tables track progress and conversion rates within automated sequences.

Sources: SPEC-WFL-007-IMPLEMENTATION-REPORT.md, backend/src/workflows/infrastructure/models.py

Funnels & Analytics

The Funnels module includes tables for page builders, order processing, and partitioned analytics events.

• Partitioning: The analytics_events table is partitioned by month to ensure performance when handling large volumes of time-series data.
• Materialized Views: The system uses funnel_step_stats materialized views to provide pre-aggregated metrics for dashboard performance.

Sources: FUNNELS_BACKEND_IMPLEMENTATION_REPORT.md

Migration Workflow

Migrations are managed via Alembic, providing a robust way to track schema changes over time. Each major feature implementation includes a corresponding migration script.

Migration Execution

Developers use standard Alembic commands to move the database state forward or backward.

# Run database migrations to latest head
alembic upgrade head

# Rollback a migration
alembic downgrade -1

# Create a new migration based on model changes
alembic revision --autogenerate -m "Add goal tracking"

Sources: README.md, VALIDATION_CHECKLIST.md

Migration Flow Diagram

Performance & Optimization

The schema employs several strategies to maintain enterprise-grade performance:

1. Strategic Indexing: Composite indexes are used extensively, particularly for (account_id, workflow_id) and (account_id, contact_id) patterns to speed up tenant-specific lookups.
2. Full-Text Search: GIN indexes are applied to content fields in the crm_notes and communication tables to allow for fast searching of customer history.
3. Audit Trails: Every significant change is logged with created_by and updated_by user IDs alongside timestamps for full accountability.
4. Optimistic Locking: The version column in high-activity tables like workflow_goals prevents concurrent updates from overwriting data.

Sources: SPEC-WFL-007-IMPLEMENTATION-REPORT.md, CRM_IMPLEMENTATION_REPORT.md, FUNNELS_BACKEND_IMPLEMENTATION_REPORT.md

Conclusion

The database schema and migration system provide a scalable, secure, and performant foundation for the GHLClone platform. By combining PostgreSQL's robust relational features with flexible JSONB storage and time-series partitioning, the architecture accommodates both rigid business rules and dynamic, user-defined marketing data. The use of Alembic ensures that schema evolution is predictable and reversible across development and production environments.