Contract data export

The following tables are the tables which uniquely describe the contract data stored in Metronome. These tables are connected to a customer from the standard data-export via the contract's customer_id column.

Contract data export diagram

USD cents

Internally, Metronome stores USD values as "USD cents", meaning that $1 is represented as 100 and $0.0005 is represented as 0.05.

Common columns

`id`

Self-explanatory. This column uniquely identifies each row and, combined with the updated_at field, teaches our data-export tech which rows should be inserted or updated in target databases.

`updated_at`

This timestamp is used to communicate to our data-export tech that this row should be updated in target databases, it reflects the time that the row of data was captured from our internal system and not when the underlying data was updated.

`metadata`

Clients using contracts have different external systems that require different metadata to be stored on contracts (Netsuite or Salesforce ids, for example). This metadata is client specific and stored as a JSON object in the metadata column of each table and on some of the JSON encoded objects too.

The metadata column will also include any custom fields under a custom_fields JSON object if they exist.

`snapshot_id`

Contract configuration is captured by calculating regular snapshots from our internal data stores. Use the snapshot_id to always select the most recent snapshot when querying exports which include multiple values for the same id. This column is recorded as an ISO8601 formatted UTC datetime string so lexicographic sorting will always select the most recent snapshot.

Contract pricing tables

`rate_cards`

Column	Type	Description
`id`	`string`
`name`	`string`
`description`	`string`
`aliases`	`RateCardAlias[]`	JSON encoded
`created_at`	`date`
`created_by`	`string`
`updated_at`	`date`
`metadata`	`Metadata`	JSON encoded

`rate_cards.Alias`

type RateCardAlias = {
  alias: string;
  /** ISO-8601 formatted timestamp */
  starting_at: string | null;
  /** ISO-8601 formatted timestamp */
  ending_before: string | null;
};

`contract_named_schedules`

Column	Type	Description
`id`	`string`
`contract_id`	`string`
`schedule_name`	`string`
`starting_at`	`date`
`ending_before`	`date` or `NULL`
`value`	`string`	JSON encoded
`environment_type`	`string`	The Metronome environment, `SANDBOX` or `PRODUCTION`
`updated_at`	`date`

`rate_card_named_schedules`

Column	Type	Description
`id`	`string`
`rate_card_id`	`string`
`schedule_name`	`string`
`starting_at`	`date`
`ending_before`	`date` or `NULL`
`value`	`string`	JSON encoded
`environment_type`	`string`	The Metronome environment, `SANDBOX` or `PRODUCTION`
`updated_at`	`date`

`customer_named_schedules`

Column	Type	Description
`id`	`string`
`customer_id`	`string`
`schedule_name`	`string`
`starting_at`	`date`
`ending_before`	`date` or `NULL`
`value`	`string`	JSON encoded
`environment_type`	`string`	The Metronome environment, `SANDBOX` or `PRODUCTION`
`updated_at`	`date`

`product_list_item_versions`

The ProductListItem type is flexible and values can change over time, so this table represents all possible versions of each ProductListItem object as a separate row. To determine the correct version of a ProductListItem at a given point in time:

Filter the table by the product_list_item_id and starting_at <= {TIME}
Select the row with the largest version

Column	Type	Description
`id`	`string`	Each version has a distinct id, for joining with other tables use `product_list_item_id`
`product_list_item_id`	`string`
`type`	`string`	Either "composite", "fixed", "subscription", "usage" or "professional_service".
`name`	`string`
`is_refundable`	`boolean` or `NULL`
`starting_at`	`date`
`version`	`int`
`billable_metric_id`	`string` or `NULL`	Only found on "usage" products.
`composite_product_ids`	`string[]` or `NULL`	JSON encoded, only found on "composite" products.
`quantity_conversion`	`QuantityConversion` or `NULL`	JSON encoded, only found on "usage" products.
`tags`	`string[]`	JSON encoded
`composite_tags`	`string[]` or `NULL`	JSON encoded, only found on "composite" products.
`created_at`	`date`
`created_by`	`string`
`updated_at`	`date`
`metadata`	`Metadata`	JSON encoded

`product_list_item_versions.QuantityConversion`

interface QuantityConversion {
  conversion_factor: number;
  operation: "multiply" | "divide";
  name: string;
}

`rate_card_entries`

The RateCardEntry type is also flexible, just like for ProductListItem each row represents a distinct version of a RateCardEntry. To determine the correct version for any product on a given rate card at a specific point in time:

Filter rate_card_entries rows by rate_card_id, product_id, and starting_at <= {TIME} < ending_before
Select the row with the largest version

Column	Type	Description
`id`	`string`
`rate_card_id`	`string`
`product_id`	`string`
`starting_at`	`date`
`ending_before`	`date` or `NULL`
`entitled`	`boolean`
`version`	`int`
`rate`	`Rate`	JSON encoded
`product_order`	`int`
`updated_at`	`date`
`metadata`	`Metadata`	JSON encoded

Each RateCardEntry row has a rate value which is a JSON representation of a Rate object:

`rate_card_entries.Rate`

Flat rates are simple and just describe the price (in USD cents) at a given time.

Percentage rates are a bit more complex and apply to a "base price". The use_list_prices property determines how the "base price" is determined.

Subscription rates are similar to flat rates with the addition of a quantity.

Custom rates are used for bespoke pricing logic. The exact structure of such rates vary.

Tiered rates define prices for different usage levels, with each tier specifying a size range and the corresponding unit price for usage within that range.

By default the base price is determined by reapplying rate-calculation logic without this specific rate, then multiplying the result by the fraction. This is useful for things like discounts. If the use_list_prices property is set to true then the base price is determined by only considering the rates on the rate card, and ignoring any rate overrides on the contract.

type Rate =
  | FlatRate
  | PercentageRate
  | SubscriptionRate
  | CustomRate
  | TieredRate;

interface FlatRate {
  type: "flat";
  /** Float, USD cents */
  unit_price: number;
}

interface PercentageRate {
  type: "percentage";
  /** Float - multiplied by the base price to get the actual price */
  fraction: number;
  use_list_prices: boolean;
}

interface SubscriptionRate {
  type: "subscription";
  /** Float, USD cents */
  unit_price: number;
  /** Float - multiplied by unit_price to get the actual price */
  quantity: number;
}

interface CustomRate {
  type: "custom";
  value: unknown;
}

type Tier = {
  /** Float, USD cents */
  unit_price: number;
  /** Float - size of the tier */
  size: number;
};

interface TieredRate {
  type: "tiered";
  tiers: Tier[];
}

Contract tables

`contracts`

Column	Type	Description
`id`	`string`
`name`	`string`
`customer_id`	`string`
`rate_card_id`	`string` or `NULL`
`starting_at`	`date`
`ending_before`	`date` or `NULL`
`archived_at`	`date` or `NULL`
`multiplier_override_prioritization`	`string` or `NULL`
`net_payment_terms_days`	`int`
`usage_statement_schedule_frequency`	`string`
`created_at`	`date`
`created_by`	`string`
`updated_at`	`date`
`metadata`	`Metadata`	JSON encoded

`balances`

Column	Type	Description
`id`	`string`
`customer_id`	`string`
`contract_id`	`string` or `NULL`
`amendment_id`	`string` or `NULL`
`type`	`string`	Either "postpaid", "prepaid", or "credit"
`name`	`string` or `NULL`
`priority`	`float`
`description`	`string` or `NULL`
`product_id`	`string`
`access_schedule`	`AccessSchedule`	JSON encoded
`invoice_schedule`	`InvoiceSchedule` or `NULL`	JSON encoded, always set for "postpaid" commits, sometimes set for "prepaid" commits, never for "credit"
`rollover_fraction`	`float` or `NULL`
`applicable_product_ids`	`string[]`	JSON encoded
`applicable_product_tags`	`string[]`	JSON encoded
`applicable_contract_ids`	`string[]`	JSON encoded
`invoice_contract_id`	`string` or `NULL`
`ledger`	`CommitLedgerEntry[]`	JSON encoded
`rolled_over_from_commit_id`	`string` or `NULL`
`rolled_over_from_contract_id`	`string` or `NULL`
`updated_at`	`date`
`metadata`	`Metadata`	JSON encoded

`balances.AccessSchedule`

interface AccessSchedule {
  schedule_items: Array<{
    id: string;
    /** ISO-8601 formatted timestamp */
    date: string;
    /** ISO-8601 formatted timestamp */
    end_date: string | null;
    /** Float, USD Cents */
    amount: number;
  }>;
}

`balances.InvoiceSchedule`

interface InvoiceSchedule {
  schedule_items: Array<{
    id: string;
    /** ISO-8601 formatted timestamp */
    date: string;
    /** Float, USD Cents */
    amount: number;
    invoice_id: string;
  }>;
  recurring_schedule: {
    /** ISO-8601 formatted timestamp */
    start_date: string;
    /** ISO-8601 formatted timestamp */
    end_date: string;
    /** Float, USD Cents */
    amount: number;
    amount_distribution: "divided" | "divided_rounded" | "each";
    frequency: "annual" | "monthly" | "quarterly" | "semi_annual";
    /** Float */
    quantity?: number;
    /** Float, USD Cents */
    unit_price?: number;
  } | null;
}

`balances.CommitLedgerEntry`

type LedgerEntry =
  | {
      /**
       * Represents the starting balance of a postpaid commit.
       */
      type: "postpaid_initial_balance";
      /** ISO-8601 formatted timestamp */
      timestamp: string;
      /** Float, USD Cents */
      amount: number;
    }
  | {
      /**
       * Represents deductions from the remaining obligation of the
       * postpaid commit as the result of an invoice with usage that
       * applies to this commit.
       */
      type: "postpaid_automated_invoice_deduction";
      /** ISO-8601 formatted timestamp */
      timestamp: string;
      /** Float, USD Cents */
      amount: number;
      invoice_id: string;
    }
  | {
      /**
       * Represents a true-up invoice that was issued for this commit to
       * cover usage that was not covered by automated usage invoices.
       */
      type: "postpaid_trueup";
      /** ISO-8601 formatted timestamp */
      timestamp: string;
      /** Float, USD Cents */
      amount: number;
      invoice_id: string;
    }
  | {
      /**
       * Represents that a prepaid commit segment was started and the customer
       * now has access to the usage amount for that segment. These segments
       * are described in the access schedule of the prepaid commit.
       */
      type: "prepaid_segment_start";
      /** ISO-8601 formatted timestamp */
      timestamp: string;
      /** Float, USD Cents */
      amount: number;
      segment_id: string;
    }
  | {
      /**
       * Represents deductions from the prepaid commit segment caused by a
       * usage invoice that included usage applicable to this commit.
       */
      type: "prepaid_automated_invoice_deduction";
      /** ISO-8601 formatted timestamp */
      timestamp: string;
      /** Float, USD Cents */
      amount: number;
      segment_id: string;
      invoice_id: string;
    }
  | {
      /**
       * Represents unused usage from the prepaid commit which was rolled
       * over to a new contract.
       */
      type: "prepaid_rollover";
      /** ISO-8601 formatted timestamp */
      timestamp: string;
      /** Float, USD Cents */
      amount: number;
      segment_id: string;
      new_contract_id: string;
      new_commit_id: string;
    }
  | {
      type: "prepaid_commit_canceled";
      /** ISO-8601 formatted timestamp */
      timestamp: string;
      /** Float, USD Cents */
      amount: number;
      segment_id: string;
      invoice_id: string;
    }
  | {
      type: "prepaid_commit_credited";
      /** ISO-8601 formatted timestamp */
      timestamp: string;
      /** Float, USD Cents */
      amount: number;
      segment_id: string;
      invoice_id: string;
    }
  | {
      /**
       * Represents commit amount that was unused and expired at the end of
       * a commit segment. Does not include usage that rolled over to a new
       * contract.
       */
      type: "prepaid_segment_expiration";
      /** ISO-8601 formatted timestamp */
      timestamp: string;
      /** Float, USD Cents */
      amount: number;
      segment_id: string;
    }
  | {
      type: "prepaid_manual";
      /** ISO-8601 formatted timestamp */
      timestamp: string;
      /** Float, USD Cents */
      amount: number;
      segment_id: string;
      reason: string;
    }
  | {
      type: "postpaid_manual";
      /** ISO-8601 formatted timestamp */
      timestamp: string;
      /** Float, USD Cents */
      amount: number;
    }
  | {
      /**
       * Represents that a credit segment was started and the customer
       * now has access to the usage amount for that segment. These segments
       * are described in the access schedule of the credit.
       */
      type: "credit_segment_start";
      /** ISO-8601 formatted timestamp */
      timestamp: string;
      /** Float, USD Cents */
      amount: number;
      segment_id: string;
    }
  | {
      /**
       * Represents deductions from the credit segment caused by a
       * usage invoice that included usage applicable to this credit.
       */
      type: "credit_automated_invoice_deduction";
      /** ISO-8601 formatted timestamp */
      timestamp: string;
      /** Float, USD Cents */
      amount: number;
      segment_id: string;
      invoice_id: string;
    }
  | {
      type: "credit_commit_canceled";
      /** ISO-8601 formatted timestamp */
      timestamp: string;
      /** Float, USD Cents */
      amount: number;
      segment_id: string;
      invoice_id: string;
    }
  | {
      type: "credit_commit_credited";
      /** ISO-8601 formatted timestamp */
      timestamp: string;
      /** Float, USD Cents */
      amount: number;
      segment_id: string;
      invoice_id: string;
    }
  | {
      /**
       * Represents credit amount that was unused and expired at the end of
       * a credit segment.
       */
      type: "credit_segment_expiration";
      /** ISO-8601 formatted timestamp */
      timestamp: string;
      /** Float, USD Cents */
      amount: number;
      segment_id: string;
    }
  | {
      type: "credit_manual";
      /** ISO-8601 formatted timestamp */
      timestamp: string;
      /** Float, USD Cents */
      amount: number;
      segment_id: string;
      reason: string;
    };

`commits`

Column	Type	Description
`id`	`string`
`contract_id`	`string`
`amendment_id`	`string` or `NULL`
`type`	`string`	Either "postpaid" or "prepaid"
`name`	`string` or `NULL`
`priority`	`float`
`description`	`string` or `NULL`
`product_id`	`string`
`amount`	`float` or `NULL`	Deprecated (use amount from schedule items instead)
`access_schedule`	`AccessSchedule`	JSON encoded
`invoice_schedule`	`InvoiceSchedule` or `NULL`	JSON encoded, always set for "postpaid" commits, sometimes set for "prepaid" commits
`rollover_fraction`	`float` or `NULL`
`applicable_product_ids`	`string[]`	JSON encoded
`applicable_product_tags`	`string[]`	JSON encoded
`ledger`	`CommitLedgerEntry[]`	JSON encoded
`rolled_over_from_commit_id`	`string` or `NULL`
`rolled_over_from_contract_id`	`string` or `NULL`
`updated_at`	`date`
`metadata`	`Metadata`	JSON encoded

`commits.AccessSchedule`

interface AccessSchedule {
  schedule_items: Array<{
    id: string;
    /** ISO-8601 formatted timestamp */
    date: string;
    /** ISO-8601 formatted timestamp */
    end_date: string | null;
    /** Float, USD Cents */
    amount: number;
  }>;
}

`commits.InvoiceSchedule`

interface InvoiceSchedule {
  schedule_items: Array<{
    id: string;
    /** ISO-8601 formatted timestamp */
    date: string;
    /** Float, USD Cents */
    amount: number;
    invoice_id: string;
  }>;
  recurring_schedule: {
    /** ISO-8601 formatted timestamp */
    start_date: string;
    /** ISO-8601 formatted timestamp */
    end_date: string;
    /** Float, USD Cents */
    amount: number;
    amount_distribution: "divided" | "divided_rounded" | "each";
    frequency: "annual" | "monthly" | "quarterly" | "semi_annual";
    /** Float */
    quantity?: number;
    /** Float, USD Cents */
    unit_price?: number;
  } | null;
}

`commits.CommitLedgerEntry`

type LedgerEntry =
  | {
      /**
       * Represents the starting balance of a postpaid commit.
       */
      type: "postpaid_initial_balance";
      /** ISO-8601 formatted timestamp */
      timestamp: string;
      /** Float, USD Cents */
      amount: number;
    }
  | {
      /**
       * Represents deductions from the remaining obligation of the
       * postpaid commit as the result of an invoice with usage that
       * applies to this commit.
       */
      type: "postpaid_automated_invoice_deduction";
      /** ISO-8601 formatted timestamp */
      timestamp: string;
      /** Float, USD Cents */
      amount: number;
      invoice_id: string;
    }
  | {
      /**
       * Represents a true-up invoice that was issued for this commit to
       * cover usage that was not covered by automated usage invoices.
       */
      type: "postpaid_trueup";
      /** ISO-8601 formatted timestamp */
      timestamp: string;
      /** Float, USD Cents */
      amount: number;
      invoice_id: string;
    }
  | {
      /**
       * Represents that a prepaid commit segment was started and the customer
       * now has access to the usage amount for that segment. These segments
       * are described in the access schedule of the prepaid commit.
       */
      type: "prepaid_segment_start";
      /** ISO-8601 formatted timestamp */
      timestamp: string;
      /** Float, USD Cents */
      amount: number;
      segment_id: string;
    }
  | {
      /**
       * Represents deductions from the prepaid commit segment caused by a
       * usage invoice that included usage applicable to this commit.
       */
      type: "prepaid_automated_invoice_deduction";
      /** ISO-8601 formatted timestamp */
      timestamp: string;
      /** Float, USD Cents */
      amount: number;
      segment_id: string;
      invoice_id: string;
    }
  | {
      /**
       * Represents unused usage from the prepaid commit which was rolled
       * over to a new contract.
       */
      type: "prepaid_rollover";
      /** ISO-8601 formatted timestamp */
      timestamp: string;
      /** Float, USD Cents */
      amount: number;
      segment_id: string;
      new_contract_id: string;
      new_commit_id: string;
    }
  | {
      type: "prepaid_commit_canceled";
      /** ISO-8601 formatted timestamp */
      timestamp: string;
      /** Float, USD Cents */
      amount: number;
      segment_id: string;
      invoice_id: string;
    }
  | {
      type: "prepaid_commit_credited";
      /** ISO-8601 formatted timestamp */
      timestamp: string;
      /** Float, USD Cents */
      amount: number;
      segment_id: string;
      invoice_id: string;
    }
  | {
      /**
       * Represents commit amount that was unused and expired at the end of
       * a commit segment. Does not include usage that rolled over to a new
       * contract.
       */
      type: "prepaid_segment_expiration";
      /** ISO-8601 formatted timestamp */
      timestamp: string;
      /** Float, USD Cents */
      amount: number;
      segment_id: string;
    }
  | {
      type: "prepaid_manual";
      /** ISO-8601 formatted timestamp */
      timestamp: string;
      /** Float, USD Cents */
      amount: number;
      segment_id: string;
      reason: string;
    }
  | {
      type: "postpaid_manual";
      /** ISO-8601 formatted timestamp */
      timestamp: string;
      /** Float, USD Cents */
      amount: number;
    };

`amendments`

Column	Type	Description
`id`	`string`
`contract_id`	`string`
`effective_at`	`date`
`created_at`	`date`
`created_by`	`string`
`updated_at`	`date`
`metadata`	`Metadata`	JSON encoded

`overrides`

Column	Type	Description
`id`	`string`
`contract_id`	`string`
`amendment_id`	`string` or `NULL`
`product_id`	`string`
`starting_at`	`date`
`ending_before`	`date` or `NULL`
`entitled`	`boolean` or `NULL`
`rate_type`	`string` or `NULL`	Either "multiplier", "overwrite_flat", or "overwrite_percentage", `NULL` if this does not override the rate
`multiplier`	`float` or `NULL`
`priority`	`float` or `NULL`	Only defined for contracts with EXPLICIT multiplier override prioritization
`new_rate`	`Rate` or `NULL`	JSON encoded
`applicable_product_tags`	`string[]`	JSON encoded
`override_specifier`	`OverrideSpecifier[]`	JSON encoded
`updated_at`	`date`
`metadata`	`Metadata`	JSON encoded

`overrides.Rate`

type Rate =
  | {
      type: "flat";
      unit_price: number;
    }
  | {
      type: "percentage";
      fraction: number;
      use_list_prices: boolean;
    }
  | {
      type: "subscription";
      unit_price: number;
      quantity: number;
    };

`override.Specifier`

type OverrideSpecifier = {
  product_id?: string;
  product_tags?: string[];
  pricing_group_values?: Array<{
    name: string;
    value: string;
  }>;
};

`discounts`

Column	Type	Description
`id`	`string`
`contract_id`	`string`
`amendment_id`	`string` or `NULL`
`name`	`string` or `NULL`
`description`	`string` or `NULL`
`product_id`	`string`
`schedule`	`Schedule`	JSON encoded
`updated_at`	`date`
`metadata`	`Metadata`	JSON encoded

`discounts.Schedule`

interface Schedule {
  schedule_items: Array<{
    id: string;
    /** ISO-8601 formatted timestamp */
    date: string;
    /** Float, USD Cents */
    amount: number;
  }>;
  recurring_schedule: {
    /** ISO-8601 formatted timestamp */
    start_date: string;
    /** ISO-8601 formatted timestamp */
    end_date: string;
    /** Float, USD Cents */
    amount: number;
    amount_distribution: "divided" | "divided_rounded" | "each";
    frequency: "annual" | "monthly" | "quarterly" | "semi_annual";
    /** Float */
    quantity?: number;
    /** Float, USD Cents */
    unit_price?: number;
  } | null;
}

`scheduled_charges`

Column	Type	Description
`id`	`string`
`contract_id`	`string`
`amendment_id`	`string` or `NULL`
`name`	`string` or `NULL`
`description`	`string` or `NULL`
`product_id`	`string`
`schedule`	`Schedule`
`updated_at`	`date`
`metadata`	`Metadata`	JSON encoded

`scheduled_charges.Schedule`

interface Schedule {
  schedule_items: Array<{
    id: string;
    /** ISO-8601 formatted timestamp */
    date: string;
    /** Float, USD Cents */
    amount: number;
  }>;
  recurring_schedule: {
    /** ISO-8601 formatted timestamp */
    start_date: string;
    /** ISO-8601 formatted timestamp */
    end_date: string;
    /** Float, USD Cents */
    amount: number;
    amount_distribution: "divided" | "divided_rounded" | "each";
    frequency: "annual" | "monthly" | "quarterly" | "semi_annual";
    /** Float */
    quantity?: number;
    /** Float, USD Cents */
    unit_price?: number;
  } | null;
}

`transitions`

Column	Type	Description
`id`	`string`
`type`	`string`	Either "renewal" or "supersede"
`contract_id`	`string`
`from_contract_id`	`string`
`to_contract_id`	`string`
`date`	`date`
`updated_at`	`date`
`metadata`	`Metadata`	JSON encoded

`usage_filters`

Usage filters change over time, so each row represents a distinct version of a usage filter. To determine the correct version of a usage filter at a given point in time:

Filter the table by contract_id and starting_at <= {TIME}
Select the row with the largest version

Column	Type	Description
`id`	`string`
`contract_id`	`string`
`version`	`int`
`starting_at`	`date` or `NULL`
`group_key`	`string`
`group_values`	`string[]`	JSON encoded
`updated_at`	`date`
`metadata`	`Metadata`	JSON encoded

`pro_services`

This table's availability is dependent on your client's configuration.

Column	Type	Description
`id`	`string`
`contract_id`	`string`
`amendment_id`	`string`
`product_id`	`string`
`name`	`string`
`description`	`string` or `NULL`
`unit_price`	`float`
`updated_at`	`date`
`metadata`	`Metadata`	JSON encoded

USD cents​

Common columns​

id​

updated_at​

metadata​

snapshot_id​

Contract pricing tables​

rate_cards​

rate_cards.Alias​

contract_named_schedules​

rate_card_named_schedules​

customer_named_schedules​

product_list_item_versions​

product_list_item_versions.QuantityConversion​

rate_card_entries​

rate_card_entries.Rate​

Contract tables​

contracts​

balances​

balances.AccessSchedule​

balances.InvoiceSchedule​

balances.CommitLedgerEntry​

commits​

commits.AccessSchedule​

commits.InvoiceSchedule​

commits.CommitLedgerEntry​

amendments​

overrides​

overrides.Rate​

override.Specifier​

discounts​

discounts.Schedule​

scheduled_charges​

scheduled_charges.Schedule​

transitions​

usage_filters​

pro_services​