fct_dunning_step


Description

Fact table for dunning steps recorded by the Magicline dunning engine. Each row represents one dunning action for a customer, whether triggered automatically by a scheduled dunning run or manually by studio staff.

A customer may have multiple rows over time. No deduplication is applied — each source row maps to exactly one row here.

Collection stop filtering: To identify customers with an active dunning-triggered collection stop as of a reference date:

collection_stop IS TRUE
AND dunning_date <= '<ref_date>'
AND (withdrawn_date IS NULL OR withdrawn_date::date > '<ref_date>')

For the current (live) state only:

collection_stop IS TRUE AND withdrawn_date IS NULL

Important: This table covers only dunning-engine-triggered collection stops. Manual stops configured by studio operators are stored in dim_customer_dunning_configuration (collection_stop_limit_type). Combine both sources for a complete collection stop picture.

Columns

Column Type Size Nulls Auto Default Children Parents Comments
dunning_step_id int8 19 null
fct_dunning_step.previous_dunning_step_id fct_dunning_step_previous_dunning_step_id_fkey R

Primary key of this table. Uniquely identifies a single dunning step.
customer_id int8 19 √ null
dim_customer.customer_id fct_dunning_step_customer_id_fkey R

The customer this dunning step applies to.
dunning_level_id int8 19 √ null
dim_dunning_level.dunning_level_id fct_dunning_step_dunning_level_id_fkey R

The dunning level reached at this step. NULL for legacy records or steps without an explicit level. Join to dim_dunning_level for the level’s position and type.
previous_dunning_step_id int8 19 √ null
fct_dunning_step.dunning_step_id fct_dunning_step_previous_dunning_step_id_fkey R

FK to the preceding dunning step for this customer (self-reference). Forms a chain of dunning steps over time. NULL for the first step in a customer’s dunning history.
dunning_date date 13 √ null

The date the dunning step was executed.
collection_stop bool 1 √ null

Whether payment collection is halted for this customer as a result of this dunning step. Source NULL values are coalesced to false.

A stop is active as of a reference date when: collection_stop IS TRUE AND dunning_date <= ref_date AND (withdrawn_date IS NULL OR withdrawn_date::date > ref_date)
service_stop bool 1 √ null

Whether the customer’s access to the studio was locked as a result of this dunning step.
withdrawn_date timestamptz 35 √ null

The timestamp at which this dunning step was withdrawn (resolved, reversed, or superseded). NULL means the step is still active.
amount numeric 23,6 √ null

The outstanding amount at the time this dunning step was recorded.
origin varchar 50 √ null

The source that triggered this dunning step.

Values

  • MANUAL_DUNNING — triggered manually by a studio user
  • DUNNING_RUN — triggered by a scheduled automated dunning run
  • CHARGE_BACK — triggered by a payment chargeback
  • DEBT_COLLECTION — triggered by the debt collection process
  • IMPORT — imported from an external source
transferred_to_debt_collection_service_provider bool 1 √ null

Whether this dunning step resulted in the debt being transferred to an external debt collection service provider.
extraordinary_contract_cancelation bool 1 √ null

Whether this dunning step triggered an extraordinary (out-of-term) contract cancellation.
contract_rest_maturity bool 1 √ null

Whether this dunning step triggered early collection of the remaining contract value (Restfälligkeit). The full outstanding contract amount becomes immediately due.
contract_cancellation_damage_compensation bool 1 √ null

Whether damage compensation was charged as part of a contract cancellation triggered by this dunning step.
communication_sent_immediately bool 1 √ null

Whether the dunning communication (letter, email, or SMS) was dispatched immediately rather than queued for batched sending. NULL for legacy rows predating this flag.

A non-NULL value indicates that a dunning communication was configured for this step. true = sent immediately; false = queued for batch dispatch.
undo_contract_cancelation_when_debt_claims_paid bool 1 √ null

Whether the extraordinary contract cancellation triggered by this dunning step should be automatically reversed once all outstanding debt claims are settled.
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()

Relationships

Close relationships within degrees of separation