Recurring Giving

Grain: RECURRING_GIFTS — one row per standing recurring commitment. RECURRING_GIFT_PAYMENTS — one row per scheduled payment instance (null GIFT_KEY means payment was expected but not yet received).

A RECURRING_GIFT is the commitment — the amount, frequency, and current status. Each time a payment is processed, a RECURRING_GIFT_PAYMENT row is created that links the commitment to the actual GIFT transaction. Project designations are stored on the commitment itself via RECURRING_GIFT_DESIGNATIONS.

Key rule: To find missed payments, look for RECURRING_GIFT_PAYMENT rows where GIFT_KEY is null — a payment was expected but no gift was recorded.

Solid lines — required join (FK always populated). Dotted lines — optional join (FK is nullable; always use LEFT JOIN).

Table columns

Tables on this page: RECURRING_GIFTS · RECURRING_GIFT_PAYMENTS · RECURRING_GIFT_DESIGNATIONS · CONTACTS (boundary) · GIFTS (boundary) · PROJECTS (boundary)

FREQUENCY and STATUS are numeric codes. Decode them using TYPE_REFERENCES with ENTITY = 'RecurringGift'. See the decode pattern.

RECURRING_GIFTS

One row per standing recurring commitment — the amount, frequency, and current status. Individual payment instances are tracked in RECURRING_GIFT_PAYMENTS.

Column	Type	Notes
`RECURRING_GIFT_KEY`	TEXT	Primary key
`CONTACT_KEY`	TEXT	FK → CONTACTS
`SEGMENT_KEY`	TEXT	FK → SEGMENTS (optional) — campaign attribution
`RECURRING_GIFT_DATE`	TIMESTAMP	Date the recurring gift was established
`NEXT_EXPECTED_PAYMENT_DATE`	TIMESTAMP	Date of the next scheduled payment — key field for lapse detection
`LAST_PAYMENT_DATE`	TIMESTAMP	Date of the most recent received payment
`CANCEL_DATE_TIME_UTC`	TIMESTAMP	Date the recurring gift was cancelled
`AMOUNT`	NUMBER	Per-payment commitment amount
`BALANCE`	NUMBER	Running balance (if applicable)
`FREQUENCY`	NUMBER	Payment schedule — decode with TYPE_REFERENCES (Entity: `RecurringGift`, Property: `Frequency`)
`STATUS`	NUMBER	Current status — decode with TYPE_REFERENCES (Entity: `RecurringGift`, Property: `Status`)
`AUTOMATED_PAYMENTS`	BOOLEAN	Payments are processed automatically
`CANCELLATION_REASON`	TEXT	Reason for cancellation
`RECURRING_GIFT_IS_DELETED`	BOOLEAN	Soft delete flag — always filter `WHERE RECURRING_GIFT_IS_DELETED = FALSE`

RECURRING_GIFT_PAYMENTS

One row per scheduled payment instance against a recurring gift. A null GIFT_KEY means a payment was expected but not received — the primary signal for lapsed giving.

Column	Type	Notes
`RECURRING_GIFT_PAYMENT_KEY`	TEXT	Primary key
`RECURRING_GIFT_KEY`	TEXT	FK → RECURRING_GIFTS
`GIFT_KEY`	TEXT	FK → GIFTS (nullable) — null = payment expected but not received
`EXPECTED_PAYMENT_DATE`	TIMESTAMP	Date the payment was expected
`FULFILL_PAYMENT_DATE`	TIMESTAMP	Date the payment was actually made
`DISMISS_PAYMENT_DATE`	TIMESTAMP	Date the payment was dismissed (skipped intentionally)
`EXPECTED_AMOUNT`	NUMBER	Amount expected based on the commitment
`ACTUAL_AMOUNT`	NUMBER	Amount actually received
`RECURRING_GIFT_PAYMENT_IS_DELETED`	BOOLEAN	Soft delete flag

RECURRING_GIFT_DESIGNATIONS

One row per project split on a recurring gift commitment. Mirrors GIFT_DESIGNATIONS but lives on the commitment, not individual transactions.

Column	Type	Notes
`RECURRING_GIFT_DESIGNATION_KEY`	TEXT	Primary key
`RECURRING_GIFT_KEY`	TEXT	FK → RECURRING_GIFTS
`PROJECT_KEY`	TEXT	FK → PROJECTS
`AMOUNT_DESIGNATED`	NUMBER	Portion of the per-payment amount allocated to this project

CONTACTS

CONTACTS appears in this diagram as the giving unit that holds the recurring commitment. The full contacts structure is documented in Contacts — extended reference.

Column	Type	Notes
`CONTACT_KEY`	TEXT	Primary key
`NAME`	TEXT	Contact display name
`CONTACT_IS_DELETED`	BOOLEAN	Soft delete flag

GIFTS

GIFTS appears in this diagram as the transaction created when a recurring payment processes. The full gift structure is documented in Gifts — extended reference.

Column	Type	Notes
`GIFT_KEY`	TEXT	Primary key
`CONTACT_KEY`	TEXT	FK → CONTACTS
`GIFT_DATE_UTC`	TIMESTAMP	Date the gift was made
`AMOUNT`	NUMBER	Total gift amount
`GIFT_IS_DELETED`	BOOLEAN	Soft delete flag

PROJECTS

PROJECTS appears in this diagram as the destination for recurring gift designations. The full project structure is documented in Gifts — extended reference.

Column	Type	Notes
`PROJECT_KEY`	TEXT	Primary key
`NAME`	TEXT	Project name
`PROJECT_IS_ACTIVE`	BOOLEAN	Whether the project is currently active
`PROJECT_IS_DELETED`	BOOLEAN	Soft delete flag

FREQUENCY and STATUS are numeric codes. Decode them using TYPE_REFERENCES with ENTITY = 'RecurringGift'. See the decode pattern.

Data freshness: These tables sync continuously from Virtuous CRM. Typical lag is under 4 hours. To check when a record was last updated, inspect the SF__ROW_SYNCED_DATE_TIME_UTC column on any table.

Overview

Reference

Table columns

RECURRING_GIFTS

RECURRING_GIFT_PAYMENTS

RECURRING_GIFT_DESIGNATIONS

CONTACTS

GIFTS

PROJECTS

​Table columns

​RECURRING_GIFTS

​RECURRING_GIFT_PAYMENTS

​RECURRING_GIFT_DESIGNATIONS

​CONTACTS

​GIFTS

​PROJECTS

Table columns

RECURRING_GIFTS

RECURRING_GIFT_PAYMENTS

RECURRING_GIFT_DESIGNATIONS

CONTACTS

GIFTS

PROJECTS