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).
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 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.
