--- title: Data Lake Schema | PostGrid description: Full schema reference for all database tables available to query via PostGrid's Enhanced Reports feature, including contacts, letters, and more. --- All the tables available to query via our enhanced reports feature. Each heading below is the name of the database table as it should be spelled in your queries. Every table below except `organizations` carries an `organization` column holding the ID of the organization that owns the row. For most organizations that is a single constant value, but if your data lake includes sub-organization data it is how you tell each sub-organization’s rows apart — join it against `organizations` to label rows with a name. See [Querying across sub-organizations](/print-and-mail/api/enhanced-reports#querying-across-sub-organizations/index.md). ## contacts Contacts you have created, including the `from` and `to` addresses on every order. Contacts marked secret (for example the recipients of [seed mail](/print-and-mail/api/seed-mail/index.md)) have their name, address, and other personal fields replaced with `*****`, matching what the API returns for them. | Column | Type | PK | FK | Description | | --------------------- | ----------- | --- | --------------- | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | `id` | `VARCHAR` | Yes | | PostGrid resource ID (returned on creation; used to retrieve later). | | `createdAt` | `TIMESTAMP` | | | Timestamp when the record was created. | | `updatedAt` | `TIMESTAMP` | | | Timestamp when the record was last updated. | | `description` | `VARCHAR` | | | Freeform description for your order/campaign (helps you identify the mailing in the dashboard). | | `metadata` | `JSON` | | | Arbitrary key–value JSON attached to the object for analytics/segmentation (size-limited). | | `organization` | `VARCHAR` | | `organizations` | ID of the organization that owns the record. Always your own organization ID unless sub-organization data is included in your data lake, in which case sub-organization records carry their own ID. Joins against `organizations`. | | `firstName` | `VARCHAR` | | | | | `lastName` | `VARCHAR` | | | | | `companyName` | `VARCHAR` | | | | | `email` | `VARCHAR` | | | | | `phoneNumber` | `VARCHAR` | | | | | `addressLine1` | `VARCHAR` | | | | | `addressLine2` | `VARCHAR` | | | | | `city` | `VARCHAR` | | | | | `provinceOrState` | `VARCHAR` | | | | | `postalOrZip` | `VARCHAR` | | | | | `countryCode` | `VARCHAR` | | | ISO country code. | | `addressStatus` | `VARCHAR` | | | | | `addressErrors` | `JSON` | | | | | `skipVerification` | `BOOLEAN` | | | | | `forceVerifiedStatus` | `BOOLEAN` | | | | | `mailingLists` | `JSON` | | | | | `addressChange` | `JSON` | | | | ## letters One row per letter order. | Column | Type | PK | FK | Description | | ------------------------------ | ----------- | --- | --------------- | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | `id` | `VARCHAR` | Yes | | PostGrid resource ID (returned on creation; used to retrieve later). | | `createdAt` | `TIMESTAMP` | | | Timestamp when the record was created. | | `updatedAt` | `TIMESTAMP` | | | Timestamp when the record was last updated. | | `description` | `VARCHAR` | | | Freeform description for your order/campaign (helps you identify the mailing in the dashboard). | | `metadata` | `JSON` | | | Arbitrary key–value JSON attached to the object for analytics/segmentation (size-limited). | | `organization` | `VARCHAR` | | `organizations` | ID of the organization that owns the record. Always your own organization ID unless sub-organization data is included in your data lake, in which case sub-organization records carry their own ID. Joins against `organizations`. | | `sendDate` | `TIMESTAMP` | | | Scheduled print date. Defaults to the next business day if omitted. | | `from_id` | `VARCHAR` | | `contacts` | Sender contact ID (foreign key into `contacts`). | | `to_id` | `VARCHAR` | | `contacts` | Recipient contact ID (foreign key into `contacts`). | | `user` | `VARCHAR` | | | User that created the record (dashboard/API). | | `status` | `VARCHAR` | | | Order status in the delivery pipeline (e.g., `ready` → `printing` → `processed_for_delivery` → `completed`; `cancelled` possible). | | `pageCount` | `INTEGER` | | | Number of pages in the generated mail piece. | | `trackingNumber` | `VARCHAR` | | | Carrier tracking number (US orders get a USPS tracking number once processed). | | `imbStatus` | `VARCHAR` | | | Intelligent Mail Barcode status (US-only; e.g., `entered_mail_stream`, `out_for_delivery`, `returned_to_sender`). | | `imbZIPCode` | `VARCHAR` | | | ZIP code from IMB scan metadata (US-only). | | `imbDate` | `TIMESTAMP` | | | Timestamp of the latest IMB scan (US-only). | | `express` | `BOOLEAN` | | | Express shipping flag (expedited printing/shipping; extra charge). | | `mailingClass` | `VARCHAR` | | | Selected mail class (e.g., First Class vs Standard/Marketing Mail). | | `mergeVariables` | `JSON` | | | JSON used to populate `{{ }}` placeholders in templates at render time. | | `campaign` | `VARCHAR` | | | Campaign this order belongs to, if it was created as part of one. | | `cancellation` | `JSON` | | | Cancellation details (who cancelled and when), if the order was cancelled. | | `seededFromOrder` | `VARCHAR` | | | For seed mail, the ID of the order this seed mailer was generated from. | | `seedDeliveryInfo_dropDate` | `TIMESTAMP` | | | For seed mail, the date the seed mailer was dropped into the mail stream. | | `seedDeliveryInfo_arrivalDate` | `TIMESTAMP` | | | For seed mail, the date the seed mailer arrived. | | `color` | `BOOLEAN` | | | Whether the mail piece is printed in colour. | | `doubleSided` | `BOOLEAN` | | | Whether the mail piece is printed on both sides. | | `addressPlacement` | `VARCHAR` | | | Where the recipient address is placed (e.g., `top_first_page`, `insert_blank_page`). | | `envelope` | `VARCHAR` | | | Return envelope resource to include with the mailing, if supplied. | | `returnEnvelope` | `VARCHAR` | | | Return envelope included with the mailing, if supplied. | | `size` | `VARCHAR` | | | Print size/format for this collateral. | | `paper` | `VARCHAR` | | | Paper stock used for this collateral (premium papers only). | | `pdfWorkflowRun` | `VARCHAR` | | | PDF workflow run applied to the uploaded PDF, if any. | ## postcards One row per postcard order. | Column | Type | PK | FK | Description | | ------------------------------ | ----------- | --- | --------------- | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | `id` | `VARCHAR` | Yes | | PostGrid resource ID (returned on creation; used to retrieve later). | | `createdAt` | `TIMESTAMP` | | | Timestamp when the record was created. | | `updatedAt` | `TIMESTAMP` | | | Timestamp when the record was last updated. | | `description` | `VARCHAR` | | | Freeform description for your order/campaign (helps you identify the mailing in the dashboard). | | `metadata` | `JSON` | | | Arbitrary key–value JSON attached to the object for analytics/segmentation (size-limited). | | `organization` | `VARCHAR` | | `organizations` | ID of the organization that owns the record. Always your own organization ID unless sub-organization data is included in your data lake, in which case sub-organization records carry their own ID. Joins against `organizations`. | | `sendDate` | `TIMESTAMP` | | | Scheduled print date. Defaults to the next business day if omitted. | | `from_id` | `VARCHAR` | | `contacts` | Sender contact ID (foreign key into `contacts`). | | `to_id` | `VARCHAR` | | `contacts` | Recipient contact ID (foreign key into `contacts`). | | `user` | `VARCHAR` | | | User that created the record (dashboard/API). | | `status` | `VARCHAR` | | | Order status in the delivery pipeline (e.g., `ready` → `printing` → `processed_for_delivery` → `completed`; `cancelled` possible). | | `pageCount` | `INTEGER` | | | Number of pages in the generated mail piece. | | `trackingNumber` | `VARCHAR` | | | Carrier tracking number (US orders get a USPS tracking number once processed). | | `imbStatus` | `VARCHAR` | | | Intelligent Mail Barcode status (US-only; e.g., `entered_mail_stream`, `out_for_delivery`, `returned_to_sender`). | | `imbZIPCode` | `VARCHAR` | | | ZIP code from IMB scan metadata (US-only). | | `imbDate` | `TIMESTAMP` | | | Timestamp of the latest IMB scan (US-only). | | `express` | `BOOLEAN` | | | Express shipping flag (expedited printing/shipping; extra charge). | | `mailingClass` | `VARCHAR` | | | Selected mail class (e.g., First Class vs Standard/Marketing Mail). | | `mergeVariables` | `JSON` | | | JSON used to populate `{{ }}` placeholders in templates at render time. | | `campaign` | `VARCHAR` | | | Campaign this order belongs to, if it was created as part of one. | | `cancellation` | `JSON` | | | Cancellation details (who cancelled and when), if the order was cancelled. | | `seededFromOrder` | `VARCHAR` | | | For seed mail, the ID of the order this seed mailer was generated from. | | `seedDeliveryInfo_dropDate` | `TIMESTAMP` | | | For seed mail, the date the seed mailer was dropped into the mail stream. | | `seedDeliveryInfo_arrivalDate` | `TIMESTAMP` | | | For seed mail, the date the seed mailer arrived. | | `size` | `VARCHAR` | | | Print size/format for this collateral. | | `paper` | `VARCHAR` | | | Paper stock used for this collateral (premium papers only). | ## cheques One row per cheque order. | Column | Type | PK | FK | Description | | ---------------- | ----------- | --- | --------------- | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | `id` | `VARCHAR` | Yes | | PostGrid resource ID (returned on creation; used to retrieve later). | | `createdAt` | `TIMESTAMP` | | | Timestamp when the record was created. | | `updatedAt` | `TIMESTAMP` | | | Timestamp when the record was last updated. | | `description` | `VARCHAR` | | | Freeform description for your order/campaign (helps you identify the mailing in the dashboard). | | `metadata` | `JSON` | | | Arbitrary key–value JSON attached to the object for analytics/segmentation (size-limited). | | `organization` | `VARCHAR` | | `organizations` | ID of the organization that owns the record. Always your own organization ID unless sub-organization data is included in your data lake, in which case sub-organization records carry their own ID. Joins against `organizations`. | | `sendDate` | `TIMESTAMP` | | | Scheduled print date. Defaults to the next business day if omitted. | | `from_id` | `VARCHAR` | | `contacts` | Sender contact ID (foreign key into `contacts`). | | `to_id` | `VARCHAR` | | `contacts` | Recipient contact ID (foreign key into `contacts`). | | `user` | `VARCHAR` | | | User that created the record (dashboard/API). | | `status` | `VARCHAR` | | | Order status in the delivery pipeline (e.g., `ready` → `printing` → `processed_for_delivery` → `completed`; `cancelled` possible). | | `pageCount` | `INTEGER` | | | Number of pages in the generated mail piece. | | `trackingNumber` | `VARCHAR` | | | Carrier tracking number (US orders get a USPS tracking number once processed). | | `imbStatus` | `VARCHAR` | | | Intelligent Mail Barcode status (US-only; e.g., `entered_mail_stream`, `out_for_delivery`, `returned_to_sender`). | | `imbZIPCode` | `VARCHAR` | | | ZIP code from IMB scan metadata (US-only). | | `imbDate` | `TIMESTAMP` | | | Timestamp of the latest IMB scan (US-only). | | `express` | `BOOLEAN` | | | Express shipping flag (expedited printing/shipping; extra charge). | | `mailingClass` | `VARCHAR` | | | Selected mail class (e.g., First Class vs Standard/Marketing Mail). | | `mergeVariables` | `JSON` | | | JSON used to populate `{{ }}` placeholders in templates at render time. | | `campaign` | `VARCHAR` | | | Campaign this order belongs to, if it was created as part of one. | | `cancellation` | `JSON` | | | Cancellation details (who cancelled and when), if the order was cancelled. | | `size` | `VARCHAR` | | | Print size/format for this collateral. | | `bankAccount` | `VARCHAR` | | | Bank account used to issue the cheque. | | `amount` | `INTEGER` | | | Cheque amount. | | `currencyCode` | `VARCHAR` | | | ISO currency code for the cheque (e.g., USD, CAD). | | `envelope` | `VARCHAR` | | | Return envelope resource to include with the mailing, if supplied. | | `digitalOnly` | `JSON` | | | Digital-only cheque delivery details (recipient email and delivery state), if the cheque was sent digitally. | ## selfmailers One row per self-mailer order. | Column | Type | PK | FK | Description | | ---------------- | ----------- | --- | --------------- | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | `id` | `VARCHAR` | Yes | | PostGrid resource ID (returned on creation; used to retrieve later). | | `createdAt` | `TIMESTAMP` | | | Timestamp when the record was created. | | `updatedAt` | `TIMESTAMP` | | | Timestamp when the record was last updated. | | `description` | `VARCHAR` | | | Freeform description for your order/campaign (helps you identify the mailing in the dashboard). | | `metadata` | `JSON` | | | Arbitrary key–value JSON attached to the object for analytics/segmentation (size-limited). | | `organization` | `VARCHAR` | | `organizations` | ID of the organization that owns the record. Always your own organization ID unless sub-organization data is included in your data lake, in which case sub-organization records carry their own ID. Joins against `organizations`. | | `sendDate` | `TIMESTAMP` | | | Scheduled print date. Defaults to the next business day if omitted. | | `from_id` | `VARCHAR` | | `contacts` | Sender contact ID (foreign key into `contacts`). | | `to_id` | `VARCHAR` | | `contacts` | Recipient contact ID (foreign key into `contacts`). | | `user` | `VARCHAR` | | | User that created the record (dashboard/API). | | `status` | `VARCHAR` | | | Order status in the delivery pipeline (e.g., `ready` → `printing` → `processed_for_delivery` → `completed`; `cancelled` possible). | | `pageCount` | `INTEGER` | | | Number of pages in the generated mail piece. | | `trackingNumber` | `VARCHAR` | | | Carrier tracking number (US orders get a USPS tracking number once processed). | | `imbStatus` | `VARCHAR` | | | Intelligent Mail Barcode status (US-only; e.g., `entered_mail_stream`, `out_for_delivery`, `returned_to_sender`). | | `imbZIPCode` | `VARCHAR` | | | ZIP code from IMB scan metadata (US-only). | | `imbDate` | `TIMESTAMP` | | | Timestamp of the latest IMB scan (US-only). | | `express` | `BOOLEAN` | | | Express shipping flag (expedited printing/shipping; extra charge). | | `mailingClass` | `VARCHAR` | | | Selected mail class (e.g., First Class vs Standard/Marketing Mail). | | `mergeVariables` | `JSON` | | | JSON used to populate `{{ }}` placeholders in templates at render time. | | `campaign` | `VARCHAR` | | | Campaign this order belongs to, if it was created as part of one. | | `cancellation` | `JSON` | | | Cancellation details (who cancelled and when), if the order was cancelled. | | `size` | `VARCHAR` | | | Print size/format for this collateral. | | `paper` | `VARCHAR` | | | Paper stock used for this collateral (premium papers only). | ## snappacks One row per snap-pack order. | Column | Type | PK | FK | Description | | ---------------- | ----------- | --- | --------------- | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | `id` | `VARCHAR` | Yes | | PostGrid resource ID (returned on creation; used to retrieve later). | | `createdAt` | `TIMESTAMP` | | | Timestamp when the record was created. | | `updatedAt` | `TIMESTAMP` | | | Timestamp when the record was last updated. | | `description` | `VARCHAR` | | | Freeform description for your order/campaign (helps you identify the mailing in the dashboard). | | `metadata` | `JSON` | | | Arbitrary key–value JSON attached to the object for analytics/segmentation (size-limited). | | `organization` | `VARCHAR` | | `organizations` | ID of the organization that owns the record. Always your own organization ID unless sub-organization data is included in your data lake, in which case sub-organization records carry their own ID. Joins against `organizations`. | | `sendDate` | `TIMESTAMP` | | | Scheduled print date. Defaults to the next business day if omitted. | | `from_id` | `VARCHAR` | | `contacts` | Sender contact ID (foreign key into `contacts`). | | `to_id` | `VARCHAR` | | `contacts` | Recipient contact ID (foreign key into `contacts`). | | `user` | `VARCHAR` | | | User that created the record (dashboard/API). | | `status` | `VARCHAR` | | | Order status in the delivery pipeline (e.g., `ready` → `printing` → `processed_for_delivery` → `completed`; `cancelled` possible). | | `pageCount` | `INTEGER` | | | Number of pages in the generated mail piece. | | `trackingNumber` | `VARCHAR` | | | Carrier tracking number (US orders get a USPS tracking number once processed). | | `imbStatus` | `VARCHAR` | | | Intelligent Mail Barcode status (US-only; e.g., `entered_mail_stream`, `out_for_delivery`, `returned_to_sender`). | | `imbZIPCode` | `VARCHAR` | | | ZIP code from IMB scan metadata (US-only). | | `imbDate` | `TIMESTAMP` | | | Timestamp of the latest IMB scan (US-only). | | `express` | `BOOLEAN` | | | Express shipping flag (expedited printing/shipping; extra charge). | | `mailingClass` | `VARCHAR` | | | Selected mail class (e.g., First Class vs Standard/Marketing Mail). | | `mergeVariables` | `JSON` | | | JSON used to populate `{{ }}` placeholders in templates at render time. | | `campaign` | `VARCHAR` | | | Campaign this order belongs to, if it was created as part of one. | | `cancellation` | `JSON` | | | Cancellation details (who cancelled and when), if the order was cancelled. | | `size` | `VARCHAR` | | | Print size/format for this collateral. | ## trackervisits One row per visit to a QR code or PURL tracker. | Column | Type | PK | FK | Description | | -------------- | ----------- | --- | --------------- | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | `id` | `VARCHAR` | Yes | | PostGrid resource ID (returned on creation; used to retrieve later). | | `createdAt` | `TIMESTAMP` | | | Timestamp when the record was created. | | `updatedAt` | `TIMESTAMP` | | | Timestamp when the record was last updated. | | `description` | `VARCHAR` | | | Freeform description for your order/campaign (helps you identify the mailing in the dashboard). | | `metadata` | `JSON` | | | Arbitrary key–value JSON attached to the object for analytics/segmentation (size-limited). | | `organization` | `VARCHAR` | | `organizations` | ID of the organization that owns the record. Always your own organization ID unless sub-organization data is included in your data lake, in which case sub-organization records carry their own ID. Joins against `organizations`. | | `tracker` | `VARCHAR` | | | Tracker ID (QR code / PURL definition) that generated this visit. | | `orderID` | `VARCHAR` | | | Order ID (letter/postcard/cheque/self-mailer) associated with this tracker visit. | | `device` | `VARCHAR` | | | Device classification captured for the visit (e.g., mobile/desktop). | | `ipAddress` | `VARCHAR` | | | Visitor IP captured for the visit. | ## documents One row per document you have uploaded. | Column | Type | PK | FK | Description | | -------------- | ----------- | --- | --------------- | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | `id` | `VARCHAR` | Yes | | PostGrid resource ID (returned on creation; used to retrieve later). | | `createdAt` | `TIMESTAMP` | | | Timestamp when the record was created. | | `updatedAt` | `TIMESTAMP` | | | Timestamp when the record was last updated. | | `description` | `VARCHAR` | | | Freeform description for your order/campaign (helps you identify the mailing in the dashboard). | | `metadata` | `JSON` | | | Arbitrary key–value JSON attached to the object for analytics/segmentation (size-limited). | | `organization` | `VARCHAR` | | `organizations` | ID of the organization that owns the record. Always your own organization ID unless sub-organization data is included in your data lake, in which case sub-organization records carry their own ID. Joins against `organizations`. | ## virtualmailboxes One row per virtual mailbox. | Column | Type | PK | FK | Description | | -------------- | ----------- | --- | --------------- | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | `id` | `VARCHAR` | Yes | | PostGrid resource ID (returned on creation; used to retrieve later). | | `createdAt` | `TIMESTAMP` | | | Timestamp when the record was created. | | `updatedAt` | `TIMESTAMP` | | | Timestamp when the record was last updated. | | `description` | `VARCHAR` | | | Freeform description for your order/campaign (helps you identify the mailing in the dashboard). | | `metadata` | `JSON` | | | Arbitrary key–value JSON attached to the object for analytics/segmentation (size-limited). | | `organization` | `VARCHAR` | | `organizations` | ID of the organization that owns the record. Always your own organization ID unless sub-organization data is included in your data lake, in which case sub-organization records carry their own ID. Joins against `organizations`. | | `name` | `VARCHAR` | | | Name of the virtual mailbox. | | `status` | `VARCHAR` | | | Provisioning status of the virtual mailbox. | | `capabilities` | `JSON` | | | What the mailbox can do (e.g., scanning, forwarding). | | `countryCode` | `VARCHAR` | | | ISO country code of the mailbox address. | ## virtualmailboxitems One row per piece of mail received in a virtual mailbox. | Column | Type | PK | FK | Description | | ---------------- | ----------- | --- | ------------------ | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | `id` | `VARCHAR` | Yes | | PostGrid resource ID (returned on creation; used to retrieve later). | | `createdAt` | `TIMESTAMP` | | | Timestamp when the record was created. | | `updatedAt` | `TIMESTAMP` | | | Timestamp when the record was last updated. | | `description` | `VARCHAR` | | | Freeform description for your order/campaign (helps you identify the mailing in the dashboard). | | `metadata` | `JSON` | | | Arbitrary key–value JSON attached to the object for analytics/segmentation (size-limited). | | `organization` | `VARCHAR` | | `organizations` | ID of the organization that owns the record. Always your own organization ID unless sub-organization data is included in your data lake, in which case sub-organization records carry their own ID. Joins against `organizations`. | | `virtualMailbox` | `VARCHAR` | | `virtualmailboxes` | Virtual mailbox this item arrived in (foreign key into `virtualmailboxes`). | | `matchedLetter` | `VARCHAR` | | `letters` | Letter this returned item was matched back to, if any (foreign key into `letters`). | | `returnReason` | `VARCHAR` | | | Why the mail piece came back (e.g., undeliverable, moved). | ## organizations Your organization, plus every sub-organization whose data is included in your data lake. Join it on the `organization` column of any other table to label rows with the organization that owns them — see [Querying across sub-organizations](/print-and-mail/api/enhanced-reports#querying-across-sub-organizations/index.md). | Column | Type | PK | FK | Description | | ----------- | ----------- | --- | -- | ------------------------------------------------------------------------ | | `id` | `VARCHAR` | Yes | | Organization ID. Matches the `organization` column on every other table. | | `name` | `VARCHAR` | | | Organization name, as shown in the dashboard. | | `updatedAt` | `TIMESTAMP` | | | Timestamp when the organization was last updated. |