Integrations

Connected apps, OAuth tokens, webhooks, and sync logs. Manages third-party connections, credential storage, outgoing webhook delivery, and data synchronization tracking.

Tables

connected_apps

Column	Type	Description
id	uuid	Primary key, auto-generated
org_id	uuid	References organizations(id), cascade delete
name	text	Display name of the connected app
provider	text	Provider identifier (slack, hubspot, stripe, etc.)
status	integration_status	Connection health status, defaults to active
config	jsonb	Provider-specific configuration
connected_by	uuid	References users(id), set null on delete
connected_at	timestamptz	When the connection was established
created_at	timestamptz	Row creation timestamp
updated_at	timestamptz	Auto-updated on modification
metadata	jsonb	Arbitrary key-value data

oauth_tokens

Column	Type	Description
id	uuid	Primary key, auto-generated
org_id	uuid	References organizations(id), cascade delete
app_id	uuid	References connected_apps(id), cascade delete
user_id	uuid	References users(id), cascade delete
access_token_enc	text	Encrypted access token
refresh_token_enc	text	Encrypted refresh token (optional)
token_type	text	Token type, defaults to bearer
scopes	text[]	Granted OAuth scopes
expires_at	timestamptz	Token expiration time
created_at	timestamptz	Row creation timestamp
updated_at	timestamptz	Auto-updated on modification
metadata	jsonb	Arbitrary key-value data

webhooks

Column	Type	Description
id	uuid	Primary key, auto-generated
org_id	uuid	References organizations(id), cascade delete
url	text	Destination URL for webhook delivery
secret	text	Signing secret for payload verification
events	text[]	Event types this webhook subscribes to
is_active	boolean	Whether the webhook is enabled, defaults to true
created_by	uuid	References users(id), set null on delete
created_at	timestamptz	Row creation timestamp
updated_at	timestamptz	Auto-updated on modification
metadata	jsonb	Arbitrary key-value data

webhook_events

Column	Type	Description
id	uuid	Primary key, auto-generated
org_id	uuid	References organizations(id), cascade delete
webhook_id	uuid	References webhooks(id), cascade delete
event_type	text	The type of event that triggered delivery
payload	jsonb	Event payload sent to the endpoint
status	webhook_event_status	Delivery status, defaults to pending
response_code	smallint	HTTP response code from the endpoint
attempts	integer	Number of delivery attempts, defaults to 0
last_attempt_at	timestamptz	Timestamp of the most recent attempt
created_at	timestamptz	Row creation timestamp
updated_at	timestamptz	Auto-updated on modification
metadata	jsonb	Arbitrary key-value data

sync_logs

Column	Type	Description
id	uuid	Primary key, auto-generated
org_id	uuid	References organizations(id), cascade delete
app_id	uuid	References connected_apps(id), cascade delete
direction	sync_direction	Whether the sync is inbound or outbound
entity_type	text	Type of entity being synced (contacts, deals, etc.)
entity_id	uuid	Optional specific entity being synced
status	sync_status	Current sync status, defaults to pending
records_processed	integer	Number of records handled, defaults to 0
records_failed	integer	Number of records that failed, defaults to 0
started_at	timestamptz	When the sync run began
completed_at	timestamptz	When the sync run finished
error	text	Error details if the sync failed
created_at	timestamptz	Row creation timestamp
updated_at	timestamptz	Auto-updated on modification
metadata	jsonb	Arbitrary key-value data

Enums

integration_status

Value	Description
active	Connection is healthy and operational
inactive	Connection is disabled
error	Connection has a problem that needs attention

webhook_event_status

Value	Description
pending	Event is queued for delivery
sent	Event was delivered successfully
failed	Delivery failed after all retry attempts

sync_direction

Value	Description
inbound	Data flowing from the external app into the platform
outbound	Data flowing from the platform to the external app

sync_status

Value	Description
pending	Sync is queued but has not started
running	Sync is currently executing
completed	Sync finished successfully
failed	Sync finished with an error

Row-Level Security

All five tables are scoped to the current user's organization via get_user_org_id(). Select, insert, and update are allowed for any org member. Delete requires the is_admin() check.

Dependencies

identity -- organizations and users tables, get_user_org_id() and is_admin() functions, set_updated_at() trigger function

Example Queries

List all active connected apps for the current org:

SELECT id, name, provider, status, connected_at
FROM connected_apps
WHERE status = 'active'
ORDER BY connected_at DESC;

Webhook delivery status summary (last 30 days):

SELECT
  w.url,
  count(we.id) AS total_events,
  count(we.id) FILTER (WHERE we.status = 'sent') AS delivered,
  count(we.id) FILTER (WHERE we.status = 'failed') AS failed,
  count(we.id) FILTER (WHERE we.status = 'pending') AS pending
FROM webhooks w
LEFT JOIN webhook_events we ON we.webhook_id = w.id
  AND we.created_at >= now() - interval '30 days'
WHERE w.is_active = true
GROUP BY w.id, w.url
ORDER BY w.url;

Sync history for a specific app:

SELECT
  sl.direction,
  sl.entity_type,
  sl.status,
  sl.records_processed,
  sl.records_failed,
  sl.started_at,
  sl.completed_at,
  sl.error
FROM sync_logs sl
WHERE sl.app_id = '<app_id>'
ORDER BY sl.started_at DESC
LIMIT 20;

Failed syncs in the last 7 days:

SELECT
  ca.name AS app_name,
  sl.direction,
  sl.entity_type,
  sl.error,
  sl.started_at
FROM sync_logs sl
JOIN connected_apps ca ON ca.id = sl.app_id
WHERE sl.status = 'failed'
  AND sl.started_at >= now() - interval '7 days'
ORDER BY sl.started_at DESC;

Active integrations with token expiration status:

SELECT
  ca.name AS app_name,
  ca.provider,
  ot.user_id,
  ot.scopes,
  ot.expires_at,
  CASE
    WHEN ot.expires_at < now() THEN 'expired'
    WHEN ot.expires_at < now() + interval '7 days' THEN 'expiring_soon'
    ELSE 'valid'
  END AS token_status
FROM connected_apps ca
JOIN oauth_tokens ot ON ot.app_id = ca.id
WHERE ca.status = 'active'
ORDER BY ot.expires_at ASC;

Failed webhook events awaiting retry:

SELECT
  w.url,
  we.event_type,
  we.attempts,
  we.response_code,
  we.last_attempt_at,
  we.payload
FROM webhook_events we
JOIN webhooks w ON w.id = we.webhook_id
WHERE we.status = 'failed'
ORDER BY we.last_attempt_at DESC;