Skip to content

Version 1.1.8 ยท Last updated

Database Migration Baseline

0066_filing_capture_versions retains immutable filing metadata when captured license terms change for the same source bytes. It keeps the globally unique ingestion idempotency key, removes the earlier byte-only uniqueness constraint and adds a tenant/company/document/time index for latest-visible capture search. It neither rewrites license snapshots nor changes source dates or object bytes. Downgrade restores the old uniqueness constraint transactionally and fails if multiple captures share that identity. In that case restore the verified paired database and object-store backup; do not delete captured history to force rollback.

This project uses Alembic for every database schema change. Migrations are part of the product audit surface and must be deterministic, reversible, and free of test data.

Naming

  • Revision files use the Alembic file_template in alembic.ini: <revision>_<slug>.py.
  • Revision ids must remain within the database's alembic_version.version_num 32-character limit.
  • The initial baseline revision is 0001_database_migration_baseline.
  • Future revisions must use a monotonic numeric prefix in the revision id, followed by a short snake_case purpose, for example 0002_company_master_data.
  • Revision messages should describe one schema capability. Do not combine unrelated data domains in one migration.

Rollback

  • Every migration must implement downgrade().
  • Downgrade must reverse only the objects introduced by that revision.
  • Migrations must not insert test data. Use pytest fixtures or explicit seed workflows for test and sample data.
  • A migration is not complete until upgrade, downgrade, and a second upgrade succeed against an empty PostgreSQL instance.
  • 0021_low_cost_sources_backfill downgrade removes only source rows that the revision itself backfilled and that still have no run/raw history; operator-created sources and used backfill sources remain intact with their history at revision 0020_connector_raw_records.

Baseline Extensions

The baseline migration enables the PostgreSQL capabilities required by the plan:

  • unaccent for full-text search normalization support.
  • pg_trgm for fuzzy text matching.
  • vector from pgvector for later vector search.

It also creates the base argus schema for future application tables.

P2 entity and provider foundation

Revision 0031_entity_provider_foundation adds effective-dated security_identifier_mappings, provider_catalog, and provider_field_catalog. Identity rows retain provider, original field, mapping version, observed/known times, license, restrictions, and quality code. Fixtures remain in tests/fixtures; the migration does not insert sample data.

P3 US equity, ETF, and option stores

Revision 0032_us_equity_etf_options adds listing history, US market sessions, ETF profiles/holdings/NAV/distributions, traceable adjustment factors, symbol mapping events, option contracts, and option market data. Holdings and NAV have indexes over effective, publication, and known time. Greeks are stored only as provider payloads with model and input versions. The revision contains no strategy, account, order, or transaction tables and is fully reversible.

P5 deterministic feature catalog

Revision 0033_feature_catalog adds the versioned metric_catalog, canonical feature_execution_cache, and append-oriented derived_value_lineage records. Cached outputs bind request and catalog hashes; lineage stores every transitive fact, evidence, provider, as-of/known-at time, and formula version. The migration contains no executable formula text, test data, stream catalog, subscription, strategy, portfolio, or order tables.

External business data governance

Revisions 0035_provider_product_governance through 0053_remove_fmp add the external commercial data layer and provider retirement controls:

  • 0035_provider_product_governance adds product-level capability, entitlement, quota, coverage, delay, credential, and redistribution status. Its downgrade removes only the connector registrations introduced by this product layer before older connector-kind constraints are restored.
  • 0036_strict_market_observations adds typed Bar, Quote, and Trade storage with observation-type, timestamp-ordering, provenance, and evidence checks.
  • 0037_controlled_evidence_storage adds access-classified, hash-addressed evidence content and public-projection state while retaining legacy audit text for reversible migration.
  • 0038_public_company_events adds the metadata-only public event projection and a closed company-event taxonomy.
  • 0039_entity_security_governance adds governed historical entity, security, listing, and field-level identifier assertions.
  • 0040_etf_action_revisions adds ETF source assertions and effective, record, pay, revision, and adjustment-policy metadata for corporate actions.
  • 0041_filing_fact_provenance adds SEC accession/XBRL source-of-record, period, unit, dimension, revision, filed, accepted, and known-time metadata.
  • 0042_ir_ownership_calendar adds structured IR, insider, ownership, and company-calendar metadata without a public text column.
  • 0043_official_macro_vintages adds official macro observations with release, known, revision, and vintage time.
  • 0044_option_contracts adds authoritative option-contract source assertions.
  • 0045_licensed_option_market_gate adds explicit entitlement decisions and strict option Quote/Trade observations; it does not grant an OPRA license.
  • 0046_connector_kind_expansion lets connector registry, run, and raw-record persistence use the independent company_calendar, option_contracts, and option_market_data kinds. Its downgrade removes only the newly registered source rows before restoring the previous closed kind constraints.
  • 0047_macro_period_uniqueness extends the official macro vintage uniqueness key with period_start and period_end, so one release vintage can store multiple observation periods without collisions. It also makes provider release, vintage, and revision fields nullable so ingestion time is never presented as source release metadata when an official API omits it. Its downgrade refuses to restore the narrower legacy key while rows would collide or unknown source-time semantics remain; the operator must archive or enrich those records explicitly first.
  • 0048_event_evidence_provider persists immutable provider identity on event evidence and backfills known connector lineage for public timeline and point-in-time reads.
  • 0049_market_action_provider applies the same provider-identity persistence and backfill to market-data and corporate-action evidence, closing renamed or proxied provider-lineage gaps across direct and point-in-time public reads.
  • 0050_public_source_identity persists provider identity on normalized facts, controlled source-material metadata, and all company/security master tables. It backfills connector-registry lineage so renamed sources and proxy URLs retain their provider identity.
  • 0051_metric_evidence_identity persists the complete non-public evidence identity for neutral metric results, including provider identity. Legacy rows whose source identity cannot be reconstructed remain nullable in storage and are excluded from public point-in-time output until explicitly reprocessed.
  • 0052_remove_finnhub removes the Finnhub connector registration and destructively deletes its events, facts, controlled-material metadata, raw payloads, run history, and linked audit rows. Canonical and historical provider aliases are both removed. Its downgrade intentionally cannot reconstruct deleted provider data.
  • 0053_remove_fmp removes the FMP connector registration and destructively deletes its facts, evidence, unshared master data, raw payloads, run history, provider catalogs, and linked audit rows. Master entities referenced by a surviving non-FMP record are retained to preserve referential integrity and remain excluded from public reads by permanent source-retirement rules. Canonical and historical provider aliases are both removed. Its downgrade intentionally cannot reconstruct deleted provider data.

Production migrations must run through python -m argus.database_migrate. Before Alembic applies a provider-retirement revision, this entrypoint deletes unshared controlled evidence objects from S3 or filesystem storage and removes their metadata in the same database transaction. The cleanup holds both controlled-evidence metadata tables against writes for its complete object-purge window, so an older worker in a zero-downtime rollout cannot race in a new reference or duplicate URI. Direct Alembic execution fails closed when an unpurged retired object is present, preventing orphaned licensed content.

All revision ids remain within the 32-character Alembic version-column limit. The integration suite upgrades from the prior deployed revision, verifies the new head, downgrades through the chain to base, and upgrades to head again on a real PostgreSQL/pgvector container.