Grain: GIFTS — one row per posted gift transaction. GIFT_DESIGNATIONS — one row per project split within a gift (a gift can have multiple rows).
GIFTS is the central financial fact table. This page maps every table that connects to it — how money arrives (recurring commitments, pledges), where it goes (designations to projects), and what is attached to it (grant records, tributes, premium fulfillments, entry batches).
Solid lines — required join (FK always populated). Dotted lines — optional join (FK is nullable; always use LEFT JOIN).
Table columns
Tables on this page: GIFTS · GIFT_DESIGNATIONS · PROJECTS · GIFT_BATCHES · GIFT_ASKS · PLEDGES · PLEDGE_PAYMENTS · RECURRING_GIFTS · RECURRING_GIFT_PAYMENTS · RECURRING_GIFT_DESIGNATIONS · GRANTS · TRIBUTES · GIFT_PREMIUMS
GIFTS
One row per posted gift transaction. The amount on GIFTS is the total; split across projects via GIFT_DESIGNATIONS.
| Column | Type | Notes |
|---|
GIFT_KEY | TEXT | Primary key |
CONTACT_KEY | TEXT | FK → CONTACTS — the giving unit |
CONTACT_INDIVIDUAL_KEY | TEXT | FK → CONTACT_INDIVIDUALS (optional) — specific person if recorded at entry |
SEGMENT_KEY | TEXT | FK → SEGMENTS (optional) — campaign attribution entry point |
GIFT_ASK_KEY | TEXT | FK → GIFT_ASKS (optional) — the ask this gift fulfills |
GIFT_BATCH_KEY | TEXT | FK → GIFT_BATCHES (optional) — entry batch this gift was part of |
GRANT_KEY | TEXT | FK → GRANTS (optional) — linked grant record |
TRIBUTE_KEY | TEXT | FK → TRIBUTES (optional) — in-honor-of or in-memory-of tribute |
GIFT_DATE_UTC | TIMESTAMP | Date the gift was made |
RECEIPT_DATE_UTC | TIMESTAMP | Date the gift was receipted |
THANK_YOU_DATE_UTC | TIMESTAMP | Date a thank-you was sent |
AMOUNT | NUMBER | Total gift amount |
GIFT_TYPE | NUMBER | Payment method — decode with TYPE_REFERENCES (Entity: Gift, Property: GiftType) |
FAIR_MARKET_VALUE | NUMBER | Fair market value of any premium received — typically non-tax-deductible |
CHECK_NUMBER | TEXT | Check number for check gift types |
ACCOUNTING_CODE | TEXT | External accounting code |
TRANSACTION_SOURCE | TEXT | Source platform name (e.g. third-party processor) |
CREDIT_CARD_TYPE | TEXT | Credit card type for credit gifts |
CURRENCY_CODE | TEXT | Currency code |
TICKER_SYMBOL | TEXT | Stock ticker symbol (stock gifts) |
NUMBER_OF_SHARES | NUMBER | Shares received (stock gifts) |
CRYPTOCOIN_TYPE | TEXT | Cryptocurrency type (crypto gifts) |
GIFT_IS_DELETED | BOOLEAN | Soft delete flag — always filter WHERE GIFT_IS_DELETED = FALSE |
GIFT_IS_PRIVATE | BOOLEAN | Gift is marked private |
GIFT_IS_TAX_DEDUCTIBLE | BOOLEAN | Gift is tax-deductible |
GIFT_DESIGNATIONS
One row per project split within a gift. A single gift can have multiple designation rows. AMOUNT_DESIGNATED across all rows for a gift sums to GIFTS.AMOUNT.
| Column | Type | Notes |
|---|
GIFT_DESIGNATION_KEY | TEXT | Primary key |
GIFT_KEY | TEXT | FK → GIFTS |
PROJECT_KEY | TEXT | FK → PROJECTS — the fund this portion goes to |
AMOUNT_DESIGNATED | NUMBER | Amount allocated to this project |
CURRENCY_CODE | TEXT | Currency code |
GIFT_DESIGNATION_IS_DELETED | BOOLEAN | Soft delete flag — always filter WHERE GIFT_DESIGNATION_IS_DELETED = FALSE |
PROJECTS
One row per fund or program. The destination for gift designations and recurring gift designations.
| Column | Type | Notes |
|---|
PROJECT_KEY | TEXT | Primary key |
NAME | TEXT | Project name |
DESCRIPTION | TEXT | Extended description |
TYPE | TEXT | Project type |
REVENUE_ACCOUNTING_CODE | TEXT | Accounting code for revenue recognition |
EXTERNAL_ACCOUNTING_CODE | TEXT | External system accounting code |
BEGINNING_BALANCE | NUMBER | Opening balance |
CURRENT_BALANCE | NUMBER | Current running balance |
START_DATE | TIMESTAMP | Project start date |
END_DATE | TIMESTAMP | Project end date |
PROJECT_IS_ACTIVE | BOOLEAN | Whether the project is currently active |
PROJECT_IS_DEFAULT | BOOLEAN | Whether this is the default project for unspecified designations |
PROJECT_IS_DELETED | BOOLEAN | Soft delete flag |
PROJECT_IS_TAX_DEDUCTIBLE | BOOLEAN | Gifts to this project are tax-deductible |
GIFT_BATCHES
One row per entry batch — a named group of gifts entered together. Useful for reconciliation and audit workflows.
| Column | Type | Notes |
|---|
GIFT_BATCH_KEY | TEXT | Primary key |
BATCH | TEXT | Batch reference ID or name |
TOTAL | NUMBER | Total gift count or amount in the batch |
GIFT_BATCH_IS_DELETED | BOOLEAN | Soft delete flag |
GIFT_ASKS
One row per formal solicitation made to a contact. Linked optionally to GIFTS (when the ask is fulfilled) and PLEDGES (when the ask results in a pledge).
| 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 |
PLEDGES
One row per pledge commitment. A pledge can exist without a gift ask — always use LEFT JOIN when connecting to GIFT_ASKS. Track fulfillment 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 |
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 |
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 |
GRANTS
One row per grant record. Grants are linked to a gift (GIFT_KEY) when funds are received, and to a project (PROJECT_KEY) for the purpose.
| Column | Type | Notes |
|---|
GRANT_KEY | TEXT | Primary key |
GIFT_KEY | TEXT | FK → GIFTS — the gift that records the received funds |
PROJECT_KEY | TEXT | FK → PROJECTS — project the grant is for |
TITLE | TEXT | Grant title |
STATUS | TEXT | Current status |
GRANT_TYPE | NUMBER | Incoming or outgoing — decode with TYPE_REFERENCES (Entity: Grant, Property: GrantType) |
TOTAL_ANTICIPATED_AMOUNT | NUMBER | Expected total award |
AWARDED_AMOUNT | NUMBER | Actual awarded amount |
AWARDED_DATE_UTC | TIMESTAMP | Date the grant was awarded |
DUE_DATE_UTC | TIMESTAMP | Application or reporting due date |
SUBMISSION_DATE_UTC | TIMESTAMP | Date the application was submitted |
AWARDED_GRANT | BOOLEAN | True if the grant was awarded |
DESCRIPTION | TEXT | Extended description |
GRANT_IS_DELETED | BOOLEAN | Soft delete flag |
TRIBUTES
One row per tribute record — gifts made in honor of or in memory of an individual. Linked to the honoree via CONTACT_INDIVIDUAL_KEY.
| Column | Type | Notes |
|---|
TRIBUTE_KEY | TEXT | Primary key |
CONTACT_INDIVIDUAL_KEY | TEXT | FK → CONTACT_INDIVIDUALS — the person being honored |
TRIBUTE_TYPE | NUMBER | In Honor Of or In Memory Of — decode with TYPE_REFERENCES (Entity: Gift, Property: TributeType) |
FIRST_NAME | TEXT | First name of the honoree |
LAST_NAME | TEXT | Last name of the honoree |
TOTAL_GIFTS | NUMBER | Count of gifts associated with this tribute |
TOTAL_GIFT_AMOUNT | NUMBER | Total giving associated with this tribute |
TRIBUTE_IS_DELETED | BOOLEAN | Soft delete flag |
GIFT_PREMIUMS
One row per premium item fulfilled as part of a gift (e.g. a thank-you gift sent to the donor). Links the gift to the premium catalog item.
| Column | Type | Notes |
|---|
GIFT_PREMIUM_KEY | TEXT | Primary key |
GIFT_KEY | TEXT | FK → GIFTS |
PREMIUM_KEY | TEXT | FK → PREMIUMS — the catalog item fulfilled |
QUANTITY | NUMBER | Number of items sent |
TOTAL_VALUE | NUMBER | Total value of premiums sent |
GIFT_PREMIUM_IS_DELETED | BOOLEAN | Soft delete flag |
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.
