Database Schema Overview - bcgov/lcfs GitHub Wiki

Database Schema Overview

This document provides an overview of the LCFS application's database schema, how it's managed, and where to find more detailed information.

The primary database for the LCFS application is PostgreSQL.

1. Schema Management - Alembic

Database schema migrations (changes over time) are managed using Alembic, a database migration tool for SQLAlchemy.

  • Alembic Configuration: backend/alembic.ini.
  • Migration Scripts: Individual migration scripts are located in backend/lcfs/db/migrations/versions/.
    • These scripts define the changes to be applied to the database schema (e.g., creating tables, adding columns, modifying constraints).
  • Seeder Scripts: Data seeding operations (populating initial or test data) can also be managed via Alembic migrations, typically found in backend/lcfs/db/seeders/ if this convention is followed, or as part of regular migration scripts.

Managing Migrations (backend/migrate.sh)

The backend/migrate.sh script is a utility to simplify common Alembic operations:

  1. Make the script executable (if not already):

    cd backend
chmod +x migrate.sh

  2. Generating a New Migration: When you make changes to SQLAlchemy models in backend/lcfs/db/models/, you need to generate a new migration script.

    ./migrate.sh -g "Your descriptive message about the changes"

    This will create a new file in backend/lcfs/db/migrations/versions/. You should then review and edit this script to ensure it accurately reflects the intended changes.

  3. Upgrading the Database (Applying Migrations): To apply pending migrations to your database (e.g., to upgrade to the latest version or a specific revision):

    ./migrate.sh -u [revision_id]
    • Omit [revision_id] to upgrade to the head (latest version).
    • The docker-compose up command for the main application may also automatically apply migrations on startup if its entrypoint script is configured to do so.

  4. Downgrading the Database (Reverting Migrations): To revert migrations:

    ./migrate.sh -d [revision_id]
    • Omit [revision_id] to revert all migrations back to the base state (an empty database from Alembic's perspective).
    • Use with caution, especially in environments with data.

  5. Displaying Help: For more options and help with the script:

    ./migrate.sh -h

2. ORM Models (SQLAlchemy)

The database schema is defined programmatically using SQLAlchemy ORM models.

  • Model Location: backend/lcfs/db/models/ (organized by domain, such as organization/, compliance/, fuel/, transaction/, and transfer/).
  • These Python classes define the tables, columns, relationships, and constraints of the database.
  • Alembic uses these model definitions (often by comparing them to the current database state) to auto-generate migration scripts.

3. Entity Relationship Diagram (ERD)

  • The current Entity Relationship Diagram is available: LCFS_ERD_v0.3.0.drawio (located in the project root).
  • This diagram was regenerated from the current SQLAlchemy metadata and covers 112 database tables and 178 foreign key relationships.
  • The v0.3.0 diagram is organized into 12 domain pages: Organization, User & Access, Compliance Reports, Final Supply Equipment, Charging Assets, Fuel, Transactions, Transfers, CI Applications, Documents & Comments, Notifications, and Administration & Audit.
  • This diagram provides a visual representation of the database schema, including tables, columns, primary keys, foreign keys, and relationships. Gray tables are external one-hop references included to show cross-domain relationships.
  • It is recommended to keep this diagram updated as the schema evolves. You can use draw.io (or a compatible desktop version) to view and edit this file.

4. Key Tables and Relationships (High-Level - To Be Expanded)

This section summarizes the major domains represented in LCFS_ERD_v0.3.0.drawio. The ERD should be used with the SQLAlchemy models for the full column-level view.

Examples of potential key entities (based on typical LCFS requirements):

  • Users & Organizations: User accounts, roles, permissions, and their association with organizations/companies.
  • Fuel Suppliers/Producers: Information about entities involved in fuel production and supply.
  • Fuel Types and Fuel Codes: Fuel categories, fuel instances, fuel codes, carbon intensity values, energy densities, energy effectiveness ratios, and transport modes.
  • Compliance Reports: Reports submitted for compliance periods, including fuel supplies, fuel exports, allocation agreements, notional transfers, other uses, summaries, report history, and organization snapshots.
  • Charging and Final Supply Equipment: Charging sites, charging equipment, charging statuses, charging power outputs, final supply equipment, and registration numbers.
  • Transactions and Transfers: Credit transactions, transfer records, transfer categories, transfer statuses, transfer comments, and transfer history.
  • Credit/Deficit Tracking: Ledger for LCFS credits and deficits.
  • Notifications: Government notifications, notification messages, types, channels, and channel subscriptions.
  • Administration and Audit: Administrative adjustments, initiative agreements, aggregator issuance, scheduled tasks, task executions, penalty logs, and audit logs.

5. Data Integrity

  • Primary Keys: Each table should have a primary key to uniquely identify records.
  • Foreign Keys: Relationships between tables are enforced using foreign key constraints.
  • NOT NULL Constraints: Applied to columns that must have a value.
  • UNIQUE Constraints: Ensure values in a column or set of columns are unique.
  • CHECK Constraints: Enforce specific conditions on data values.
  • Enum Types: PostgreSQL ENUM types (managed via alembic-postgresql-enum) are used for columns with a fixed set of possible values.

For the most accurate and detailed schema information, always refer to the SQLAlchemy models in backend/lcfs/db/models/ and the Alembic migration scripts. The ERD should be used as a visual guide and kept synchronized with the codebase.