Grain: PLEDGES — one row per commitment to give. PLEDGE_PAYMENTS — one row per installment against a pledge (null GIFT_KEY means the payment is expected but not yet received).
GIFT_ASK is a specific solicitation made to a contact for a project. When a contact commits to giving, a PLEDGE is created — optionally linked back to the ask. PLEDGE_PAYMENTS track each scheduled installment, and when payment arrives, it is recorded as a GIFT.
Key rule: A pledge can exist without a gift ask (contact committed without a formal ask). Always use a LEFT JOIN when connecting PLEDGES to GIFT_ASKS.
Solid lines — required join (FK always populated). Dotted lines — optional join (FK is nullable; always use LEFT JOIN).
Table columns
Tables on this page: PLEDGES · GIFT_ASKS · PLEDGE_PAYMENTS · CONTACTS (boundary) · GIFTS (boundary) · PROJECTS (boundary)
PLEDGES.STATUS is a numeric code. Decode it using TYPE_REFERENCES with ENTITY = 'Pledge' and PROPERTY = 'Status'. See the decode pattern.
PLEDGES
One row per pledge commitment. Tracks the total amount pledged, how much remains (BALANCE), and current status. Fulfillment is tracked via PLEDGE_PAYMENTS.
| Column | Type | Notes |
|---|
PLEDGE_KEY | TEXT | Primary key |
CONTACT_KEY | TEXT | FK → CONTACTS |
GIFT_ASK_KEY | TEXT | FK → GIFT_ASKS (optional) — the ask that generated this pledge; always LEFT JOIN |
PROJECT_KEY | TEXT | FK → PROJECTS |
PLEDGE_DATE | TIMESTAMP | Date the pledge was made |
EXPECTED_FULFILLMENT_DATE | TIMESTAMP | Expected date pledge will be fully paid |
CANCEL_DATE_TIME_UTC | TIMESTAMP | Date the pledge was cancelled |
WRITE_OFF_DATE_TIME_UTC | TIMESTAMP | Date the pledge was written off |
AMOUNT_PLEDGED | NUMBER | Total commitment amount |
BALANCE | NUMBER | Remaining unfulfilled amount |
FREQUENCY | NUMBER | Payment schedule — decode with TYPE_REFERENCES (Entity: Pledge, Property: Frequency) |
STATUS | NUMBER | Current status — decode with TYPE_REFERENCES (Entity: Pledge, Property: Status) |
WRITE_OFF_AMOUNT | NUMBER | Amount written off |
WRITE_OFF_REASON | TEXT | Reason for write-off |
PLEDGE_IS_DELETED | BOOLEAN | Soft delete flag — always filter WHERE PLEDGE_IS_DELETED = FALSE |
GIFT_ASKS
One row per formal solicitation made to a contact. Linked optionally to GIFTS (when fulfilled) and PLEDGES (when it results in a commitment).
| Column | Type | Notes |
|---|
GIFT_ASK_KEY | TEXT | Primary key |
CONTACT_KEY | TEXT | FK → CONTACTS — the contact being solicited |
PROJECT_KEY | TEXT | FK → PROJECTS — project the ask is for |
SEGMENT_KEY | TEXT | FK → SEGMENTS (optional) — campaign segment for this ask |
ASK_DATE | TIMESTAMP | Date the ask was made |
EXPECTED_FULFILLMENT_DATE | TIMESTAMP | Expected date the ask will be fulfilled |
ASK_AMOUNT | NUMBER | Amount requested |
ASK_TYPE | TEXT | Type of ask (e.g. Major Gift, Annual Fund) |
PROBABILITY_TO_CLOSE | NUMBER | Likelihood of fulfillment (0–100) |
STATUS | TEXT | Current status text |
DECLINED | BOOLEAN | True if the ask was declined |
GIFT_ASK_IS_DELETED | BOOLEAN | Soft delete flag |
PLEDGE_PAYMENTS
One row per scheduled installment against a pledge. A null GIFT_KEY means the installment was expected but not yet received.
| Column | Type | Notes |
|---|
PLEDGE_PAYMENT_KEY | TEXT | Primary key |
PLEDGE_KEY | TEXT | FK → PLEDGES |
GIFT_KEY | TEXT | FK → GIFTS (nullable) — null = installment expected but not received |
EXPECTED_PAYMENT_DATE | TIMESTAMP | Date the installment was expected |
FULFILL_PAYMENT_DATE_TIME_UTC | TIMESTAMP | Date the installment was actually paid |
EXPECTED_AMOUNT | NUMBER | Amount expected for this installment |
ACTUAL_AMOUNT | NUMBER | Amount actually received |
PLEDGE_PAYMENT_IS_DELETED | BOOLEAN | Soft delete flag |
CONTACTS appears in this diagram as the giving unit making the pledge and receiving the ask. 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 recorded when a pledge payment is collected. 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 fund the pledge and ask are associated with. 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 |
PLEDGES.STATUS is a numeric code. Decode it using TYPE_REFERENCES with ENTITY = 'Pledge' and PROPERTY = 'Status'. 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.
