Analytics

Tags, custom fields, and an activity log. A cross-cutting data layer that can attach labels, user-defined fields, and audit history to any entity type.

Tables

taggings

Column	Type	Description
id	uuid	Primary key, auto-generated
org_id	uuid	References organizations(id), cascade delete
tag_id	uuid	References tags(id), cascade delete
entity_type	text	Target entity kind (contact, company, deal, task, ticket, document, project, event)
entity_id	uuid	ID of the tagged entity
created_at	timestamptz	Row creation timestamp
updated_at	timestamptz	Auto-updated on modification
metadata	jsonb	Arbitrary key-value data

custom_field_definitions

Column	Type	Description
id	uuid	Primary key, auto-generated
org_id	uuid	References organizations(id), cascade delete
entity_type	text	Which entity type this field applies to (contact, company, deal, task, ticket, document, project)
name	text	Field label, unique per org and entity type
field_type	field_type	Data type for this field, defaults to text
description	text	Optional explanation of the field
is_required	boolean	Whether a value is mandatory, defaults to false
options	jsonb	Allowed values for select and multi_select fields
position	integer	Display order, defaults to 0
created_at	timestamptz	Row creation timestamp
updated_at	timestamptz	Auto-updated on modification
metadata	jsonb	Arbitrary key-value data

custom_field_values

Column	Type	Description
id	uuid	Primary key, auto-generated
org_id	uuid	References organizations(id), cascade delete
field_id	uuid	References custom_field_definitions(id), cascade delete
entity_type	text	Target entity kind (contact, company, deal, task, ticket, document, project)
entity_id	uuid	ID of the entity this value belongs to
value_text	text	Text value column
value_number	numeric	Numeric value column
value_boolean	boolean	Boolean value column
value_date	date	Date value column
value_json	jsonb	JSON value column (for select, multi_select, etc.)
created_at	timestamptz	Row creation timestamp
updated_at	timestamptz	Auto-updated on modification
metadata	jsonb	Arbitrary key-value data

activities

Column	Type	Description
id	uuid	Primary key, auto-generated
org_id	uuid	References organizations(id), cascade delete
actor_id	uuid	References users(id), set null on delete. NULL for system-generated activities
entity_type	text	Target entity kind (contact, company, deal, task, ticket, document, project, subscription, invoice, event)
entity_id	uuid	ID of the entity this activity relates to
action	activity_action	What happened
description	text	Human-readable summary
changes	jsonb	JSON diff, e.g. {"status": {"from": "open", "to": "closed"}}
occurred_at	timestamptz	When the action took place. Can differ from created_at for imported data
created_at	timestamptz	Row creation timestamp
updated_at	timestamptz	Auto-updated on modification
metadata	jsonb	Arbitrary key-value data

Enums

field_type

Value	Description
text	Free-form text
number	Numeric value
boolean	True or false
date	Calendar date
select	Single choice from a list of options
multi_select	Multiple choices from a list of options
url	URL string
email	Email address string

activity_action

Value	Description
created	Entity was created
updated	Entity was updated
deleted	Entity was deleted
status_changed	Status field changed
assigned	Entity was assigned to a user
commented	A comment was added
viewed	Entity was viewed
email_sent	An email was sent
email_received	An email was received
note_added	A note was attached
call_logged	A phone call was recorded
stage_changed	Pipeline stage changed
deal_won	Deal marked as won
deal_lost	Deal marked as lost
task_completed	Task marked as done
payment_received	Payment was received

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

Activity feed for a contact:

SELECT
  a.action,
  a.description,
  a.entity_type,
  u.full_name AS actor,
  a.occurred_at
FROM activities a
LEFT JOIN users u ON u.id = a.actor_id
WHERE a.entity_type = 'contact' AND a.entity_id = '<contact_id>'
ORDER BY a.occurred_at DESC
LIMIT 50;

Get all tags for an entity:

SELECT t.name, t.color
FROM tags t
JOIN taggings tg ON tg.tag_id = t.id
WHERE tg.entity_type = 'deal' AND tg.entity_id = '<deal_id>';

Get custom field values for a contact:

SELECT
  cfd.name AS field_name,
  cfd.field_type,
  coalesce(
    cfv.value_text,
    cfv.value_number::text,
    cfv.value_boolean::text,
    cfv.value_date::text,
    cfv.value_json::text
  ) AS value
FROM custom_field_definitions cfd
LEFT JOIN custom_field_values cfv
  ON cfv.field_id = cfd.id
  AND cfv.entity_type = 'contact'
  AND cfv.entity_id = '<contact_id>'
WHERE cfd.entity_type = 'contact'
ORDER BY cfd.position;

Cross-entity activity feed for a deal and its related contact and company:

WITH deal_context AS (
  SELECT id AS entity_id, 'deal' AS entity_type FROM deals WHERE id = '<deal_id>'
  UNION ALL
  SELECT id, 'contact' FROM contacts WHERE id = (SELECT contact_id FROM deals WHERE id = '<deal_id>')
  UNION ALL
  SELECT id, 'company' FROM companies WHERE id = (SELECT company_id FROM deals WHERE id = '<deal_id>')
)
SELECT
  a.action,
  a.description,
  a.entity_type,
  u.full_name AS actor,
  a.changes,
  a.occurred_at
FROM activities a
JOIN deal_context dc ON a.entity_type = dc.entity_type AND a.entity_id = dc.entity_id
LEFT JOIN users u ON u.id = a.actor_id
ORDER BY a.occurred_at DESC
LIMIT 100;

Find entities with a specific tag:

SELECT tg.entity_type, tg.entity_id, tg.created_at
FROM taggings tg
JOIN tags t ON t.id = tg.tag_id
WHERE t.name = 'VIP'
ORDER BY tg.created_at DESC;

Count activities by action type in the last 30 days:

SELECT
  action,
  count(*) AS total
FROM activities
WHERE occurred_at >= now() - interval '30 days'
GROUP BY action
ORDER BY total DESC;

analytics