# Hand-authored curated contract for a transform/python product (ADR 0015/0019).
# The python job is an independent producer, so it owns a contract for the product it
# writes — committed here rather than generated, since dbt's generator does not run python.
schema_version: 0.1.0
kind: product
id: finance.monthly_revrec_collections
name: monthly_revrec_collections
version: 0.3.1
layer: curated
owner:
  type: team
  name: data-platform
domain:
  name: finance
purpose: Curated-layer product monthly_revrec_collections (one row per org and bill month, with
  billing and collection info), produced by the transform layer (the Python revenue-recognition
  job transform/python/jobs/monthly_cloud_billing_revrec.py). Output parity with the legacy
  BigQuery/dbt Python model is proven by the monthly_cloud_billing_revrec output harness.
usage:
  intended:
  - Cloud revenue-recognition collection analysis
  - Downstream modeling that accepts the documented monthly_cloud_billing_revrec parity scope
  - Agent-mediated querying with upstream revenue-source provenance
  prohibited:
  - Use as a source-faithful raw record (see the raw-snapshot contract).
  - Treat the upstream Revenue__c freeze as the go-forward revenue business process.
assets:
- id: monthly_revrec_collections
  kind: table
  ref: transform_python_dwh.monthly_revrec_collections
  materialization: table
  produced_by:
    kind: python
    ref: monthly_cloud_billing_revrec
schema:
  kind: relational
  columns:
  - name: bill_month
    type: VARCHAR
    nullable: true
  - name: adjust_tenant_id
    type: VARCHAR
    nullable: true
  - name: start_date
    type: DATE
    nullable: true
  - name: end_date
    type: DATE
    nullable: true
  - name: account_surrogate_key
    type: VARCHAR
    nullable: true
  - name: account_id
    type: VARCHAR
    nullable: true
  - name: account_name
    type: VARCHAR
    nullable: true
  - name: account_region
    type: VARCHAR
    nullable: true
  - name: payment_type
    type: VARCHAR
    nullable: true
  - name: marketplace_amount
    type: DOUBLE
    nullable: true
  - name: stripe_amount
    type: VARCHAR
    nullable: true
  - name: wire_transfer_amount
    type: DOUBLE
    nullable: true
  - name: total_amount
    type: DOUBLE
    nullable: true
  - name: stripe_tax_rate
    type: VARCHAR
    nullable: true
  - name: stripe_amount_including_tax
    type: VARCHAR
    nullable: true
  - name: stripe_invoice_number
    type: VARCHAR
    nullable: true
  - name: stripe_invoice
    type: VARCHAR
    nullable: true
  - name: stripe_invoice_paid
    type: VARCHAR
    nullable: true
  - name: stripe_invoice_finalized_date
    type: VARCHAR
    nullable: true
  - name: stripe_invoice_paid_date
    type: VARCHAR
    nullable: true
  - name: invoice_amount
    type: DOUBLE
    nullable: true
  - name: actual_revenue_recognition_date
    type: VARCHAR
    nullable: true
freshness:
  max_age: P1D
tests:
- id: monthly_revrec_collections_not_empty
  type: sql
  severity: blocking
  description: The collections output must contain rows for the most recent completed Python
    revrec run.
  sql: select row_count from (select count(*) as row_count from transform_python_dwh.monthly_revrec_collections) where row_count = 0
- id: monthly_revrec_collections_org_month_unique
  type: sql
  severity: blocking
  description: Collections are emitted at one row per adjusted tenant and bill month.
  sql: select bill_month, adjust_tenant_id, count(*) from transform_python_dwh.monthly_revrec_collections group by 1, 2 having count(*) > 1
- id: monthly_revrec_collections_required_fields_present
  type: sql
  severity: blocking
  description: Fields used for output parity, order allocation joins, and collection totals
    must be present on every collections row. Payment-channel amount fields are nullable
    because only the active collection channel is populated by the legacy-compatible output.
  sql: >
    select *
    from transform_python_dwh.monthly_revrec_collections
    where bill_month is null
       or adjust_tenant_id is null
       or start_date is null
       or end_date is null
       or account_surrogate_key is null
       or account_id is null
       or account_name is null
       or account_region is null
       or total_amount is null
       or invoice_amount is null
    limit 1
- id: monthly_revrec_collections_total_matches_order_revenue
  type: sql
  severity: blocking
  description: Each org-month collection total must be fully allocated to monthly revrec order
    revenue and billing allocation amounts.
  sql: >
    with collections as (
      select
        adjust_tenant_id as tenant_id,
        bill_month,
        sum(total_amount) as collection_total
      from transform_python_dwh.monthly_revrec_collections
      group by 1, 2
    ),
    orders as (
      select
        tenant_id,
        bill_month,
        sum(revenue_amount) as order_revenue_total,
        sum(billing_allocation_amount) as billing_allocation_total
      from transform_python_dwh.monthly_revrec_orders
      group by 1, 2
    )
    select
      coalesce(c.tenant_id, o.tenant_id) as tenant_id,
      coalesce(c.bill_month, o.bill_month) as bill_month,
      c.collection_total,
      o.order_revenue_total,
      o.billing_allocation_total
    from collections c
    join orders o on c.tenant_id = o.tenant_id and c.bill_month = o.bill_month
    where abs(c.collection_total - o.order_revenue_total) > 0.01
       or abs(c.collection_total - o.billing_allocation_total) > 0.01
    union all
    select
      c.tenant_id,
      c.bill_month,
      c.collection_total,
      null as order_revenue_total,
      null as billing_allocation_total
    from collections c
    left join orders o on c.tenant_id = o.tenant_id and c.bill_month = o.bill_month
    where o.tenant_id is null
    union all
    select
      o.tenant_id,
      o.bill_month,
      null as collection_total,
      o.order_revenue_total,
      o.billing_allocation_total
    from orders o
    left join collections c on c.tenant_id = o.tenant_id and c.bill_month = o.bill_month
    where c.tenant_id is null
    limit 1
declared_lineage:
  upstreams:
  - kind: data_product
    ref: finance.salesforce_org_monthly_revrec
  - kind: data_product
    ref: finance.salesforce_order_daily_view
governance:
  classifications:
  - financial
  - customer_data
  access:
    default: restricted
  audit:
    required: true
    events:
    - export
    - agent_action
agent_permissions:
  default: deny
compatibility:
  change_policy: semver
semantics:
  terms:
  - name: salesforce_revrec_order_revenue_input
    description: This product consumes finance.salesforce_order_daily_view, whose accumulated
      revenue fields come from finance.salesforce_revrec.
  - name: monthly_cloud_billing_revrec_output_parity_scope
    description: >
      Output parity is defined against the legacy BigQuery/dbt monthly_cloud_billing_revrec
      Python model for the same billing_month, order_date, and revenue mode. PR 44 proved the
      selected parity case; issue 48 broadens the proof to latest selectors and all common
      billing months for the latest common order snapshot, while reporting source-only
      non-comparable parameter values and failing on key or field mismatches.
  entities:
  - name: org_bill_month
    description: One adjusted tenant or org for one bill month with collection and invoice context.
  metrics:
  - name: collection_total_amount
    expression: sum(total_amount)
    description: Total collection amount in the product's current parity revenue context.
    grain: org_bill_month
  - name: invoice_amount
    expression: sum(invoice_amount)
    description: Invoice amount attached to the collection record in the current parity
      revenue context.
    grain: org_bill_month