Database Schema

All data is stored in Cloudflare D1 (serverless SQLite). The schema is defined across migration files in the migrations/ directory. The createDB(d1) factory in src/db.js returns async store objects for each table.

Tables

oauth_tokens

Stores Pipedrive OAuth2 tokens per user. Used for both interactive sessions and automation user lookups.

Column Type Description
user_id INTEGER Primary key. Pipedrive user ID.
company_id INTEGER Pipedrive company ID. Indexed for session restoration.
access_token TEXT Current OAuth2 access token.
refresh_token TEXT OAuth2 refresh token.
expires_at INTEGER Token expiration timestamp (Unix epoch).
api_domain TEXT Pipedrive API domain (e.g., https://api.pipedrive.com).
created_at INTEGER Row creation timestamp.
updated_at INTEGER Last update timestamp.

Indexes: idx_oauth_company on company_id.

app_settings

Per-company key-value configuration store. Holds webhook URLs, field mappings, notification preferences, Google Ads settings, webhook credentials, and more.

Column Type Description
company_id INTEGER Pipedrive company ID. Part of compound primary key.
key TEXT Setting key (e.g., webhookUrl, adwordsWebhookUsername, adwordsCurrencyCode). Part of compound primary key.
value TEXT Setting value. Complex values are JSON-serialized.
updated_at INTEGER Last update timestamp.

Primary key: (company_id, key).

sessions

D1-backed session store managed by src/session.js. Cookie-based session tracking with proxy-based dirty detection.

Column Type Description
sid TEXT Primary key. Session ID (from cookie).
data TEXT JSON-serialized session data.
expires_at INTEGER Expiration timestamp. Expired sessions are cleaned up probabilistically.

Indexes: idx_sessions_expires on expires_at.

flip_merge

Temporary storage for flip-merge state (swapping primary and secondary deals). Replaces an in-memory Map for stateless Workers.

Column Type Description
user_id INTEGER Primary key. Pipedrive user ID.
deal_id TEXT The deal ID to flip.
created_at INTEGER Row creation timestamp.

merge_logs

Audit log of all deal/person merges performed through the extension.

Column Type Description
id INTEGER Primary key. Auto-increment.
company_id INTEGER Pipedrive company ID.
user_id INTEGER User who performed the merge.
created_at INTEGER Merge timestamp.
primary_deal_id INTEGER Surviving deal ID.
primary_deal_title TEXT Surviving deal title.
secondary_deal_id INTEGER Merged (deleted) deal ID.
secondary_deal_title TEXT Merged deal title.
primary_person_id INTEGER Surviving person ID (nullable).
primary_person_name TEXT Surviving person name.
secondary_person_id INTEGER Merged person ID (nullable).
secondary_person_name TEXT Merged person name.
merge_persons INTEGER Whether person merge was requested (0/1).
resubscribe_primary INTEGER Whether resubscribe flag was set (0/1).
skip_unsubscribe INTEGER Whether unsubscribe was skipped (0/1).
person_merged INTEGER Whether person merge succeeded (0/1).
person_merge_error TEXT Error message if person merge failed.
unsubscribe_success INTEGER Whether webhook unsubscribe succeeded (nullable).
resubscribe_success INTEGER Whether webhook resubscribe succeeded (nullable).
preserve_deal_count INTEGER Number of deal fields preserved.
preserve_person_count INTEGER Number of person fields preserved.
snapshot TEXT JSON snapshot of field values at merge time.

Indexes: idx_merge_logs_company on company_id, idx_merge_logs_created on (company_id, created_at DESC).

google_ads_tokens

Stores Google Ads OAuth2 tokens per company. Separate from the Pipedrive OAuth tokens.

Column Type Description
company_id INTEGER Primary key. Pipedrive company ID.
access_token TEXT Google OAuth2 access token.
refresh_token TEXT Google OAuth2 refresh token.
expires_at INTEGER Token expiration timestamp (Unix epoch).
created_at TEXT Row creation timestamp.
updated_at TEXT Last update timestamp.

adwords_conversions

Log of all Google Ads conversion uploads. Includes full request/response payloads for debugging.

Column Type Description
id INTEGER Primary key. Auto-increment.
company_id INTEGER Pipedrive company ID.
deal_id INTEGER Pipedrive deal ID.
milestone TEXT Conversion name (stored in the milestone column for backward compatibility).
gclid TEXT Google Click ID.
conversion_action_id TEXT Google Ads conversion action resource name.
conversion_value REAL Monetary value of the conversion.
currency_code TEXT Currency code (default: USD).
conversion_date_time TEXT Conversion timestamp in Google Ads format.
order_id TEXT Unique order ID for deduplication.
adjustment_type TEXT CONVERSION for all new uploads.
status TEXT success, error, or queued.
error_message TEXT Error details if upload failed.
group_id INTEGER Conversion action row ID (nullable).
group_uuid TEXT Legacy field (nullable, no longer used).
group_name TEXT Conversion action name at time of upload.
request_payload TEXT JSON of the request sent to Google Ads API.
response_payload TEXT JSON of the response from Google Ads API.
webhook_source_ip TEXT IP address of the inbound webhook.
webhook_request TEXT JSON of the inbound webhook request body.
webhook_response TEXT JSON of the response sent back to the webhook caller.
created_at TEXT Row creation timestamp.

Indexes: idx_adwords_conv_company, idx_adwords_conv_deal, idx_adwords_conv_group.

conversion_groups

Defines conversion actions. Each row maps to one Google Ads conversion action with a name and value.

Column Type Description
id INTEGER Primary key. Auto-increment.
company_id INTEGER Pipedrive company ID.
uuid TEXT Unique. Legacy identifier (no longer used in webhook URLs).
name TEXT Conversion name used in webhook body (e.g., Inquiry).
conversion_action_id TEXT Google Ads conversion action resource name.
conversion_action_name TEXT Display name of the conversion action.
upload_customer_id TEXT Target customer ID for uploads (may differ from MCC for cross-account tracking).
initial_value REAL Conversion value (accessed as value in the application layer).
initial_milestone_id TEXT Legacy field (unused).
currency_code TEXT Legacy field (currency is now a global setting).
milestones TEXT Legacy field (restatements removed).
created_at TEXT Row creation timestamp.
updated_at TEXT Last update timestamp.

Indexes: idx_conv_groups_company on company_id.

retry_queue

General-purpose retry queue for deferred background tasks. Used for Google Ads GCLID retry (gads_click_not_found).

Column Type Description
id INTEGER Primary key. Auto-increment.
company_id INTEGER Pipedrive company ID.
queue_type TEXT Task type identifier (e.g., gads_click_not_found).
payload TEXT JSON-serialized task data.
status TEXT pending, processing, completed, or failed. Default: pending.
retry_after TEXT Earliest processing time (datetime).
attempt_count INTEGER Number of attempts made. Default: 0.
max_attempts INTEGER Maximum retry attempts before marking as failed. Default: 5.
error_message TEXT Most recent error message (nullable).
created_at TEXT Row creation timestamp.
updated_at TEXT Last update timestamp.

Indexes: idx_retry_queue_pending on (status, retry_after), idx_retry_queue_company on company_id.

Previous Deployment