fct_payment_instrument_status_history


Description

Full history of status changes for customers’ standard stored payment methods (credit/debit cards, direct debit, TWINT, PayPal, and similar instruments), including invalidation reasons and bank-reported chargeback details. Used to identify when and why a payment method became unusable so it can be followed up on. This table does not include bank-account-backed direct-debit rows from dim_payment_instrument: the separate mandate path has no equivalent status-event history, so those changes cannot be reconstructed here.

Columns

Column Type Size Nulls Auto Default Children Parents Comments
payment_instrument_status_history_id int8 19 null

The primary key of this table.
payment_instrument_id int8 19 √ null
dim_payment_instrument.payment_instrument_id fct_payment_instrument_status_history_payment_instrument_id_fkey R

The payment instrument whose status changed. Always a standard payment instrument id (positive) — bank-account direct-debit rows in dim_payment_instrument (negative ids) never appear here, see the table description above.
customer_id int8 19 √ null
dim_customer.customer_id fct_payment_instrument_status_history_customer_id_fkey R

The customer whose account this payment instrument belongs to. Null in rare cases where the instrument’s billing setup could not be linked back to an account.
paying_customer_id int8 19 √ null
dim_customer.customer_id fct_payment_instrument_status_history_paying_customer_id_fkey R

The customer who actually pays for this instrument. Equals customer_id for payment_responsibility_type SELF/UNKNOWN; differs from it for CUSTOMER (e.g. a parent paying for a child’s membership); null for EXTERNAL (a non-member payer).
payment_responsibility_type varchar 64 √ null

Who is financially responsible for this instrument. One of:

  • SELF — The account holder pays for themselves.
  • CUSTOMER — Another member pays on the account holder’s behalf (see paying_customer_id).
  • UNKNOWN — Migrated/imported account; the specific arrangement is not known.
  • EXTERNAL — A non-member (e.g. a parent or guardian) pays on the account holder’s behalf.
organization_unit_id int8 19 √ null
dim_organization_unit.organization_unit_id fct_payment_instrument_status_history_organization_unit_id_fkey R

The studio (organization unit) of the customer who owns the payment instrument whose status changed.
status varchar 64 √ null

The new status value at this log entry. One of:

  • WAITING_FOR_CONFIRMATION — Created and awaiting confirmation from the customer/PSP.
  • IMPORTED_WAITING_FOR_PROCESSING — Imported/migrated and awaiting PSP registration/tokenization following the import.
  • CONFIRMED — Confirmed and usable for charges.
  • INVALID — Can no longer be used (bank-rejected, disabled, charged back).
invalid_reason varchar 64 √ null

Populated when status is INVALID; explains why the instrument was invalidated. One of:

  • RECURRING_TOKEN_DISABLED — The PSP reported that the recurring token backing this instrument was disabled.
  • CHARGEBACK — The instrument was invalidated as a result of a chargeback. See charged_back_reason for detail.
charged_back_reason varchar 64 √ null

Populated when invalid_reason is CHARGEBACK; the specific chargeback reason reported by the bank/PSP. One of:

  • INSUFFICIENT_COVERING — Insufficient funds to cover the charge.
  • OPPOSITION — Customer actively disputed the charge with their bank.
  • INVALID_BANK_INFORMATION — Bank account details were invalid or incorrect (bank-rejected mandate/account).
  • INVALID_MANDATE — Direct debit mandate was invalid, expired, or revoked (bank-rejected mandate/account).
  • AUTHORIZATION_DECLINED — Authorization was declined by the issuing bank.
  • INVALID_CARD — Credit card number was invalid.
  • CARD_EXPIRED — Credit card had expired.
  • REVOKED_BY_CUSTOMER — Customer revoked the authorization.
  • OTHER — Catch-all for other return reasons.
message varchar 65535 √ null

Free-text message with additional detail about this status transition (e.g. provider error message).
event_date_time timestamptz 35 √ null

The UTC date and time at which this status transition occurred.
last_updated timestamptz 35 √ null

System column. UTC Timestamp at which entry was calculated (lags shortly behind source system).
data_landing_time timestamptz 35 getdate()

System column. UTC timestamp assigned when a table batch is applied on Redshift. It represents warehouse landing order at table-batch granularity, not source event order. Usable to filter for incremental extractions.

Relationships

Close relationships within degrees of separation