Agreements — Technical Reference

This page covers the data models, automated versioning systems, signing endpoints, and security policies behind the Agreements module.

Data Models

agreement_templates

Stores metadata and body content for the active template configurations.

Column	Type	Description
`id`	`uuid`	Primary key
`workspace_id`	`uuid`	Tenant isolation — FK → `workspaces.id` (`ON DELETE CASCADE`)
`title`	`text`	Template title (e.g., "Standard Liability Waiver")
`body_html`	`text`	Rich-text HTML content of the agreement
`version`	`integer`	Current active version identifier (starts at 1)
`is_active`	`boolean`	Flag to enable/disable templates (default `true`)
`created_by`	`uuid`	Creator profile — FK → `user_profiles.id`
`created_at`	`timestamptz`	Record creation timestamp
`updated_at`	`timestamptz`	Last updated timestamp

agreement_template_versions

Append-only history log containing previous versions of template bodies for audit trails.

Column	Type	Description
`id`	`uuid`	Primary key
`template_id`	`uuid`	Origin template — FK → `agreement_templates.id` (`ON DELETE CASCADE`)
`version`	`integer`	Archived version number
`body_html`	`text`	HTML content at this version
`updated_by`	`uuid`	Editor profile — FK → `user_profiles.id`
`created_at`	`timestamptz`	Timestamp when this version was archived

contact_agreements

Dispatched agreement instances assigned to contacts.

Column	Type	Description
`id`	`uuid`	Primary key (used as the unguessable public signing token)
`workspace_id`	`uuid`	Tenant isolation — FK → `workspaces.id` (`ON DELETE CASCADE`)
`contact_id`	`uuid`	Target contact — FK → `contacts.id` (`ON DELETE CASCADE`)
`template_id`	`uuid`	Target template — FK → `agreement_templates.id` (`ON DELETE RESTRICT`)
`status`	`text`	`'pending'` or `'signed'` (CHECK constraint)
`template_version`	`integer`	The template version matched at the moment of signing
`snapshot_body`	`text`	Immutable copy of the HTML body signed by the contact
`signed_by_name`	`text`	typed name value of the signer
`signed_at`	`timestamptz`	Signature timestamp
`ip_address`	`text`	Submitter IP address resolved from headers
`sent_by`	`uuid`	Sender profile — FK → `user_profiles.id` (null for automated sends)
`created_at`	`timestamptz`	Record creation timestamp

Constraints & Indexes:

idx_unique_pending_agreement conditional unique index on (contact_id, template_id) WHERE status = 'pending' (prevents concurrent race conditions or double dispatches).

Versioning & Deletion Architecture

Automated Version Archiving

When an administrator modifies an existing template:

If only the title changes, the system updates the record inline.
If the body_html is modified, the database or application layer automatically pushes the prior body and version identifier to agreement_template_versions before incrementing the version column on agreement_templates.

Cascade Deletion Safeguards

To preserve compliance audit integrity:

Database foreign keys restrict template deletion if active or signed rows exist in contact_agreements.
Deletes throw a Restrict exception, forcing administrators to Archive (is_active = false) templates instead of deleting them.

Cancelling Pending Agreements

To allow administrators to cancel outstanding agreement requests:

Pending agreements (status = 'pending') can be hard-deleted from contact_agreements by workspace Owners or Admins (via the cancelAgreement server action).
Signed agreements (status = 'signed') are locked. Both application-layer validation and database check constraints prevent deletion or modification of signed agreements to preserve compliance audit integrity.

Dispatch & Signing Workflows

Dispatch Deduplication Logic

When sending agreements manually or via the send_agreement automation:

Evaluated State	CRM Behavior
No existing record	Creates a new `contact_agreements` row in status `'pending'`.
Pending status exists	Retains the existing token and resends the link (acts as a reminder).
Signed (Current Version)	Skips dispatch entirely (no email sent).
Signed (Older Version)	Generates a new pending record to collect signatures on the updated version.

Idempotency & Concurrency Safeguards

PostgreSQL Unique Violations: To prevent race conditions from concurrent webhook events (e.g. from Eventbrite), the database enforces the idx_unique_pending_agreement index. The agreement dispatcher (src/lib/services/agreement-dispatch.ts) catches unique violation error code 23505 and safely/silently skips duplicate pending record insertions.
Automation Trigger Gating: The database add_tag helper action returns the affected row only on new insertions. Downstream tag_applied triggers are gated on this return value (inserted), preventing duplicate trigger runs on redundant tag updates.

Public Signing Pipeline

Token Authorization: Public signing links use the UUID of the contact_agreements record:
```
GET https://app.gordoncrm.com/agree/{token}
```
The endpoint is bypass-configured in the middleware to allow unauthenticated GET and POST requests.
Signing Execution (POST /api/agreements/sign): The client-side form submits the typed name. Since signers are not authenticated CRM users, the backend executes using a Supabase admin client (createAdminClient()) to transition the status to 'signed', capture the signer IP, and store the immutable snapshot_body.

Sender Resolution Chain

Verification emails resolve sending domains in the following order:

Workspace verified domain.
Workspace fallback domain ({slug}-{hash}@[FALLBACK_SENDER_DOMAIN]).
Platform sender (notifications@app.gordoncrm.com).

Route Protection & Redirections

Workspace Module Route Protection

Route Protection: The router checks settings.modules.agreements on the server. If a workspace does not have the agreements module enabled, the /agreements dashboard returns a forbidden state or redirects.
UI Gating: Entry points such as the contact details Agreements card and the "Send Agreement" automation action are hidden based on this toggle state.

Legacy Redirection

Settings Redirection: Requests targeting the legacy path /settings/agreements are permanently redirected (301 Redirect) to the unified /agreements path.

Row-Level Security (RLS)

Workspace members read only records matching their workspace credentials.

Table	SELECT	INSERT	UPDATE	DELETE
`agreement_templates`	Workspace members	Admin+	Admin+	Admin+
`agreement_template_versions`	Workspace members	Admin+	Admin+	Admin+
`contact_agreements`	Workspace members	All members	System API	Admin+

Events Courses