Skip to main content

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

ColumnTypeDescription
idstring
namestring
descriptionstring
aliasesRateCardAlias[]JSON encoded
created_atdate
created_bystring
updated_atdate
metadataMetadataJSON encoded

rate_cards.Alias

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

customer_named_schedules

ColumnTypeDescription
idstring
customer_idstring
schedule_namestring
starting_atdate
ending_beforedate or NULL
valuestringJSON encoded
updated_atdate

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
ColumnTypeDescription
idstringEach version has a distinct id, for joining with other tables use product_list_item_id
product_list_item_idstring
typestringEither "composite", "fixed", "subscription", "usage" or "professional_service".
namestring
is_refundableboolean or NULL
starting_atdate
versionint
billable_metric_idstring or NULLOnly found on "usage" products.
composite_product_idsstring[] or NULLJSON encoded, only found on "composite" products.
quantity_conversionQuantityConversion or NULLJSON encoded, only found on "usage" products.
tagsstring[]JSON encoded
composite_tagsstring[] or NULLJSON encoded, only found on "composite" products.
created_atdate
created_bystring
updated_atdate
metadataMetadataJSON 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
ColumnTypeDescription
idstring
rate_card_idstring
product_idstring
starting_atdate
ending_beforedate or NULL
entitledboolean
versionint
rateRateJSON encoded
product_orderint
updated_atdate
metadataMetadataJSON 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

ColumnTypeDescription
idstring
namestring
customer_idstring
rate_card_idstring or NULL
starting_atdate
ending_beforedate or NULL
archived_atdate or NULL
multiplier_override_prioritizationstring or NULL
net_payment_terms_daysint
usage_statement_schedule_frequencystring
created_atdate
created_bystring
updated_atdate
metadataMetadataJSON encoded

balances

ColumnTypeDescription
idstring
customer_idstring
contract_idstring or NULL
amendment_idstring or NULL
typestringEither "postpaid", "prepaid", or "credit"
namestring or NULL
priorityfloat
descriptionstring or NULL
product_idstring
access_scheduleAccessScheduleJSON encoded
invoice_scheduleInvoiceSchedule or NULLJSON encoded, always set for "postpaid" commits, sometimes set for "prepaid" commits, never for "credit"
rollover_fractionfloat or NULL
applicable_product_idsstring[]JSON encoded
applicable_product_tagsstring[]JSON encoded
applicable_contract_idsstring[]JSON encoded
invoice_contract_idstring or NULL
ledgerCommitLedgerEntry[]JSON encoded
rolled_over_from_commit_idstring or NULL
rolled_over_from_contract_idstring or NULL
updated_atdate
metadataMetadataJSON 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;
}>;
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

ColumnTypeDescription
idstring
contract_idstring
amendment_idstring or NULL
typestringEither "postpaid" or "prepaid"
namestring or NULL
priorityfloat
descriptionstring or NULL
product_idstring
amountfloat or NULLDeprecated (use amount from schedule items instead)
access_scheduleAccessScheduleJSON encoded
invoice_scheduleInvoiceSchedule or NULLJSON encoded, always set for "postpaid" commits, sometimes set for "prepaid" commits
rollover_fractionfloat or NULL
applicable_product_idsstring[]JSON encoded
applicable_product_tagsstring[]JSON encoded
ledgerCommitLedgerEntry[]JSON encoded
rolled_over_from_commit_idstring or NULL
rolled_over_from_contract_idstring or NULL
updated_atdate
metadataMetadataJSON 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;
}>;
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

ColumnTypeDescription
idstring
contract_idstring
effective_atdate
created_atdate
created_bystring
updated_atdate
metadataMetadataJSON encoded

overrides

ColumnTypeDescription
idstring
contract_idstring
amendment_idstring or NULL
product_idstring
starting_atdate
ending_beforedate or NULL
entitledboolean or NULL
rate_typestring or NULLEither "multiplier", "overwrite_flat", or "overwrite_percentage", NULL if this does not override the rate
multiplierfloat or NULL
priorityfloat or NULLOnly defined for contracts with EXPLICIT multiplier override prioritization
new_rateRate or NULLJSON encoded
applicable_product_tagsstring[]JSON encoded
updated_atdate
metadataMetadataJSON 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;
};

discounts

ColumnTypeDescription
idstring
contract_idstring
amendment_idstring or NULL
namestring or NULL
descriptionstring or NULL
product_idstring
scheduleScheduleJSON encoded
updated_atdate
metadataMetadataJSON 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

ColumnTypeDescription
idstring
contract_idstring
amendment_idstring or NULL
namestring or NULL
descriptionstring or NULL
product_idstring
scheduleSchedule
updated_atdate
metadataMetadataJSON 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

ColumnTypeDescription
idstring
typestringEither "renewal" or "supersede"
contract_idstring
from_contract_idstring
to_contract_idstring
datedate
updated_atdate
metadataMetadataJSON 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
ColumnTypeDescription
idstring
contract_idstring
versionint
starting_atdate or NULL
group_keystring
group_valuesstring[]JSON encoded
updated_atdate
metadataMetadataJSON encoded

pro_services

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

ColumnTypeDescription
idstring
contract_idstring
amendment_idstring
product_idstring
namestring
descriptionstring or NULL
unit_pricefloat
updated_atdate
metadataMetadataJSON encoded
Metronome logoMetronome logo