notifications
This table stores notification delivery records for email, SMS, and in-app notification events. It provides traceability for who was notified, how, when, and about what resource.
Purpose
The purpose of this table is to provide an audit trail and operational record of notifications sent by the system across multiple channels and workflows.
What this table does
- Stores one notification delivery attempt or event
- Tracks type, channel, target, and delivery status
- Supports operational troubleshooting and notification history
- Provides a reusable notification record across forms, RFIs, certifications, and more
Why this table is defined
DirtView relies heavily on email and SMS for workflow communication. The platform needs to know whether notifications were created, sent, delivered, failed, or muted, and for which business event.
Columns
| Column | Type | Required | Description | Example |
|---|---|---|---|---|
id | uuid | Yes | Primary key for the notification record | notif_001 |
tenant_id | uuid | Yes | Tenant owning the notification | tenant_001 |
user_id | uuid | No | Internal target user if applicable | user_123 |
external_email | text | No | External recipient email if applicable | architect@studioaxis.com |
external_phone | text | No | External recipient phone if applicable | +1-555-0202 |
type | text | Yes | Business notification type | rfi_status_change |
channel | text | Yes | Delivery channel | email |
status | text | Yes | Delivery status | sent |
resource_type | text | No | Related business resource type | rfi |
resource_id | uuid | No | Related business resource ID | rfi_001 |
provider | text | No | Delivery provider | sendgrid |
provider_message_id | text | No | Provider-specific ID | sg_abc123 |
payload | jsonb | Yes | Rendered or structured payload details | {"subject":"RFI-042 updated"} |
error_message | text | No | Error text if delivery failed | SMTP timeout |
sent_at | timestamptz | No | Timestamp of send attempt | 2026-04-11 13:15:00+00 |
delivered_at | timestamptz | No | Timestamp of delivery confirmation | 2026-04-11 13:15:05+00 |
created_at | timestamptz | Yes | Creation timestamp | 2026-04-11 13:14:59+00 |
Relationships
- user_id may reference a platform user/profile
- resource_id references the resource named by
resource_type - Notifications may be created from RFI, form, certification, scheduling, or other services
How it is used
- Written by notification workers/services
- Used for audit, retry, and support investigation
- Used to display notification history where appropriate
- Used to track failures and provider interactions
Access and security
- This table may contain recipient contact info and should be handled carefully
- Not all notification payload data should be exposed to end users
- Access should be limited based on relevance and role
Example scenarios
Scenario 1: RFI email alert
An email notification is created and sent when an RFI changes status.
Scenario 2: Form submission SMS
A form submission triggers SMS delivery to an opted-in user group.
Scenario 3: Failed delivery
A provider timeout occurs and the notification record stores the failure details for retry or support review.
Notes and assumptions
- One business event may produce many notification rows
- Keep delivery history even if the related resource is later deleted or archived
- Use indexes on
(tenant_id, type, created_at)and provider IDs as needed