Activity Log

The activity_log table is the contact timeline — every meaningful event related to a contact is written here. AI agents building reports or re-engagement automations must use this table correctly.

Schema

SELECT id, id_company, id_customer, type, subject, body,
       created_at
FROM activity_log
WHERE id_company = :id_company
  AND id_customer = :id_customer
ORDER BY created_at DESC;

Column	Type	Notes
id_customer	int	FK → contacts table
type	varchar	Event type — see enum below
subject	varchar	Headline / short label
body	text	Detail content
created_at	timestamptz	Event timestamp

type enum

Value	Logged when
note_added	A note is written on a contact
email_sent	An email is sent to the contact
sms_sent	An SMS is sent to the contact
call_made	A call is logged via the dialer
deal_created	A deal is created for the contact
deal_won	A deal is marked won
deal_lost	A deal is marked lost
stage_changed	Contact moves to a new pipeline stage
ai_execution	An AI goal step runs
goal_complete	A goal sequence finishes
tag_added	A tag is applied to the contact

Writing to activity_log

Activity log writes must always be non-fatal. Never let a failed log write break the primary operation:

// Correct pattern — non-fatal
await db.query(`
  INSERT INTO activity_log (id_company, id_customer, type,
                            subject, body, created_at)
  VALUES (?, ?, ?, ?, ?, NOW())
`, [id_company, id_customer, type, subject, body])
.catch(() => {}); // never throw

Querying "last activity"

The contacts table has a last_activity column (unix integer epoch). This is the fastest way to check recency. For detailed activity history, query activity_log.

-- Contacts with no activity in 30 days
SELECT id, first_name, last_name, last_activity
FROM customers
WHERE id_company = :id_company
  AND deleted = 0
  AND (
    last_activity = 0
    OR last_activity < EXTRACT(EPOCH FROM NOW() - INTERVAL '30 days')::integer
  );

There is no contact_activities table

Several templates reference a contact_activities table — this does not exist. All contact activity is in activity_log. Do not attempt to create or query contact_activities.

Re-engagement reports

SELECT
  c.first_name,
  c.last_name,
  c.stage,
  TO_TIMESTAMP(c.last_activity) AS last_active_at,
  COUNT(al.id) AS total_activity_count
FROM customers c
LEFT JOIN activity_log al
  ON al.id_customer = c.id
  AND al.id_company = c.id_company
WHERE c.id_company = :id_company
  AND c.deleted = 0
  AND c.last_activity < EXTRACT(EPOCH FROM NOW() - INTERVAL '14 days')::integer
GROUP BY c.id, c.first_name, c.last_name, c.stage, c.last_activity
ORDER BY c.last_activity ASC;