Uploaded image for project: 'CiviCRM'
  1. CiviCRM
  2. CRM-728

CiviContribute - Schema for Recurring Contributions


    • Type: New Feature
    • Status: Done/Fixed
    • Priority: Major
    • Resolution: Fixed/Completed
    • Affects Version/s: None
    • Fix Version/s: Unscheduled
    • Component/s: None
    • Labels:


      There are several unresolved issues related to storage of recurring contribution data w/in CiviCRM:

      • Unless we decide to persist credit card data within CiviCRM, we will need to rely on processor/server-side solutions.
      • Based on how Moneris and PayPal offerings work, a recurring payment would be TRIGGERED by our software - and we could specify frequency, amount and number of payments. However, it looks like all subsequent charges happen completely w/in the processor scope.
      • There MAY or MAY NOT be a way to have these processors "inform" us of subsequent events (and this will be processor-dependent), e.g.:
        successful or failed charges
        cancellation of the 'subscription'
        change in amount or frequency
      • At some point, we may also be able to hook into their APIs so that changes/cancellations go through our interface and we can update the recurring contribution info.
      • Given that we are not actually 'driving' the contribution actions - the recurring contribution data we store is a snapshot of contributor 'commitments' - which we can hopefully maintain accurately.

      Recurring Contributions Schema
      civicrm_contribution_recur (new table)
      Stores parameters for a recurring contribution. The actual contributions are stored in civicrm_contribution and are keyed to this table by
      a new foreign key in that table - recur_contribution_id.

      id INT NOT NULL ,
      contact_id INT NOT NULL COMMENT 'Foreign key to civicrm_contact.id',
      amount MONEY NOT NULL COMMENT 'Amount to be contributed/charged each recurrence.',
      frequency_unit ENUM; 'day, week, month, year' NOT NULL DEFAULT 'month' COMMENT 'Time unit for recurrence of payment.',
      frequency_interval INT NOT NULL COMMENT 'Number of time units for recurrence of payment.',
      installments INT NOT NULL COMMENT '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 DATE NOT NULL COMMENT 'The date the first scheduled recurring contribution occurs.',
      create_date DATE NOT NULL COMMENT 'When this recurring contribution record was created.'
      modified_date DATE NULL COMMENT 'Last updated date for this record.'
      cancel_date DATE NULL COMMENT 'Date this recurring contribution was cancelled by contributor- if we can get access to it ??',
      is_active TINYINT NOT NULL DEFAULT 1 COMMENT 'Set to false by contributor cancellation or greater than max permitted failures - if we know about that.'

      // some other properties we might consider including ?? the first four were part of the Groundspring schema...
      cycle_day INT DEFAULT 1 COMMENT 'Day in the period when the payment should be charged (e.g. 1st of month, 15th etc.) .',
      next_sched_contribution DATE COMMENT 'At Groundspring this was used by the cron job which triggered payments. If we're not doing that but we know about payments, it might still be useful to store for display to org and/or contributors.',
      failure_count INT DEFAULT 0 COMMENT 'Number of failed charge attempts since last success. Business rule could be set to deactivate on more than x failures.',
      failure_retry_date DATE NULL COMMENT 'At Groundspring we set a business rule to retry failed payments every 7 days - and stored the next scheduled attempt date there.',
      processor_id varchar(255) NULL COMMENT 'Possibly needed to store a unique identifier for this recurring payment order - if this is available from the processor(s)??',
      auto_renew TINYINT NOT NULL DEFAULT 0 COMMENT 'Some systems allow contributor to set a number of installments - but then auto-renew the subscription/commitment if they do not cancel.',

      civicrm_contribution (alter table)
      recur_contribution_id INT NULL COMMENT 'Conditional foreign key to civicrm_contribution_recur.id. Each contribution made in connection with a recurring contribution carries a foreign key to the recurring contribution record. This assumes we can track these processor initiated events.'

      Reference Info

      Moneris Recurring Payments Info (from Alan Dixon):
      Yes, Moneris does offer recurring payments, and it's quite
      straightforward - it's the same as a regular direct payment, with a
      bunch of extra parameters for the recurring payments, which are passed
      in an extra call before the main payment call. To save you from RTFM:

      "In the example to the left the first transaction will occur in the
      future on Jan 2nd 2005. It will be billed $30.00 every 2 months on the
      2nd of each month. The card will be billed a total of 12 times.

      $recurArray = array(recur_unit=>'month',
      period => '2',
      recur_amount=> '30.00'

      $mpgRecur = new mpgRecur($recurArray);




            • Assignee:
              lobo Donald A. Lobo
              dgg David Greenberg
            • Votes:
              0 Vote for this issue
              0 Start watching this issue


              • Created: