PyCharter Configuration and Database Setup¶

PyCharter follows Airflow's configuration pattern for database credentials, making it easy to manage database connections across different environments. This guide covers database configuration, initialization, and migrations.

Table of Contents¶

Database Connection Configuration
Variable Injection in Configuration Files
Database Initialization
Database Migrations
API and UI Configuration
Worker and DLQ Configuration
Troubleshooting

Database Connection Configuration¶

Hosted agent preparation, verification, and audited receipt reads require their own explicit PYCHARTER_AGENT_VERIFICATION_URL, receipt HMAC key ring (PYCHARTER_AGENT_RECEIPT_KEYS), and active key (PYCHARTER_AGENT_RECEIPT_ACTIVE_KEY). They do not inherit the contract database URL or authentication secret. See Agent verification environment provisioning for the complete settings, dedicated SQL migrations, key rotation, and pycharter agent receipts sweep retention command.

Configuration Priority¶

PyCharter checks for database configuration in this order:

Command-line argument (highest priority)

pycharter db init postgresql://user:pass@localhost/pycharter

Environment variable: PYCHARTER_DATABASE_URL

export PYCHARTER_DATABASE_URL=postgresql://user:pass@localhost/pycharter

Config file: pycharter.cfg (in project root or ~/.pycharter/)

[database]
PYCHARTER_DATABASE_URL = postgresql://user:pass@localhost/pycharter

Alembic config: alembic.ini (fallback)

[alembic]
sqlalchemy.url = postgresql://user:pass@localhost/pycharter

Config File Locations¶

PyCharter looks for pycharter.cfg in:

Current working directory
~/.pycharter/pycharter.cfg (user home directory)
Project root (where alembic.ini is located)

SQLite path semantics¶

In pycharter.cfg: relative SQLite URLs (e.g. sqlite:///pycharter.db) are anchored to the directory that contains pycharter.cfg. This makes resolution stable regardless of the current working directory.
In environment variables and CLI flags: relative SQLite URLs are rejected — use absolute paths (e.g. sqlite:////absolute/path/to/pycharter.db) or rely on the config-file anchoring behavior.

SQLite defaults (WAL and timeouts)¶

For file-backed SQLite databases, PyCharter applies safe defaults on every connection:

PRAGMA foreign_keys=ON — enforce referential integrity
PRAGMA journal_mode=WAL — robust concurrent writers on one host
PRAGMA busy_timeout=5000 — wait up to 5 seconds on transient locks
PRAGMA synchronous=NORMAL — durability appropriate for WAL

These settings only apply to file-backed databases. In-memory SQLite (sqlite:// or sqlite:///:memory:) is not forced to WAL.

The optional queue contention soak uses four submitters and two claim/heartbeat/completion loops against a temporary SQLite database:

PYCHARTER_RUN_SOAK=1 python -m pytest tests/soak/test_sqlite_concurrency_soak.py

It runs for 600 seconds by default, with a 720-second test timeout, rejects task errors and requires real progress by every participant. It does not exercise API requests or actual pipeline execution; full service concurrency acceptance remains separate.

SQLite on local disk only¶

SQLite is designed for single-host usage. For production robustness:

Use SQLite only on local disks (ext4/xfs). Avoid network filesystems: NFS, SMB/CIFS, and cloud-sync mounts are not supported for WAL.
If you need more than one host writing to the same registry, use PostgreSQL.
pycharter where prints the effective database URL and warns when a SQLite path appears to be on a network filesystem.

Comparison with Airflow¶

Feature	Airflow	PyCharter
Env Var	`AIRFLOW__DATABASE__SQL_ALCHEMY_CONN`	`PYCHARTER_DATABASE_URL`
Config File	`airflow.cfg`	`pycharter.cfg`
Config Section	`[database]`	`[database]`
Config Key	`sql_alchemy_conn`	`PYCHARTER_DATABASE_URL`

Best Practices¶

Development: Use environment variables

export PYCHARTER_DATABASE_URL=postgresql://localhost/pycharter_dev

Production: Use config file or environment variables (set by deployment system)

# pycharter.cfg
[database]
PYCHARTER_DATABASE_URL = postgresql://user:pass@prod-db:5432/pycharter

CI/CD: Use environment variables (set in CI/CD secrets)

env:
  PYCHARTER_DATABASE_URL: ${{ secrets.DATABASE_URL }}

Docker: Use environment variables or mounted config file

ENV PYCHARTER_DATABASE_URL=postgresql://user:pass@db:5432/pycharter

Security Notes¶

Never commit pycharter.cfg with credentials to version control
Use environment variables or secrets management in production
Consider using pycharter.cfg.example as a template (without credentials)

Variable Injection in Configuration Files¶

PyCharter supports Docker Compose-style variable substitution in all YAML and JSON configuration files. This allows you to inject dynamic values from environment variables or config files into your configuration.

Syntax¶

PyCharter supports the following variable substitution syntax:

Basic substitution: ${VAR}
Replaces with value from environment variable or config file
If variable not found, returns original string (backward compatible)
Default value: ${VAR:-default}
Uses default if VAR is unset or empty
Useful for providing fallback values
Required variable: ${VAR:?error message}
Raises ValueError if VAR is unset or empty
Error message is included in the exception
Use for critical configuration that must be provided
Escaped literal: $${VAR}
Results in literal ${VAR} (no substitution)
Use when you need to include ${} in your output

Value Resolution Priority¶

Variables are resolved in the following priority order:

Context dictionary (if provided programmatically)
Environment variables (os.getenv())
pycharter.cfg [variables] section
pycharter.cfg [etl] section
Default value (if ${VAR:-default} syntax used)
Error (if ${VAR:?error} syntax used and variable not found)

Examples¶

Basic Usage¶

# pipeline.yaml — extract step source (see snippets/extract_*.yaml)
params:
  apikey: ${FMP_API_KEY}  # Basic substitution
  timeout: ${REQUEST_TIMEOUT:-30}  # Default to 30 if not set
  base_url: https://${API_HOST:-api.example.com}/v1  # Partial substitution with default

Required Variables¶

# pipeline.yaml — extract step source
params:
  apikey: ${FMP_API_KEY:?FMP_API_KEY is required}  # Will raise error if not set

Config File Variables¶

Define variables in pycharter.cfg:

# pycharter.cfg
[variables]
FMP_API_KEY=replace-with-your-key
API_HOST = api.example.com
DB_PORT = 5432

[etl]
default_timeout = 30
default_batch_size = 1000

Then use in YAML files:

# pipeline.yaml — extract step source
params:
  apikey: ${FMP_API_KEY}  # Resolved from config file if env var not set
  timeout: ${default_timeout}  # Resolved from [etl] section

Complex Examples¶

# pipeline.yaml — extract step source
base_url: https://${API_HOST}/v1
params:
  apikey: ${FMP_API_KEY:?API key required}
  timeout: ${TIMEOUT:-30}
headers:
  Authorization: Bearer ${API_KEY}
  X-API-Version: ${API_VERSION:-v1}

Escaped Variables¶

# When you need literal ${VAR} in output
message: "Cost is $${AMOUNT}"  # Results in: "Cost is ${AMOUNT}"

Supported Files¶

Variable substitution is applied when loading:

Pipeline configs: pipeline.yaml (and values referenced inside steps:)
Contract files: schema, metadata, coercion/validation rules, field bindings
All YAML/JSON loaded through PyCharter's parsers

Error Handling¶

When a required variable is missing, PyCharter raises a clear error:

ValueError: Environment variable FMP_API_KEY is required (from pipeline.yaml:12)

The error message includes: - The variable name - The error message (if provided) - The source file and line number (if available)

Best Practices¶

Use defaults for optional configuration:

timeout: ${TIMEOUT:-30}  # Default to 30 seconds

Use required syntax for critical values:

apikey: ${API_KEY:?API key is required}  # Fail fast if missing

Store secrets in environment variables, not config files:
```
export FMP_API_KEY="replace-with-your-key"
```

Store non-sensitive defaults in config files:

# pycharter.cfg
[variables]
API_HOST = api.example.com
DEFAULT_TIMEOUT = 30

Use partial substitution for URLs:

base_url: https://${API_HOST}/v1/endpoint

Worker and DLQ Configuration¶

pycharter worker reads env vars and [worker] options in pycharter.cfg (env-style keys). See also pycharter.cfg.example and the operability runbook Worker hardening.

Core worker settings¶

Setting	Default	Purpose
`PYCHARTER_WORKER_DB_URL`	inherits `PYCHARTER_DATABASE_URL`	Dedicated DB URL for the worker process
`PYCHARTER_WORKER_MODE`	`all`	Queues to poll: `all`, `validation`, or `pipeline` (also `--mode` on CLI)
`PYCHARTER_WORKER_POLL_INTERVAL_MS`	`500`	Poll interval when no job is available
`PYCHARTER_WORKER_CONCURRENCY`	`5`	Default poller count per enabled queue
`PYCHARTER_WORKER_VALIDATION_CONCURRENCY`	—	Validation queue pollers (defaults to `CONCURRENCY`)
`PYCHARTER_WORKER_PIPELINE_CONCURRENCY`	—	Pipeline queue pollers (defaults to `CONCURRENCY`)
`PYCHARTER_WORKER_DRAIN_TIMEOUT_S`	`30`	Graceful shutdown wait for in-flight jobs
`PYCHARTER_WORKER_MAX_ATTEMPTS`	`5`	Retry ceiling before terminal failure
`PYCHARTER_WORKER_LEASE_SECONDS`	`300`	Claim lease duration; stale claims are released after expiry
`PYCHARTER_WORKER_HEARTBEAT_SECONDS`	`60`	How often the worker extends an active lease (must be `< LEASE_SECONDS`)
`PYCHARTER_WORKER_RETRY_BACKOFF_BASE_MS`	`1000`	Initial retry backoff
`PYCHARTER_WORKER_RETRY_BACKOFF_MAX_MS`	`300000`	Maximum retry backoff
`PYCHARTER_WORKER_EXECUTION_TIMEOUT_S`	`300`	Per-job execution timeout
`PYCHARTER_WORKER_ID`	auto-generated	Stable worker identity for logs and claim ownership

DLQ settings¶

Terminal failures write structured records to the DLQ table (when enabled) and set the queue row to failed with optional dlq_record_id.

Setting	Default	Purpose
`PYCHARTER_WORKER_DLQ_ENABLED`	`true`	Write terminal failures to structured DLQ
`PYCHARTER_WORKER_DLQ_DATABASE_URL`	worker DB URL	Dedicated DLQ database URL
`PYCHARTER_WORKER_DLQ_TABLE_NAME`	`dead_letter_queue`	DLQ table name

Replay and discard: POST /api/v1/dlq/{id}/replay, POST /api/v1/dlq/{id}/discard.

Operability settings (opt-in)¶

Install optional dependencies when enabling these features: pip install pycharter[server].

Setting	Default	Purpose
`PYCHARTER_WORKER_METRICS_ENABLED`	`false`	Prometheus counters/histograms in-process
`PYCHARTER_WORKER_HEALTH_SERVER_ENABLED`	`false`	HTTP server for `/metrics` and `/healthz/*`
`PYCHARTER_WORKER_HEALTH_SERVER_HOST`	`0.0.0.0`	Bind address for health server
`PYCHARTER_WORKER_HEALTH_SERVER_PORT`	`9090`	Port for metrics and health probes
`PYCHARTER_WORKER_NOTIFY_LISTENER_ENABLED`	`false`	Postgres LISTEN wake-up (reduces poll latency)
`PYCHARTER_WORKER_NOTIFY_CHANNEL_PREFIX`	—	Optional prefix for `pycharter_worker_jobs` channel
`PYCHARTER_WORKER_RETENTION_ENABLED`	`false`	Background DELETE sweeps for terminal queue rows
`PYCHARTER_WORKER_RETENTION_VALIDATION_COMPLETED_DAYS`	`7`	Prune old completed validation jobs
`PYCHARTER_WORKER_RETENTION_VALIDATION_FAILED_DAYS`	`90`	Prune old failed validation jobs
`PYCHARTER_WORKER_RETENTION_PIPELINE_COMPLETED_DAYS`	`7`	Prune old completed pipeline jobs
`PYCHARTER_WORKER_RETENTION_PIPELINE_FAILED_DAYS`	`90`	Prune old failed pipeline jobs
`PYCHARTER_WORKER_RETENTION_SWEEP_INTERVAL_SECONDS`	`86400`	Retention sweep cadence
`PYCHARTER_WORKER_RETENTION_SWEEP_BATCH_SIZE`	`10000`	Max rows deleted per batch

Set a retention window to none, forever, keep, or -1 to disable pruning for that category.

For side-by-side overlap mapping with pystator, see Config Parity with PyStator.

Database Initialization¶

Quick Start by Database Type¶

PostgreSQL (Most Common)¶

PostgreSQL automatically initializes the schema when you connect:

from pycharter import PostgresContractStore

# Just connect - schema is created automatically!
store = PostgresContractStore("postgresql://user:pass@localhost/pycharter")
store.connect()  # Tables created automatically on first connection

# Ready to use
schema_id = store.store_json_schema(
    "user", {"type": "object", "version": "1.0.0"}, version="1.0.0"
)

MongoDB¶

MongoDB requires no initialization - collections are created automatically:

from pycharter import MongoDBContractStore

store = MongoDBContractStore(
    connection_string="mongodb://localhost:27017", database_name="pycharter"
)
store.connect()  # Collections and indexes created automatically

# Ready to use
schema_id = store.store_json_schema(
    "user", {"type": "object", "version": "1.0.0"}, version="1.0.0"
)

Redis¶

Redis requires no initialization - keys are created automatically:

from pycharter import RedisContractStore

store = RedisContractStore(
    connection_string="redis://localhost:6379/0", key_prefix="pycharter"
)
store.connect()  # Keys created automatically when storing data

# Ready to use
schema_id = store.store_json_schema(
    "user", {"type": "object", "version": "1.0.0"}, version="1.0.0"
)

In-Memory¶

In-memory store requires no initialization:

from pycharter import InMemoryContractStore

store = InMemoryContractStore()
store.connect()  # No database needed

# Ready to use
schema_id = store.store_json_schema(
    "user", {"type": "object", "version": "1.0.0"}, version="1.0.0"
)

PostgreSQL Initialization (Detailed)¶

Contract and pipeline Stores can add missing tables with connect(auto_initialize=True). Both clients may share one SQLite file or PostgreSQL database, in either opening order. Bootstrap includes foreign-key dependencies, serializes concurrent table creation, and rolls back failed DDL. SQLite schema translation is local to the bootstrap connection; shared ORM metadata is unchanged.

This Store bootstrap is additive: it does not alter existing tables or stamp an Alembic revision. API and worker startup still require a current migrated schema. Provision their databases with pycharter db init and use explicit pycharter db upgrade for subsequent migrations.

Method 1: Automatic Initialization (Recommended for Development)¶

from pycharter import PostgresContractStore

# Create store with connection string
store = PostgresContractStore(
    connection_string="postgresql://user:password@localhost:5432/pycharter"
)

# Explicitly opt in to creating missing Store tables.
store.connect(auto_initialize=True)  # The direct connect default is False.

# Ready to use!
schema_id = store.store_json_schema(
    "user", {"type": "object", "version": "1.0.0"}, version="1.0.0"
)

Method 2: Bootstrap Separately for Direct Store Clients¶

Direct Store clients can bootstrap once and use validation on later connections:

from pycharter import PostgresContractStore

# Step 1: Initialize schema (run once, e.g., in deployment script)
store = PostgresContractStore(connection_string="postgresql://...")
store.connect(auto_initialize=True)  # Creates schema
store.disconnect()

# Step 2: Connect with validation only (in application)
store = PostgresContractStore(connection_string="postgresql://...")
store.connect(auto_initialize=False, validate_schema_on_connect=True)

Method 3: Using CLI (Recommended for Services)¶

# Initialize schema
pycharter db init postgresql://user:pass@localhost/pycharter

Then in Python, connect with validation only:

from pycharter import PostgresContractStore

store = PostgresContractStore(connection_string="postgresql://...")
store.connect(auto_initialize=False, validate_schema_on_connect=True)

What Gets Created¶

CLI initialization creates the full migrated schema, including the alembic_version table. Explicit contract/pipeline Store bootstrap creates the client's missing tables, indexes, constraints and FK dependencies under the pycharter schema. It also fills missing support tables in a partially initialized Store database, while retaining existing tables and records.

Connection String Formats¶

PostgreSQL:

postgresql://[user[:password]@][host][:port][/database]

Examples: - postgresql://postgres:password@localhost:5432/pycharter - postgresql://user@localhost/pycharter (no password) - postgresql://user:pass@host.example.com:5432/pycharter (remote)

MongoDB:

mongodb://localhost:27017/database]

Examples: - mongodb://localhost:27017 -

mongodb://localhost:27017
-

mongodb://localhost:27017/pycharter` (replica set)

Redis:

redis://[password@]host[:port][/database]

Examples: - redis://localhost:6379 - redis://localhost:6379/0 (database 0) - redis://:password@localhost:6379 (with password)

Docker Setup¶

PostgreSQL¶

docker run -d \
  --name pycharter-postgres \
  -e POSTGRES_USER=postgres \
  -e POSTGRES_PASSWORD=postgres \
  -e POSTGRES_DB=pycharter \
  -p 5432:5432 \
  postgres:latest

Connection: postgresql://postgres:postgres@localhost:5432/pycharter

MongoDB¶

docker run -d \
  --name pycharter-mongo \
  -e MONGO_INITDB_ROOT_USERNAME=rootUser \
  -e MONGO_INITDB_ROOT_PASSWORD=rootPassword \
  -p 27017:27017 \
  mongodb/mongodb-community-server:latest

Connection: `mongodb://localhost:27017

Redis¶

docker run -d \
  --name pycharter-redis \
  -p 6379:6379 \
  redis:latest

Connection: redis://localhost:6379/0

CLI Initialization¶

If you're using PostgreSQL with the CLI, you can initialize the database:

# If you configured via environment variable or config file:
pycharter db init

# Or pass the connection string directly:
pycharter db init postgresql://user:password@localhost:5432/pycharter

This will: 1. ✅ Create all database tables (schemas, coercion_rules, validation_rules, metadata_records, owners, governance_rules, data_contracts, systems, domains, and join tables) 2. ✅ Create all indexes and unique constraints 3. ✅ Set up Alembic version tracking 4. ✅ Stamp the database with the current revision

Verify Initialization¶

Check that everything worked:

# Check current database revision
pycharter db current

# View migration history
pycharter db history

You should see output like:

Current database revision: ac3d8fcc3e60

Database Migrations¶

When you need to change the database structure (add columns, tables, indexes, etc.), you'll create and apply migrations using Alembic.

Notes: - Before the first API or worker start on PostgreSQL, run: pycharter db upgrade postgresql://user:pass@host/dbname - API, pycharter worker, pycharter stream run, and pycharter stream launch refuse to start when the schema is out of date, with a clear hint to run pycharter db upgrade. - A truly empty persistent SQLite database initializes through Alembic on first API/worker/streaming use, including creation of missing parent directories. Initialization uses one transaction; competing initializers wait and recheck the committed schema. Failures propagate and roll back the schema changes. - Existing nonempty databases must have exactly one current revision. Missing, empty, malformed, multiple, older or unknown revision metadata causes refusal before application connections open. Startup does not upgrade, stamp or repair these databases, including files containing unrelated tables or views. Inspect the selected database and use explicit database commands to provision or upgrade it. In-memory SQLite (e.g. for tests) remains exempt. - PostgreSQL no longer auto‑upgrades at API startup. Upgrades are explicit via pycharter db upgrade.

Embedding services can call pycharter.db.admission.ensure_database_ready(database_url) for the same startup behavior. The check-only helper ensure_revision_current(database_url) requires an existing current schema and never initializes it. An explicit startup URL does not change PYCHARTER_DATABASE_URL or redirect bootstrap to an ambient URL. Streaming startup does not require the optional FastAPI wrapper.

Quick Reference: Core Migration Commands¶

Check current database revision:

# Option 1: With database URL as argument
pycharter db current postgresql://user:pass@localhost:5432/pycharter

# Option 2: Using environment variable
export PYCHARTER_DATABASE_URL="postgresql://user:pass@localhost:5432/pycharter"
pycharter db current

View migration history:

pycharter db history

Run migration (upgrade to latest):

# Option 1: With database URL as argument
pycharter db upgrade postgresql://user:pass@localhost:5432/pycharter

# Option 2: Using environment variable
export PYCHARTER_DATABASE_URL="postgresql://user:pass@localhost:5432/pycharter"
pycharter db upgrade

Rollback migration (downgrade one step):

# Option 1: With database URL as argument
pycharter db downgrade postgresql://user:pass@localhost:5432/pycharter --revision -1

# Option 2: Using environment variable
export PYCHARTER_DATABASE_URL="postgresql://user:pass@localhost:5432/pycharter"
pycharter db downgrade --revision -1

Verify migration:

# Check current revision (should show the new migration)
pycharter db current

# Verify the schema matches the model (PostgreSQL)
# Connect to PostgreSQL and check:
# \d pycharter.metadata_records

Workflow Overview¶

Modify SQLAlchemy models in pycharter/db/models/
Generate migration using Alembic
Review migration file (auto-generated, may need manual edits)
Test migration on development database
Apply migration to production

Step-by-Step: Creating a Migration¶

Example: Adding a New Column¶

Let's say you want to add a description field to the json_schemas table.

Step 1: Modify the SQLAlchemy Model

Edit pycharter/db/models/json_schema.py:

from sqlalchemy import Column, Integer, String, JSON, DateTime, Text
from sqlalchemy.sql import func
from sqlalchemy.orm import relationship

from pycharter.db.models.base import Base


class JsonSchemaModel(Base):
    __tablename__ = "schemas"

    id = Column(Integer, primary_key=True, autoincrement=True)
    title = Column(String(255), nullable=False)
    data_contract = Column(String(255), nullable=False)
    version = Column(String(50), nullable=False)
    schema_data = Column(JSON, nullable=False)
    description = Column(Text, nullable=True)  # ← NEW COLUMN
    created_at = Column(DateTime(timezone=True), server_default=func.now())
    updated_at = Column(
        DateTime(timezone=True), server_default=func.now(), onupdate=func.now()
    )

    # ... rest of the model

Step 2: Generate Migration

Make sure your database URL is configured, then run:

# Set database URL (if not already configured)
export PYCHARTER_DATABASE_URL=postgresql://user:password@localhost:5432/pycharter

# Generate migration
alembic revision --autogenerate -m "Add description to schemas table"

This creates a new migration file in pycharter/db/migrations/versions/ like:

abc123def456_add_description_to_schemas_table.py

Step 3: Review the Generated Migration

Open the generated migration file and review it:

"""Add description to schemas table

Revision ID: abc123def456
Revises: ac3d8fcc3e60
Create Date: 2025-11-19 10:00:00.000000
"""

from alembic import op
import sqlalchemy as sa

# revision identifiers
revision = "abc123def456"
down_revision = "ac3d8fcc3e60"  # Points to previous migration
branch_labels = None
depends_on = None


def upgrade() -> None:
    # Add column
    op.add_column("schemas", sa.Column("description", sa.Text(), nullable=True))


def downgrade() -> None:
    # Remove column (for rollback)
    op.drop_column("schemas", "description")

Important: Review the migration carefully: - ✅ Check that upgrade() does what you expect - ✅ Check that downgrade() can reverse the changes - ✅ Verify column types, constraints, and defaults - ✅ Remove any unwanted changes (Alembic sometimes detects unrelated changes)

Step 4: Test the Migration

Test on a development database first:

# Apply the migration
pycharter db upgrade

# Verify the change
pycharter db current  # Should show new revision

# Test rollback (optional)
pycharter db downgrade --revision -1
pycharter db upgrade  # Re-apply

Step 5: Apply to Production

Once tested, apply to production:

# Backup database first!
pg_dump -h localhost -U user pycharter > backup.sql

# Apply migration
pycharter db upgrade

Common Migration Scenarios¶

Adding a New Table¶

1. Create Model (pycharter/db/models/new_table.py):

from sqlalchemy import Column, Integer, String, DateTime
from sqlalchemy.sql import func
from pycharter.db.models.base import Base


class NewTableModel(Base):
    __tablename__ = "new_table"

    id = Column(Integer, primary_key=True)
    name = Column(String(255), nullable=False)
    created_at = Column(DateTime(timezone=True), server_default=func.now())

2. Import in pycharter/db/models/__init__.py:

from pycharter.db.models.new_table import NewTableModel

3. Generate migration:

alembic revision --autogenerate -m "Add new_table"

Adding a Column¶

1. Modify model (add column to existing model)

2. Generate migration:

alembic revision --autogenerate -m "Add column_name to table_name"

Adding an Index¶

1. Modify model (add index):

from sqlalchemy import Index


class JsonSchemaModel(Base):
    # ... columns ...

    __table_args__ = (Index("idx_schemas_name_version", "name", "version"),)

2. Generate migration:

alembic revision --autogenerate -m "Add index to schemas"

Modifying a Column Type¶

1. Modify model (change column type)

2. Generate migration and manually edit if needed:

def upgrade() -> None:
    # Alembic may generate this, but you might need to adjust
    op.alter_column(
        "table_name",
        "column_name",
        type_=sa.String(500),  # New type
        existing_type=sa.String(255),
    )  # Old type

Migration Commands Reference¶

All commands can be used in two ways: 1. With database URL as argument: pycharter db <command> <database_url> 2. With environment variable: Set PYCHARTER_DATABASE_URL and run pycharter db <command>

View Current Revision¶

# Option 1: With database URL as argument
pycharter db current postgresql://user:pass@localhost:5432/pycharter

# Option 2: Using environment variable
export PYCHARTER_DATABASE_URL="postgresql://user:pass@localhost:5432/pycharter"
pycharter db current

View Migration History¶

pycharter db history

Upgrade to Latest¶

# Option 1: With database URL as argument
pycharter db upgrade postgresql://user:pass@localhost:5432/pycharter

# Option 2: Using environment variable
export PYCHARTER_DATABASE_URL="postgresql://user:pass@localhost:5432/pycharter"
pycharter db upgrade

Upgrade to Specific Revision¶

# Option 1: With database URL as argument
pycharter db upgrade postgresql://user:pass@localhost:5432/pycharter --revision abc123def456

# Option 2: Using environment variable
export PYCHARTER_DATABASE_URL="postgresql://user:pass@localhost:5432/pycharter"
pycharter db upgrade --revision abc123def456

Downgrade One Step¶

# Option 1: With database URL as argument
pycharter db downgrade postgresql://user:pass@localhost:5432/pycharter --revision -1

# Option 2: Using environment variable
export PYCHARTER_DATABASE_URL="postgresql://user:pass@localhost:5432/pycharter"
pycharter db downgrade --revision -1

Downgrade to Specific Revision¶

# Option 1: With database URL as argument
pycharter db downgrade postgresql://user:pass@localhost:5432/pycharter --revision ac3d8fcc3e60

# Option 2: Using environment variable
export PYCHARTER_DATABASE_URL="postgresql://user:pass@localhost:5432/pycharter"
pycharter db downgrade --revision ac3d8fcc3e60

Generate Migration (Manual)¶

# Set database URL (if not already configured)
export PYCHARTER_DATABASE_URL="postgresql://user:pass@localhost:5432/pycharter"

# Auto-generate from model changes
alembic revision --autogenerate -m "Description"

# Create empty migration (for manual SQL)
alembic revision -m "Description"

Migration Best Practices¶

1. Always Test First¶

Test migrations on a development database
Verify both upgrade() and downgrade() work
Test with real data if possible

2. Review Auto-Generated Migrations¶

Alembic's autogenerate is helpful but not perfect
Always review and edit migration files
Remove unwanted changes
Add data migrations if needed

3. Use Descriptive Messages¶

# Good
alembic revision --autogenerate -m "Add description column to schemas table"

# Bad
alembic revision --autogenerate -m "update"

4. Version Control¶

Commit migration files to version control
Never edit existing migrations that have been applied
Create new migrations for additional changes

5. Backup Before Production¶

# Always backup before applying migrations
pg_dump -h localhost -U user pycharter > backup_$(date +%Y%m%d_%H%M%S).sql

6. Data Migrations¶

If you need to migrate data (not just schema), add it to the migration:

def upgrade() -> None:
    # Schema change
    op.add_column("schemas", sa.Column("description", sa.Text(), nullable=True))

    # Data migration
    connection = op.get_bind()
    connection.execute(
        sa.text(
            "UPDATE schemas SET description = 'Default description' WHERE description IS NULL"
        )
    )

Complete Migration Workflow Example¶

# 1. Configure database
export PYCHARTER_DATABASE_URL=postgresql://user:pass@localhost:5432/pycharter

# 2. Initialize (first time only)
pycharter db init

# 3. Make changes to models
# Edit pycharter/db/models/json_schema.py

# 4. Generate migration
alembic revision --autogenerate -m "Add description to schemas"

# 5. Review migration file
# Edit pycharter/db/migrations/versions/xxx_add_description_to_schemas.py

# 6. Test migration
pycharter db upgrade
pycharter db current  # Verify

# 7. Apply to production (after testing)
pycharter db upgrade

API and UI Configuration¶

PyCharter reads API and UI settings from pycharter.cfg (sections [auth] and [ui]) or from environment variables. These apply when running pycharter api or when the UI is served by PyCharter.

Authentication (`[auth]`)¶

For development or single-user deployments, use local users defined in config:

[auth]
# JSON array of users: username, password, role. Used by POST /auth/login.
PYCHARTER_AUTH_USERS = [{"username": "admin", "password": "changeme", "role": "Admin"}]

# Required when using local users; used to sign JWT tokens.
PYCHARTER_AUTH_JWT_SECRET = your-secret-change-in-production

PYCHARTER_AUTH_USERS — List of {username, password, role} objects. Environment variable overrides config.
PYCHARTER_AUTH_JWT_SECRET — Secret for signing JWTs (required when auth is enabled).
PYCHARTER_AUTH_DISABLED — Set to 1 to disable authentication (all routes open; dev only).
PYCHARTER_AUTH_SERVICE_URL — Optional external auth service URL for production.

See pycharter.cfg.example for the full [auth] and [ui] sections.

UI (`[ui]`)¶

PYCHARTER_UI_THEME — light, dark, system, or a named palette (e.g. ocean, forest).
PYCHARTER_UI_APP_NAME — Name shown in the UI (default: PyCharter).

Troubleshooting¶

Configuration Issues¶

Problem: Can't connect to database

Solutions: - Check connection string format - Verify database is running - Check credentials - Verify network connectivity

PostgreSQL: "relation does not exist"¶

Solution: Schema wasn't initialized. Run:

store.connect(auto_initialize=True)

PostgreSQL: "permission denied"¶

Solution: User needs CREATE TABLE permission:

GRANT CREATE ON DATABASE pycharter TO your_user;

MongoDB: "authentication failed"¶

Solution: Check username/password in connection string:

# Correct format
connection_string = "mongodb://localhost:27017"

Redis: "Connection refused"¶

Solution: Make sure Redis is running:

docker ps | grep redis
# or
redis-cli ping  # Should return PONG

Migration Fails¶

Problem: Migration fails with error

Solution:

# Check current state
pycharter db current

# View migration history
pycharter db history

# Try to fix manually or rollback
pycharter db downgrade --revision -1

Common Migration Errors:

Migration fails with "column does not exist"¶

The column might have already been removed
Check current database state: pycharter db current
You may need to manually adjust the migration

Migration fails with "column already exists"¶

The column might already exist
Check the database schema directly (e.g., \d pycharter.metadata_records in PostgreSQL)
You may need to stamp the database: pycharter db stamp <revision>

Connection errors during migration¶

Verify PostgreSQL is running
Check connection string format
Ensure database exists and user has permissions

Tables Already Exist¶

Problem: pycharter db init says tables already exist

Solution: Use --force flag or stamp the database:

pycharter db init --force
# OR
alembic stamp head

Migration Out of Sync¶

Problem: Database state doesn't match migrations

Solution: Stamp database to current state:

# Check what revision database thinks it's at
pycharter db current

# Stamp to correct revision
alembic stamp head  # or specific revision

Need to Edit Migration¶

Problem: Need to modify a migration before applying

Solution: Edit the migration file before running pycharter db upgrade. Never edit migrations that have already been applied to production.

UI theme¶

The global color theme for the PyCharter web UI (light / dark / system) can be set via:

Environment variable: PYCHARTER_UI_THEME — one of light, dark, or system (follow OS preference). Default: light.
Config file: pycharter.cfg section [ui], key PYCHARTER_UI_THEME. Same values. Env overrides .cfg.

When the UI is served by the Python server (pycharter ui or similar), the theme is applied before first paint to avoid a flash. In development (next dev), the app fetches theme from the API and applies it after load.

UI app name¶

The website/app name shown in the UI (nav bar, login, settings) can be overridden via:

Environment variable: PYCHARTER_UI_APP_NAME — display name (e.g. My Company Portal). Default: PyCharter.
Config file: pycharter.cfg section [ui], key PYCHARTER_UI_APP_NAME. Env overrides .cfg.

Summary¶

Configuration: Use environment variables or config files (see Database Connection Configuration)
Initialization: Most stores auto-initialize on connect; PostgreSQL can use CLI: pycharter db init
Create Migration: Modify models → alembic revision --autogenerate -m "message"
Apply Migration: pycharter db upgrade
Rollback: pycharter db downgrade --revision -1
Check Status: pycharter db current

For more details, see: - pycharter/db/README.md - Database management overview - Data Journey Guide - Complete workflow guide - Alembic documentation: https://alembic.sqlalchemy.org/

Configuration Priority¶

PyCharter checks for database configuration in this order:

Command-line argument (highest priority)

pycharter db init postgresql://user:pass@localhost/pycharter

Environment variable: PYCHARTER_DATABASE_URL

export PYCHARTER_DATABASE_URL=postgresql://user:pass@localhost/pycharter

Config file: pycharter.cfg (in project root or ~/.pycharter/)

[database]
PYCHARTER_DATABASE_URL = postgresql://user:pass@localhost/pycharter

Alembic config: alembic.ini (fallback)

[alembic]
sqlalchemy.url = postgresql://user:pass@localhost/pycharter

Usage Examples¶

CLI Commands¶

Once configured, you can run commands without passing the database URL:

# Initialize database
pycharter db init

# Upgrade database
pycharter db upgrade

# Check current revision
pycharter db current

# View migration history
pycharter db history

Python Code¶

PostgresContractStore also uses the configuration:

from pycharter import PostgresContractStore

# Uses configuration automatically
store = PostgresContractStore()
store.connect()

# Or override with explicit connection string
store = PostgresContractStore("postgresql://other:db@localhost:5432/other_db")

Config File Locations¶

PyCharter looks for pycharter.cfg in:

Current working directory
~/.pycharter/pycharter.cfg (user home directory)
Project root (where alembic.ini is located)

Comparison with Airflow¶

Feature	Airflow	PyCharter
Env Var	`AIRFLOW__DATABASE__SQL_ALCHEMY_CONN`	`PYCHARTER_DATABASE_URL`
Config File	`airflow.cfg`	`pycharter.cfg`
Config Section	`[database]`	`[database]`
Config Key	`sql_alchemy_conn`	`PYCHARTER_DATABASE_URL`

Best Practices¶

Development: Use environment variables

export PYCHARTER_DATABASE_URL=postgresql://localhost/pycharter_dev

Production: Use config file or environment variables (set by deployment system)

# pycharter.cfg
[database]
PYCHARTER_DATABASE_URL = postgresql://user:pass@prod-db:5432/pycharter

CI/CD: Use environment variables (set in CI/CD secrets)

env:
  PYCHARTER_DATABASE_URL: ${{ secrets.DATABASE_URL }}

Docker: Use environment variables or mounted config file

ENV PYCHARTER_DATABASE_URL=postgresql://user:pass@db:5432/pycharter

Security Notes¶

Never commit pycharter.cfg with credentials to version control
Use environment variables or secrets management in production
Consider using pycharter.cfg.example as a template (without credentials)

PyCharter Configuration and Database Setup¶

Table of Contents¶

Database Connection Configuration¶

Configuration Priority¶

Config File Locations¶

SQLite path semantics¶

SQLite defaults (WAL and timeouts)¶

SQLite on local disk only¶

Comparison with Airflow¶

Best Practices¶

Security Notes¶

Variable Injection in Configuration Files¶

Syntax¶

Value Resolution Priority¶

Examples¶

Basic Usage¶

Required Variables¶

Config File Variables¶

Complex Examples¶

Escaped Variables¶

Supported Files¶

Error Handling¶

Best Practices¶

Worker and DLQ Configuration¶

Core worker settings¶

DLQ settings¶

Operability settings (opt-in)¶

Database Initialization¶

Quick Start by Database Type¶

PostgreSQL (Most Common)¶

MongoDB¶

Redis¶

In-Memory¶

PostgreSQL Initialization (Detailed)¶

Method 1: Automatic Initialization (Recommended for Development)¶

Method 2: Bootstrap Separately for Direct Store Clients¶

Method 3: Using CLI (Recommended for Services)¶

What Gets Created¶

Connection String Formats¶

Docker Setup¶

PostgreSQL¶

MongoDB¶

Redis¶

CLI Initialization¶

Verify Initialization¶

Database Migrations¶

Quick Reference: Core Migration Commands¶

Workflow Overview¶

Step-by-Step: Creating a Migration¶

Example: Adding a New Column¶

Common Migration Scenarios¶

Adding a New Table¶

Adding a Column¶

Adding an Index¶

Modifying a Column Type¶

Migration Commands Reference¶

View Current Revision¶

View Migration History¶

Upgrade to Latest¶

Upgrade to Specific Revision¶

Downgrade One Step¶

Downgrade to Specific Revision¶

Generate Migration (Manual)¶

Migration Best Practices¶

1. Always Test First¶

2. Review Auto-Generated Migrations¶

3. Use Descriptive Messages¶

4. Version Control¶

5. Backup Before Production¶

6. Data Migrations¶

Complete Migration Workflow Example¶

API and UI Configuration¶

Authentication ([auth])¶

UI ([ui])¶

Troubleshooting¶

Configuration Issues¶

PostgreSQL: "relation does not exist"¶

PostgreSQL: "permission denied"¶

MongoDB: "authentication failed"¶

Redis: "Connection refused"¶

Authentication (`[auth]`)¶

UI (`[ui]`)¶