Payments: Develop fct_aggregations model

[lauriemerrell]

As we focus more on the outcomes of (attempted) fare payment transactions, it seems appropriate to develop a model of aggregations, since those are the entity at which the dollar amount reconciliation occurs.

Proposed schema:

• aggregation_id
• distinct_auth_actions = count of distinct authorisation rows for this aggregation
• auth_type = most recent auth request_type
• auth_status = most recent status
• auth_datetime = most recent authorisation_date_time_utc value
• card_check_status = most recent card check outcome for this aggregation
• card_check_datetime = authorisation_date_time_utc value for most recent card check
• authorisation_status = most recent authorisation outcome
• authorisation_datetime = authorisation_date_time_utc value for most recent authorisation
• debt_recovery_status = most recent debt recovery authorisation outcome
• debt_recovery_datetime = authorisation_date_time_utc value for most recent debt recovery attempt
• settlement_requested_datetime = settlement_requested_date_time_utc
• A few columns about refunds? Maybe a boolean for has_refund and then some details about the refund if one occurred?

This must take account of cases where micropayment transaction_amount is 0 so no further authorisation or settlement is required.

To create such a table, it is likely that we will need to create some parent models, things like:

• Pivoted / aggregated authorisations
• A unified refunds model that combines the two types of refunds