Database Schema¶

Carbon Connect uses PostgreSQL 16 with the pgvector extension for combined relational storage and 768-dimensional vector similarity search. The database is managed through SQLAlchemy 2.0 (async) with Alembic for migrations.

Entity Relationship Diagram¶

erDiagram
    tenants ||--o{ users : "has many"
    tenants ||--o{ companies : "has many"
    companies ||--o{ matches : "has many"
    companies ||--o{ applications : "has many"
    grants ||--o{ matches : "has many"
    grants ||--o{ applications : "has many"
    users ||--o| notification_preferences : "has one"
    users ||--o{ notification_history : "has many"
    applications ||--o{ documents : "has many"
    matches }o--|| applications : "optional link"

    tenants {
        uuid id PK
        string name
        string slug UK
        jsonb settings
        string subscription_tier
        boolean is_active
        timestamp created_at
        timestamp updated_at
    }

    users {
        uuid id PK
        uuid tenant_id FK
        string email
        string hashed_password
        string full_name
        string role
        boolean is_active
        boolean email_verified
        timestamp last_login_at
        timestamp created_at
        timestamp updated_at
    }

    companies {
        uuid id PK
        uuid tenant_id FK
        string name
        text description
        array nace_codes
        string country
        vector description_embedding
        float scope1_emissions
        float scope2_emissions
        float scope3_emissions
        array certifications
        vector carbon_profile_embedding
        timestamp created_at
        timestamp updated_at
    }

    grants {
        uuid id PK
        string external_id
        string source
        string title
        text description
        float funding_amount_min
        float funding_amount_max
        timestamp deadline
        array countries
        array nace_codes
        boolean is_carbon_focused
        array carbon_categories
        vector title_embedding
        vector description_embedding
        string content_hash
        timestamp created_at
        timestamp updated_at
    }

    matches {
        uuid id PK
        uuid tenant_id
        uuid company_id FK
        uuid grant_id FK
        float total_score
        float semantic_score
        float rule_score
        float carbon_score
        float collaborative_score
        float recency_score
        boolean is_viewed
        boolean is_saved
        boolean is_dismissed
        timestamp calculated_at
        timestamp created_at
        timestamp updated_at
    }

    applications {
        uuid id PK
        uuid tenant_id
        uuid company_id FK
        uuid grant_id FK
        uuid match_id FK
        string status
        float requested_amount
        float approved_amount
        jsonb generated_content
        timestamp submitted_at
        timestamp created_at
        timestamp updated_at
    }

Base Model¶

All models inherit from BaseModel which provides a UUID primary key and automatic timestamps:

Source: backend/app/db/base.py

Table Definitions¶

tenants¶

Multi-tenant organizations with subscription tiers. All tenant-scoped data is isolated via PostgreSQL Row-Level Security policies.

Relationships: Has many users, has many companies (both cascade delete).

users¶

User accounts with tenant association and role-based access control.

Relationships: Belongs to tenant, has one notification_preferences, has many notification_history.

companies¶

SME profiles with industry classification, carbon profile, and 768-dimensional description embeddings for semantic matching.

Carbon Profile Fields¶

Computed Properties (Python, not in database):

• company_size -- Returns micro (< 10), small (< 50), medium (< 250), or large based on employee_count_max
• total_emissions -- Sum of Scope 1 + Scope 2 + Scope 3 emissions
• carbon_readiness_score -- 0-100 score based on profile completeness

Relationships: Belongs to tenant, has many matches, has many applications.

grants¶

Grant opportunities with eligibility criteria, carbon classification, and 768-dimensional embeddings. Grants are NOT tenant-scoped -- they are shared across all tenants.

Carbon Classification Fields¶

Carbon Categories:

energy_efficiency, renewable_energy, clean_technology, circular_economy,
sustainable_transport, green_buildings, carbon_capture, hydrogen,
industrial_decarbonization, sustainable_agriculture, biodiversity,
water_management, waste_reduction, climate_adaptation

EU Taxonomy Objectives:

climate_mitigation, climate_adaptation, water_marine_resources,
circular_economy, pollution_prevention, biodiversity_ecosystems

Relationships: Has many matches, has many applications.

matches¶

Company-grant matching scores with full component breakdown. Matches are tenant-scoped through the tenant_id column and company relationship.

Unique Constraint: (company_id, grant_id) -- prevents duplicate matches.

Relationships: Belongs to company, belongs to grant.

applications¶

Grant application tracking with a Kanban-style status workflow.

Status Workflow:

stateDiagram-v2
    [*] --> draft
    draft --> in_progress
    draft --> submitted
    in_progress --> draft
    in_progress --> submitted
    submitted --> under_review
    under_review --> approved
    under_review --> rejected
    approved --> [*]
    rejected --> [*]

Relationships: Belongs to company, belongs to grant, has many documents.

Additional Tables¶

The database includes several supporting tables:

Indexes¶

Standard Indexes¶

Composite and single-column indexes for common query patterns:

Vector Indexes (pgvector)¶

HNSW (Hierarchical Navigable Small World) indexes for approximate nearest-neighbor search on 768-dimensional embeddings:

Alembic Migrations¶

Configuration¶

Alembic is configured in alembic/env.py to use the synchronous psycopg2 driver for migrations (asyncpg has compatibility issues on Windows). The database URL is pulled from the application Settings class.

All SQLAlchemy models are imported in alembic/env.py so that autogenerate can detect schema changes:

from backend.app.models.database import (
    Application, Company, Document, Grant, Match,
    NotificationHistory, NotificationPreferences,
    SyncRun, SyncStatus, Tenant, User,
)

Common Commands¶

# Apply all pending migrations
poetry run alembic upgrade head

# Roll back one migration
poetry run alembic downgrade -1

# Roll back to a specific revision
poetry run alembic downgrade <revision_id>

# Generate a new migration from model changes
poetry run alembic revision --autogenerate -m "Add new column to grants"

# View current migration state
poetry run alembic current

# View migration history
poetry run alembic history

# View migration history with verbose output
poetry run alembic history -v

Migration Best Practices¶

Connection Strings¶

# Async (application runtime)
postgresql+asyncpg://grant_user:grant_password_dev@localhost:5433/grant_engine?ssl=disable

# Sync (Alembic migrations)
postgresql://grant_user:grant_password_dev@localhost:5433/grant_engine

# Async (application runtime)
postgresql+asyncpg://{user}:{password}@{rds_endpoint}:5432/{database}

# Sync (Alembic migrations)
postgresql://{user}:{password}@{rds_endpoint}:5432/{database}