| Column | Type | Size | Nulls | Auto | Default | Children | Parents | Comments | ||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| id | INT UNSIGNED | 10 | √ | null |
|
|
Contribution Recur ID |
|||||
| contact_id | INT UNSIGNED | 10 | null |
|
|
Foreign key to civicrm_contact.id . |
||||||
| amount | DECIMAL | 20,2 | null |
|
|
Amount to be contributed or charged each recurrence. |
||||||
| currency | VARCHAR | 3 | √ | null |
|
|
3 character string, value from config setting or input via user. |
|||||
| frequency_unit | VARCHAR | 8 | √ | month |
|
|
Time units for recurrence of payment. |
|||||
| frequency_interval | INT UNSIGNED | 10 | null |
|
|
Number of time units for recurrence of payment. |
||||||
| installments | INT UNSIGNED | 10 | √ | null |
|
|
Total number of payments to be made. Set this to 0 if this is an open-ended commitment i.e. no set end date. |
|||||
| start_date | DATETIME | 19 | null |
|
|
The date the first scheduled recurring contribution occurs. |
||||||
| create_date | DATETIME | 19 | null |
|
|
When this recurring contribution record was created. |
||||||
| modified_date | DATETIME | 19 | √ | null |
|
|
Last updated date for this record. mostly the last time a payment was received |
|||||
| cancel_date | DATETIME | 19 | √ | null |
|
|
Date this recurring contribution was cancelled by contributor- if we can get access to it |
|||||
| end_date | DATETIME | 19 | √ | null |
|
|
Date this recurring contribution finished successfully |
|||||
| processor_id | VARCHAR | 255 | √ | null |
|
|
Possibly needed to store a unique identifier for this recurring payment order - if this is available from the processor?? |
|||||
| trxn_id | VARCHAR | 255 | √ | null |
|
|
unique transaction id. may be processor id, bank id + trans id, or account number + check number… depending on payment_method |
|||||
| invoice_id | VARCHAR | 255 | √ | null |
|
|
unique invoice id, system generated or passed in |
|||||
| contribution_status_id | INT UNSIGNED | 10 | √ | 1 |
|
|
||||||
| is_test | TINYINT | 3 | √ | 0 |
|
|
||||||
| cycle_day | INT UNSIGNED | 10 | 1 |
|
|
Day in the period when the payment should be charged e.g. 1st of month, 15th etc. |
||||||
| next_sched_contribution_date | DATETIME | 19 | √ | null |
|
|
Next scheduled date |
|||||
| failure_count | INT UNSIGNED | 10 | √ | 0 |
|
|
Number of failed charge attempts since last success. Business rule could be set to deactivate on more than x failures. |
|||||
| failure_retry_date | DATETIME | 19 | √ | null |
|
|
Date to retry failed attempt |
|||||
| auto_renew | TINYINT | 3 | 0 |
|
|
Some systems allow contributor to set a number of installments - but then auto-renew the subscription or commitment if they do not cancel. |
||||||
| payment_processor_id | INT UNSIGNED | 10 | √ | null |
|
|
Foreign key to civicrm_payment_processor.id |
|||||
| financial_type_id | INT UNSIGNED | 10 | √ | null |
|
|
FK to Financial Type |
|||||
| payment_instrument_id | INT UNSIGNED | 10 | √ | null |
|
|
FK to Payment Instrument |
|||||
| campaign_id | INT UNSIGNED | 10 | √ | null |
|
|
The campaign for which this contribution has been triggered. |
|||||
| is_email_receipt | TINYINT | 3 | √ | 1 |
|
|
if true, receipt is automatically emailed to contact on each successful payment |
|||||
| payment_token_id | INT UNSIGNED | 10 | √ | null |
|
|
Optionally used to store a link to a payment token used for this recurring contribution. |
|||||
| cancel_reason | TEXT | 65535 | √ | null |
|
|
Free text field for a reason for cancelling |
| Constraint Name | Type | Sort | Column(s) |
|---|---|---|---|
| PRIMARY | Primary key | Asc | id |
| FK_civicrm_contribution_recur_campaign_id | Performance | Asc | campaign_id |
| FK_civicrm_contribution_recur_contact_id | Performance | Asc | contact_id |
| FK_civicrm_contribution_recur_financial_type_id | Performance | Asc | financial_type_id |
| FK_civicrm_contribution_recur_payment_processor_id | Performance | Asc | payment_processor_id |
| FK_civicrm_contribution_recur_payment_token_id | Performance | Asc | payment_token_id |
| index_contribution_status | Performance | Asc | contribution_status_id |
| UI_contrib_invoice_id | Must be unique | Asc | invoice_id |
| UI_contrib_trxn_id | Must be unique | Asc | trxn_id |
| UI_contribution_recur_payment_instrument_id | Performance | Asc | payment_instrument_id |
