-- brick-activity: contacts, and what was said and still has to be done. Tables prefixed activity_,
-- natural keys, bp_id BIGINT (modules/shared.yaml).
CREATE TABLE IF NOT EXISTS activity_contacts (
contact_id VARCHAR NOT NULL,
bp_id BIGINT NOT NULL, -- the company they work at: a business partner
full_name VARCHAR NOT NULL,
email VARCHAR NOT NULL, -- normalised lower-case; unique through activity_contact_emails
owner VARCHAR NOT NULL,
title VARCHAR NOT NULL,
phone VARCHAR NOT NULL,
subscribed BOOLEAN NOT NULL, -- consent to marketing email
status VARCHAR NOT NULL, -- active | left
created_at TIMESTAMP,
updated_at TIMESTAMP,
version INTEGER, -- 0.2.0: the optimistic version; NULL (a row from before) = 0
PRIMARY KEY (contact_id)
);
-- One email, one contact. DataK3 enforces nothing unique except a primary key, so the email IS the key
-- of this table, and a claim is a plain INSERT: a second contact with the same email fails with
-- 23505 instead of being written (an upsert here would silently move the email to the newcomer).
CREATE TABLE IF NOT EXISTS activity_contact_emails (
email VARCHAR NOT NULL,
contact_id VARCHAR NOT NULL,
created_at TIMESTAMP,
PRIMARY KEY (email)
);
-- Activities and tasks on one record: a task is an activity with a due date (domain/activity.py).
CREATE TABLE IF NOT EXISTS activity_log (
activity_id VARCHAR NOT NULL,
kind VARCHAR NOT NULL, -- NOTE CALL MEETING EMAIL TASK
subject VARCHAR NOT NULL,
owner VARCHAR NOT NULL,
bp_id BIGINT NOT NULL,
contact_id VARCHAR,
opportunity_id VARCHAR,
note VARCHAR NOT NULL,
direction VARCHAR,
occurred_at TIMESTAMP, -- set: it happened
due_on DATE, -- set: it is a task
status VARCHAR, -- tasks: open | done | cancelled
done_at TIMESTAMP,
created_at TIMESTAMP,
updated_at TIMESTAMP,
version INTEGER, -- 0.2.0: the optimistic version; NULL (a row from before) = 0
PRIMARY KEY (activity_id)
);
-- 0.2.0 — optimistic versions (erp_platform.repository; the CRM audit's lost updates, 2026-10-17). The columns are
-- at the END of the CREATEs above and added here for a bucket that predates them; the claim tables are new. A save
-- CLAIMS version n+1 with a plain INSERT in the same transaction as its rows: a racer from the same version fails
-- 23505 -> 409 "changed since you opened it". erp-migrate marks the ALTERs DISRUPTIVE (reads stall for minutes).
ALTER TABLE activity_contacts ADD COLUMN version INTEGER;
ALTER TABLE activity_log ADD COLUMN version INTEGER;
CREATE TABLE IF NOT EXISTS activity_contacts_versions (
contact_id VARCHAR NOT NULL,
version INTEGER NOT NULL,
saved_at TIMESTAMP WITHOUT TIME ZONE NOT NULL,
PRIMARY KEY (contact_id, version)
);
CREATE TABLE IF NOT EXISTS activity_log_versions (
activity_id VARCHAR NOT NULL,
version INTEGER NOT NULL,
saved_at TIMESTAMP WITHOUT TIME ZONE NOT NULL,
PRIMARY KEY (activity_id, version)
);