Grain: CONTACTS — one row per giving unit (a household or organization). CONTACT_INDIVIDUALS — one row per person within a contact.
CONTACTS is the central entity in Virtuous — it represents the giving unit (a household or organization). This diagram shows the tables that make up a contact’s profile: the people within it (CONTACT_INDIVIDUALS) and the stewardship activity around it.
Key concept: A CONTACT is the giving unit. A CONTACT_INDIVIDUAL is a person within that contact. Gifts belong to the contact; emails are sent to individuals. Always join to CONTACT_INDIVIDUALS when you need a person’s name or email address.
Solid lines — required join (FK always populated). Dotted lines — optional join (FK is nullable; always use LEFT JOIN).
Table columns
Tables on this page: CONTACTS · CONTACT_INDIVIDUALS · NOTES · TASKS · RECEIPTS · GIFTS (boundary)
One row per giving unit. LIFE_TO_DATE_GIVING and YEAR_TO_DATE_GIVING are pre-aggregated giving totals — no join to GIFTS required. For all contact-related tables, see Contacts — extended reference.
| Column | Type | Notes |
|---|
CONTACT_KEY | TEXT | Primary key |
NAME | TEXT | Contact display name |
CONTACT_TYPE | TEXT | Customizable type — base types are Household, Foundation, Organization |
PRIMARY_CITY | TEXT | City from primary address |
PRIMARY_STATE_CODE | TEXT | State from primary address |
LIFE_TO_DATE_GIVING | NUMBER | Pre-aggregated total giving (all time) |
YEAR_TO_DATE_GIVING | NUMBER | Pre-aggregated total giving (current calendar year) |
LAST_GIFT_AMOUNT | NUMBER | Amount of the most recent gift |
LAST_GIFT_DATE_UTC | TIMESTAMP | Date of the most recent gift |
CONTACT_IS_DELETED | BOOLEAN | Soft delete flag — always filter WHERE CONTACT_IS_DELETED = FALSE |
CONTACT_IS_ARCHIVED | BOOLEAN | Contact is archived |
CONTACT_INDIVIDUAL_IS_PRIMARY = TRUE to get the main person. Email and phone live here, not on CONTACTS.
| Column | Type | Notes |
|---|
CONTACT_INDIVIDUAL_KEY | TEXT | Primary key |
CONTACT_KEY | TEXT | FK → CONTACTS |
FIRST_NAME | TEXT | First name |
LAST_NAME | TEXT | Last name |
PRIMARY_EMAIL | TEXT | Primary email address |
PRIMARY_PHONE | TEXT | Primary phone number |
BIRTH_DATE | TIMESTAMP | Full date of birth |
CONTACT_INDIVIDUAL_IS_PRIMARY | BOOLEAN | True for the primary person on the contact |
CONTACT_INDIVIDUAL_IS_DECEASED | BOOLEAN | Individual is deceased |
CONTACT_INDIVIDUAL_IS_GLOBALLY_UNSUBSCRIBED | BOOLEAN | Individual has globally unsubscribed from email |
CONTACT_INDIVIDUAL_IS_DELETED | BOOLEAN | Soft delete flag |
NOTES
One row per stewardship note logged against a contact or individual. PLAIN_TEXT_VALUE contains the note content without formatting.
| Column | Type | Notes |
|---|
NOTE_KEY | TEXT | Primary key |
CONTACT_KEY | TEXT | FK → CONTACTS |
CONTACT_INDIVIDUAL_KEY | TEXT | FK → CONTACT_INDIVIDUALS (optional) — individual the note is about |
DATE_TIME_UTC | TIMESTAMP | Date and time the note was recorded |
VALUE | TEXT | Note content (may include HTML formatting) |
PLAIN_TEXT_VALUE | TEXT | Note content as plain text |
CONTACT_NOTE_TYPE | TEXT | Note type (e.g. Meeting, Call, Email) |
IMPORTANT | BOOLEAN | Note is flagged as important |
NOTE_IS_PRIVATE | BOOLEAN | Note is private |
NOTE_IS_DELETED | BOOLEAN | Soft delete flag |
TASKS
One row per action item on a contact. TASK_STATUS is a numeric code.
| Column | Type | Notes |
|---|
TASK_KEY | TEXT | Primary key |
CONTACT_KEY | TEXT | FK → CONTACTS |
CONTACT_INDIVIDUAL_KEY | TEXT | FK → CONTACT_INDIVIDUALS (optional) |
NAME | TEXT | Task name or title |
DESCRIPTION | TEXT | Extended task description |
DUE_DATE_TIME_UTC | TIMESTAMP | Task due date |
RESOLUTION_DATE_TIME_UTC | TIMESTAMP | Date the task was completed or dismissed |
TASK_STATUS | NUMBER | Current status — decode with TYPE_REFERENCES (Entity: Task, Property: TaskStatus) |
TASK_IS_DELETED | BOOLEAN | Soft delete flag |
RECEIPTS
One row per receipt issued to a contact. For the full receipts hub including receipted gifts and statement runs, see Receipts — extended reference.
| Column | Type | Notes |
|---|
RECEIPT_KEY | TEXT | Primary key |
CONTACT_KEY | TEXT | FK → CONTACTS |
RECEIPT_TYPE | NUMBER | Type of receipt — decode with TYPE_REFERENCES (Entity: Receipt, Property: ReceiptType) |
RECEIPT_MEDIUM | NUMBER | Delivery medium — decode with TYPE_REFERENCES (Entity: Receipt, Property: ReceiptMedium) |
YEAR | NUMBER | Tax year for the receipt |
RECEIPT_IS_AVAILABLE_TO_DONOR | BOOLEAN | Receipt is visible in the donor portal |
RECEIPT_IS_DELETED | BOOLEAN | Soft delete flag |
GIFTS
GIFTS appears in this diagram as the transaction linked to a contact. 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 |
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.
