SQL Templates¶

How the SQL template system generates deployment-specific database initialization files from shared Jinja2 templates.

Overview¶

BigBrotr uses Jinja2 templates to generate PostgreSQL init scripts for each deployment variant (bigbrotr, lilbrotr, brotr). A base set of templates defines the minimal shared schema; each deployment extends the base via Jinja2 block overrides to add deployment-specific objects without duplicating the shared structure.

Directory Layout¶

tools/
+-- generate_sql.py              # Generator script
+-- templates/sql/
    +-- base/                    # 10 base templates (shared schema)
    +-- lilbrotr/                # Override templates (lightweight event table)

deployments/
+-- bigbrotr/postgres/init/      # 10 generated SQL files (DO NOT EDIT DIRECTLY)
+-- lilbrotr/postgres/init/      # 10 generated SQL files

Warning

Never edit the .sql files in deployments/*/postgres/init/ directly. They are generated from templates. Edit the Jinja2 templates in tools/templates/sql/ instead.

Base Templates¶

The 10 base templates define the minimal Brotr schema shared by all deployments:

Template	Purpose
`00_extensions.sql.j2`	PostgreSQL extensions (btree_gin, pg_stat_statements)
`01_functions_utility.sql.j2`	Utility function: `tags_to_tagvalues()`
`02_tables.sql.j2`	Core tables: relay, event, event_relay, metadata, relay_metadata, service_state
`03_functions_crud.sql.j2`	10 CRUD functions (inserts, upserts, cascade operations)
`04_functions_cleanup.sql.j2`	2 cleanup functions (orphan metadata + orphan event deletion)
`05_views.sql.j2`	Regular views (extension point for future use)
`06_materialized_views.sql.j2`	1 materialized view: `relay_metadata_latest`
`07_functions_refresh.sql.j2`	1 refresh function: `relay_metadata_latest_refresh()`
`08_indexes.sql.j2`	Performance indexes for tables and `relay_metadata_latest`
`99_verify.sql.j2`	Post-init verification script (schema summary)

Override Mechanism¶

Jinja2 Block Inheritance¶

Base templates define named blocks with extra_* extension points. Deployment-specific templates extend the base and override only the blocks they need to customize:

{# lilbrotr/02_tables.sql.j2 #}
{% extends "base/02_tables.sql.j2" %}

{% block header_comment %}
 * LilBrotr - 02_tables.sql
 * Lightweight schema: events store only essential fields.
{% endblock %}

{% block events_table %}
CREATE TABLE IF NOT EXISTS event (
    id BYTEA PRIMARY KEY,
    pubkey BYTEA NOT NULL,
    created_at BIGINT NOT NULL,
    kind INTEGER NOT NULL,
    tagvalues TEXT []
);
{% endblock %}

Blocks not overridden are inherited from the base template unchanged.

Extension Points¶

Base templates provide extra_* blocks that are empty by default. Deployments fill these blocks to add objects beyond the minimal schema:

Block	Used by	Content
`extra_cleanup_functions`	(none yet)	Additional cleanup functions
`extra_materialized_views`	bigbrotr	10 statistics/analytics matviews
`extra_refresh_functions`	bigbrotr	10 stat refresh + `all_statistics_refresh()`
`extra_matview_indexes`	bigbrotr	Indexes for statistics matviews
`views`	(none yet)	Regular SQL views

All deployments generate the same 10 SQL files. The OVERRIDES dict in generate_sql.py is empty for all deployments (no skip, no rename).

Commands¶

# Regenerate all SQL files from templates
make sql-generate

# Verify generated files match templates (used in CI)
make sql-check

make sql-check detects three types of drift:

MISSING: A template produces a file that doesn't exist on disk
MISMATCH: A generated file differs from what the template produces
ORPHAN: A .sql file exists in deployments/*/postgres/init/ that no template produces

Adding a New SQL File¶

Create tools/templates/sql/base/NN_name.sql.j2 with Jinja2 blocks for customization
Add "NN_name" to BASE_TEMPLATES in tools/generate_sql.py
Create override templates in tools/templates/sql/{deployment}/ as needed
Run make sql-generate to generate the new files
Run make sql-check to verify
Commit both the template and the generated .sql files

Modifying an Existing Template¶

Edit the base or override template in tools/templates/sql/
Run make sql-generate to regenerate
Review the generated SQL diff with git diff deployments/
Run make sql-check to verify consistency
Commit both template changes and regenerated files

Adding a New Deployment¶

Add an entry to OVERRIDES in tools/generate_sql.py
Create a directory tools/templates/sql/{deployment}/ (only if overrides are needed)
Add override templates for any blocks that need customization
Run make sql-generate
Create the deployment directory structure: deployments/{deployment}/

Setup -- Makefile targets including sql-generate and sql-check
Coding Standards -- SQL formatting conventions