Data export

<bucket_name>/<folder_name>/<table_name>/dt=<transfer_date>/<file_part>_<transfer_timestamp>.parquet

SELECT id, name, alert_type, threshold FROM alert WHERE webhooks_enabled = TRUE AND disabled_at IS NULL;

SELECT DATE_TRUNC("DAY", ah.created_at) AS alert_date, alert.name, COUNT(alert.id) AS triggered_count
FROM customer_alert_history ah
JOIN alert
    ON ah.alert_id = alert.id
GROUP BY 1, 2
ORDER BY 1;

SELECT DATE_TRUNC("MONTH", end_timestamp) AS month, COUNT(0) AS invoice_count, SUM(total) AS invoice_total FROM invoice
GROUP BY 1
ORDER BY 1;

SELECT DATE_TRUNC("MONTH", i.end_timestamp) AS month,
li.line_item_type
, SUM(li.total) AS total
FROM invoice i
JOIN line_item li ON i.id = li.invoice_id
GROUP BY 1, 2
ORDER BY 2, 1;

SELECT *
FROM contracts_contracts
WHERE archived_at = TRUE;

SELECT * FROM contracts_overrides
WHERE contract_id = <contract_id>
ORDER BY updated_at DESC;

SELECT crc.id AS rate_card_id, COUNT(*)
FROM contracts_rate_cards crc
JOIN contracts_rate_card_entries crce ON crc.id = crce.rate_card_id
WHERE crce.ending_before > NOW()
GROUP BY 1
ORDER BY 1;

SELECT COUNT(0) FROM customer WHERE archived_at IS NULL;

SELECT snapshot_time, COUNT(0) AS invoice_count, SUM(total) AS invoice_total FROM draft_invoice
GROUP BY 1
ORDER BY 1;

SELECT contract_id, COUNT(0) AS invoice_count, SUM(total) AS invoice_total FROM draft_invoice
WHERE snapshot_time = (SELECT MAX(snapshot_time) FROM draft_invoice)
GROUP BY 1
ORDER BY 1;

WITH max_draft_breakdown_snapshot as (
    SELECT
         max(snapshot_timestamp) as max_snapshot_ts
    FROM draft_invoice
)

SELECT i.breakdown_start_timestamp, li.name, sum(li.quantity) as quantity, sum(li.total/100) as total_dollars
FROM draft_invoices i
JOIN draft_line_items li
ON i.id = li.invoice_breakdown_id
AND i.snapshot_timestamp = li.snapshot_timstamp
WHERE i.environment_type = 'PRODUCTION'
AND i.snapshot_timestamp = (SELECT max_snapshot_ts FROM max_draft_breakdown_snapshot)
AND li.total >= 0
GROUP BY 1,2
ORDER BY 1 DESC

WITH max_draft_breakdown_snapshot as (
    SELECT
         max(snapshot_timestamp) as max_snapshot_ts
    FROM draft_invoices
)

SELECT i.customer_id, i.breakdown_start_timestamp, i.invoice_id, li.name, li.quantity, li.total/100 as total_dollars
FROM draft_invoices i
JOIN draft_line_items li
ON i.id = li.invoice_breakdown_id
AND i.snapshot_timestamp = li.snapshot_timstamp
WHERE i.environment_type = 'PRODUCTION'
AND i.snapshot_timestamp = (SELECT max_snapshot_ts FROM max_draft_breakdown_snapshot)
GROUP BY 1,2
ORDER BY 1 DESC

SELECT DATE_TRUNC('MONTH', timestamp), COUNT(0) FROM events
GROUP BY 1
ORDER BY 1;

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

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

type LedgerEntry =
  | {
      /**
       * Represents the starting balance of a postpaid commit.
       */
      type: "postpaid_initial_balance";
      /** ISO-8601 formatted timestamp */
      timestamp: string;
      /** Float */
      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 */
      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 */
      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 */
      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 */
      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 */
      amount: number;
      segment_id: string;
      new_contract_id: string;
      new_commit_id: string;
    }
  | {
      type: "prepaid_commit_canceled";
      /** ISO-8601 formatted timestamp */
      timestamp: string;
      /** Float */
      amount: number;
      segment_id: string;
      invoice_id: string;
    }
  | {
      type: "prepaid_commit_credited";
      /** ISO-8601 formatted timestamp */
      timestamp: string;
      /** Float */
      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 */
      amount: number;
      segment_id: string;
    }
  | {
      type: "prepaid_manual";
      /** ISO-8601 formatted timestamp */
      timestamp: string;
      /** Float */
      amount: number;
      segment_id: string;
      reason: string;
    }
  | {
      type: "postpaid_manual";
      /** ISO-8601 formatted timestamp */
      timestamp: string;
      /** Float */
      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 */
      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 */
      amount: number;
      segment_id: string;
      invoice_id: string;
    }
  | {
      type: "credit_commit_canceled";
      /** ISO-8601 formatted timestamp */
      timestamp: string;
      /** Float */
      amount: number;
      segment_id: string;
      invoice_id: string;
    }
  | {
      type: "credit_commit_credited";
      /** ISO-8601 formatted timestamp */
      timestamp: string;
      /** Float */
      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 */
      amount: number;
      segment_id: string;
    }
  | {
      type: "credit_manual";
      /** ISO-8601 formatted timestamp */
      timestamp: string;
      /** Float */
      amount: number;
      segment_id: string;
      reason: string;
    };

type CommitSpecifier = {
  product_id?: string;
  product_tags?: string[];
  presentation_group_values?: {
    [key: string]: string;
  };
  pricing_group_values?: {
    [key: string]: string;
  };
}

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

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

type LedgerEntry =
  | {
      /**
       * Represents the starting balance of a postpaid commit.
       */
      type: "postpaid_initial_balance";
      /** ISO-8601 formatted timestamp */
      timestamp: string;
      /** Float */
      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 */
      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 */
      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 */
      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 */
      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 */
      amount: number;
      segment_id: string;
      new_contract_id: string;
      new_commit_id: string;
    }
  | {
      type: "prepaid_commit_canceled";
      /** ISO-8601 formatted timestamp */
      timestamp: string;
      /** Float */
      amount: number;
      segment_id: string;
      invoice_id: string;
    }
  | {
      type: "prepaid_commit_credited";
      /** ISO-8601 formatted timestamp */
      timestamp: string;
      /** Float */
      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 */
      amount: number;
      segment_id: string;
    }
  | {
      type: "prepaid_manual";
      /** ISO-8601 formatted timestamp */
      timestamp: string;
      /** Float */
      amount: number;
      segment_id: string;
      reason: string;
    }
  | {
      type: "postpaid_manual";
      /** ISO-8601 formatted timestamp */
      timestamp: string;
      /** Float */
      amount: number;
    };

type CommitSpecifier = {
  product_id?: string;
  product_tags?: string[];
  presentation_group_values?: {
    [key: string]: string;
  };
  pricing_group_values?: {
    [key: string]: string;
  };
}

interface AccessAmount {
  unit_price: number;
  quantity: number;
  credit_type_id: string;
  credit_type_name: string;
}

interface InvoiceAmount {
  unit_price: number;
  quantity: number;
  credit_type_id: string;
  credit_type_name: string;
}

enum CommitDurationUnit {
  PERIODS = "PERIODS",
}

interface CommitDuration {
  unit: CommitDurationUnit;
  value: number;
}

type CommitSpecifier = {
  product_id?: string;
  product_tags?: string[];
  presentation_group_values?: {
    [key: string]: string;
  };
  pricing_group_values?: {
    [key: string]: string;
  };
}

enum SubscriptionAllocationForRecurringCommitEnum {
  POOLED = "pooled",
  INDIVIDUAL = "individual",
}

type SubscriptionConfig = {
  subscription_id: string;
  apply_seat_increase_config: {
    is_prorated: boolean;
  };
  allocation: SubscriptionAllocationForRecurringCommitEnum;
};

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

type OverrideSpecifier = {
  commit_ids?: string[];
  product_id?: string;
  product_tags?: string[];
  pricing_group_values?: Array<{
    name: string;
    value: string;
  }>;
  presentation_group_values?: Array<{
    name: string;
    value: string;
  }>;
  // If set, the override will only apply to usage that burns down commits / credits that were created by the specified recurring config
  recurring_commit_ids?: string[];
  recurring_credit_ids?: string[];
};

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