# Commercial negotiated rates (TiC)

`vlada_curated.fact_rate` · Insurer negotiated rates · status partial

Cleaned commercial-insurer negotiated rates extracted from payer Transparency-in-Coverage machine-readable files. The substrate of "what does this payer actually pay this provider for this code."

**Grain.** One row per (payer, plan, NPI, code, modifier, billing_class, POS, negotiated_type) at a snapshot.

| column | type | meaning |
|---|---|---|
| rate_id | string |  |
| payer_id | string |  |
| entity | string |  |
| state | string |  |
| state_source | string |  |
| code | string |  |
| code_system | string |  |
| code_version | string |  |
| code_prefix | string |  |
| modifiers | array<string> |  |
| description | string |  |
| billing_class | string |  |
| negotiation_arrangement | string |  |
| negotiated_type | string |  |
| negotiated_rate | double |  |
| setting | string |  |
| service_codes | array<string> |  |
| bundled_codes | array<string> |  |
| expiration_date | string |  |
| provider_group_hash | string |  |
| raw_rate_identity | string |  |
| raw_provider_group_ids | array<bigint> |  |
| schema_version | string |  |
| version_id | string |  |
| canonical_source_file | string |  |
| source_hash | string |  |
| loaded_at | timestamp |  |
| commercial_mrf_version_id | string | FK to dim_commercial_mrf_version (RDS, migration 033). UUID as string. NULL for pre-Phase-C rows; populated by C.J re-extraction. Audit chain: row → dim → raw_s3_key → archived bytes → replay tool. |
| observed_period | date | M-2 universal: period the data describes. For commercial TiC MRFs, the YYYY-MM-01 of the manifest version. Substrate-wide name; distinct from version_id (payer's filename convention). |
| asserted_at | date | M-2 universal: date the source published this bytes-version. For commercial MRFs, derived from MRF's last_updated_on field (preferred) or HTTP Last-Modified header (fallback). Quality in asserted_at_source. |
| effective_period_start | date | M-2 universal: start of period this data is effective for reasoning. For commercial rates, equals observed_period (point-in-time pricing snapshot). M-3 catalogs per-table convention. |
| effective_period_end | date | M-2 universal: end of effective period. Equals start for point-in-time snapshots (typical for commercial TiC). Range for spans. |
| asserted_at_source | string | M-2 universal ENUM: mrf_last_updated_on / http_last_modified / filename_inferred / unknown_default. Quality marker for asserted_at. |
| calculation_epoch | date | M-2 universal NULLABLE: for sources with mid-cycle recalculation. Expected NULL for commercial TiC (no mid-cycle rerun semantics — payers republish full monthly manifests). |
| vintage | string | M-2 universal ENUM: as_published / as_restated. Binary display category. Commercial payers rarely restate but the column is required substrate-wide. |
| cms_vintage_sequence | int | M-2 universal: 1 for first publication, 2 for first restatement, etc. Advances ONLY on bronze sha256 change (payer-side). For commercial, advances when a payer republishes the same source file. |
| parse_revision | int | M-2 universal: substrate-side parser-bug-fix sequence. Advances on parser_artifact_hash bump for the same bronze object. Initial 1. Distinct from cms_vintage_sequence (payer-side changes). |
| restatement_of | string | M-2 universal NULLABLE (UUID-as-string): FK to prior vintage's commercial_mrf_version_id when a payer restates. NULL for cms_vintage_sequence=1 rows. |
| is_current | boolean | M-2 universal: TRUE for the most recent (cms_vintage_sequence, parse_revision) of this (natural_key, observed_period) tuple. Invariant: exactly one TRUE per natural key. |
| snapshot_set | string | M-2 universal: JSON-encoded MAP<table_fqn, iceberg_snapshot_id> covering every silver table read to produce this row. Bronze-anchored: single-entry self-reference. Derived: multi-entry upstream map. Phase 1 JSON; native MAP later. |
| s3_version_id | string | M-2 universal NULLABLE: exact S3 VersionId of the bronze object this row was parsed from. NULL for derived rows. Detects silent payer republishes that don't bump filename. |
| bronze_object_key | string | M-2 universal: full S3 key of bronze object (substrate-wide name). ALIAS of raw_s3_key (dim_commercial_mrf_version.raw_s3_key) during transition. Future migration drops the legacy name. |
| sha256_content_hash | string | M-2 universal: SHA-256 of bronze content (substrate-wide name). Populated by Rust extractor FU-9 streaming primitive. Same value as dim_commercial_mrf_version.content_sha256. |
| source_lineage | string | M-2 universal NULLABLE: JSON-encoded ARRAY<STRUCT<table_fqn, snapshot_id, row_set_fingerprint>>. Derived-row lineage. NULL on bronze-anchored fact_rate; populated on mart_unified_pricing joins. Phase 1 JSON; native ARRAY<STRUCT> later. |
| parent_vintage_fingerprint | string | M-2 universal NULLABLE: SHA-256 of sorted-concatenated upstream (s3_version_id // snapshot_id) tuples. Used by query helper to emit cascade_stale caveat when upstream vintages advance. |
| source_id | string | M-2 universal: stable source-family identifier from M-1's source registry. For commercial fact_rate, value like 'commercial_in_network_tic' or 'commercial_allowed_amount_tic'. |
| source_url | string | M-2 universal: original download URL for this row's bronze object. For commercial, the MRF URL from payer_toc_files.file_url at fetch time. Useful for replay-tool re-fetch if archived bytes corrupt. |
| parser_artifact_hash | string | M-2 universal: SHA-256 of parser code artifact (Rust extractor binary). Stamped at compile time via VLADA_PARSER_ARTIFACT_HASH env var. Reparse decisions key on this hash. Same value as dim_commercial_mrf_version.parser_artifact_hash. |
| ingested_at | timestamp | M-2 universal: UTC timestamp of silver write. Audit-only; never used for query semantics. Distinct from existing loaded_at if present (ALIAS during transition). |
| methodology_version | string |  |
| substrate_schema_version | string |  |
| builder_sha | string |  |
| nppes_vintage_at | timestamp |  |
| lookup_snapshot_id | string |  |

Tools that read it: get_negotiated_rate, lookup_rates_for_npi, lookup_rates_for_tin, plan_commercial_price_proof, prove_commercial_price, replay_commercial_price_proof

Query it: `query_substrate` with `SELECT ... FROM vlada_curated.fact_rate WHERE ... LIMIT 100`; read `describe_data_source("vlada_curated.fact_rate")` first.