Product Specification
This is the full product specification for PR Tracker. It serves as the source of truth for the data model, tool inventory, and system behavior.
Overview
PR Tracker is a specialized CRM for public relations work. It tracks outlets, contacts, channels, speaking engagements, awards, campaigns, opportunities, coverage, and email communications. It stores full-text copies of articles, media clips (video, audio), and email threads with contacts.
The system is designed for agent-first interaction via MCP (Model Context Protocol). There is no GUI in V1. Users interact through AI agents (Claude Cowork, Claude Code, OpenClaw, etc.). A web UI may be added in a future release.
This is a single-company production system. It does not need multi-tenancy. Multiple team members authenticate via Google OAuth, and each may connect multiple agents.
Architecture
┌─────────────────────┐ ┌──────────────────────┐ ┌─────────────────┐
│ AI Agents │ │ Cloudflare Worker │ │ Supabase │
│ (Claude, OpenClaw) │────▶│ MCP Server (TS) │────▶│ - Postgres DB │
│ │ SSE │ - Auth validation │ │ - Auth (OAuth) │
│ │ │ - Tool handlers │ │ - Storage │
└─────────────────────┘ │ - Rate limiting │ └─────────────────┘
│ - Email sync (cron) │ │
└──────────────────────┘ │
│ │
┌────────┼────────┐ │
▼ ▼ ▼ │
┌──────┐ ┌──────────┐ ┌──────────────┐ │
│Slack │ │ Gmail API │ │ Slack │◀────┘
│Notif.│ │ (system │ │ Webhooks │ (DB triggers)
└──────┘ │ inbox) │ └──────────────┘
└──────────┘Components
| Component | Technology | Purpose |
|---|---|---|
| Database | Supabase Postgres | All application data, change log, RLS policies |
| Auth | Supabase Auth + Google OAuth | User authentication, agent API keys |
| Media Storage | Supabase Storage | Articles, video clips, audio clips |
| MCP Server | Cloudflare Worker (TypeScript) | Agent-facing tool interface over SSE |
| Email Sync | Cloudflare Worker Cron Trigger + Gmail API | Ingest emails from shared system inbox |
| Notifications | Slack Webhooks | Alert users on opportunity changes |
| CLI Admin | Supabase CLI, Wrangler CLI | Schema migrations, deployments, troubleshooting |
Why These Choices
- Supabase: Single platform for DB + Auth + Storage. Postgres gives us triggers for the change log, JSONB for flexible channel attributes, and full-text search for coverage.
- Cloudflare Workers: Already in use. Handles SSE connections for MCP. Global edge deployment. No cold start issues like Supabase Edge Functions.
- MCP over SSE: Native protocol for Claude agents. Structured tool calls with typed schemas. OAuth 2.0 auth support built into the spec.
Data Model
Entity Relationship Summary
Campaign 1──∞ Opportunity ∞──1 Channel ∞──1 Outlet
│ │
∞ ∞
│ │
OpportunityContact ContactOutlet
│ │
∞ ∞
│ │
Contact ◄──────────────────────┘
│
┌─────────┼──────────┬──────────┐
∞ ∞ ∞ ∞
│ │ │ │
Opportunity ∞──∞ Coverage Email Note
│ (thread + │
∞ messages) ∞
│ │ NoteLink ──▶ Contact, Outlet,
Media EmailContact Opportunity, Channel
EmailOpportunityInteraction Log: A unified timeline view (not a table) that queries across Notes, Emails, and Coverage for a given contact, outlet, or opportunity. Answers: "What have we done with X?"
Tables
outlet
Represents any entity we interact with: media company, podcast network, individual publisher, conference organizer, awards body.
| Column | Type | Description |
|---|---|---|
| id | uuid (PK) | |
| name | text NOT NULL | Outlet name |
| description | text | About this outlet |
| website | text | Primary website URL |
| tier | smallint | 1 (top-tier), 2, 3 |
| geographic_reach | text | local, regional, national, global |
| parent_company | text | Parent organization if applicable |
| industry_focus | text[] | Array of industry/topic tags |
| notes | text | Free-form notes |
| archived_at | timestamptz | Soft delete |
| created_at | timestamptz | |
| updated_at | timestamptz |
channel
A specific channel within an outlet. An outlet can have many channels.
| Column | Type | Description |
|---|---|---|
| id | uuid (PK) | |
| outlet_id | uuid FK | |
| type | channel_type ENUM | website, newsletter, podcast, tv_broadcast, print, event, award |
| name | text | Channel name (may differ from outlet name) |
| description | text | |
| url | text | Channel-specific URL |
| frequency | text | daily, weekly, monthly, quarterly, annual |
| attributes | jsonb | Channel-type-specific metrics (see below) |
| archived_at | timestamptz | Soft delete |
| created_at | timestamptz | |
| updated_at | timestamptz |
Channel-type attribute schemas (stored in attributes JSONB, populated as available):
website:
{
"monthly_unique_visitors": null,
"domain_authority": null,
"social_followers": null,
"content_categories": [],
"syndication_partners": []
}newsletter:
{
"subscriber_count": null,
"open_rate": null,
"click_through_rate": null,
"paid_vs_free": null,
"audience_segment": null
}podcast:
{
"downloads_per_episode": null,
"total_subscribers": null,
"platform_distribution": {},
"average_episode_length_minutes": null,
"format": null
}tv_broadcast:
{
"viewership": null,
"market_rank": null,
"time_slot": null,
"segment_length_minutes": null,
"live_vs_recorded": null
}print:
{
"circulation": null,
"readership": null,
"geographic_distribution": null,
"ad_rate_cpm": null
}event:
{
"attendee_count": null,
"attendee_seniority_mix": null,
"industry_vertical": null,
"sponsorship_tiers": [],
"speaking_formats": []
}award:
{
"prestige_tier": null,
"industry_focus": null,
"judging_criteria": null,
"entry_fee": null,
"number_of_categories": null,
"winner_announcement_reach": null
}contact
A person we interact with. May be a journalist, editor, producer, podcast host, event organizer, freelancer, etc.
| Column | Type | Description |
|---|---|---|
| id | uuid (PK) | |
| first_name | text NOT NULL | |
| last_name | text NOT NULL | |
| text | Primary email | |
| phone | text | |
| title | text | Job title |
| bio | text | |
| is_freelance | boolean DEFAULT false | Not tied to a single outlet |
| social_links | jsonb | { "twitter": "", "linkedin": "", ... } |
| preferences | jsonb | Contact preferences, notes on best way to reach |
| notes | text | |
| archived_at | timestamptz | Soft delete |
| created_at | timestamptz | |
| updated_at | timestamptz |
contact_outlet
Many-to-many: contacts can work with multiple outlets, and outlets have multiple contacts. Tracks history (contacts move between outlets).
| Column | Type | Description |
|---|---|---|
| id | uuid (PK) | |
| contact_id | uuid FK | |
| outlet_id | uuid FK | |
| role | text | Their role at this outlet |
| is_primary | boolean DEFAULT true | Current (true) or historical (false) |
| start_date | date | When they started |
| end_date | date | When they left (null = current) |
| created_at | timestamptz |
campaign
Groups related opportunities. Every opportunity belongs to a campaign.
| Column | Type | Description |
|---|---|---|
| id | uuid (PK) | |
| name | text NOT NULL | |
| description | text | |
| status | text DEFAULT 'active' | active, paused, completed |
| start_date | date | |
| end_date | date | |
| archived_at | timestamptz | Soft delete |
| created_at | timestamptz | |
| updated_at | timestamptz |
A "General" campaign is created as seed data and serves as the default.
opportunity
The core workflow object — like a "deal" in a CRM. Moves through stages from identification to resolution.
| Column | Type | Description |
|---|---|---|
| id | uuid (PK) | |
| title | text NOT NULL | |
| description | text | |
| campaign_id | uuid FK | DEFAULT to "General" campaign |
| channel_id | uuid FK | The specific channel this targets (nullable) |
| outlet_id | uuid FK | The outlet (nullable if channel is set) |
| stage | opportunity_stage ENUM | See stages below |
| priority | text | high, medium, low |
| target_date | date | When we're aiming for |
| notes | text | |
| owner_user_id | uuid FK | Team member who owns this |
| next_action | text | Next concrete step needed |
| next_action_date | date | When the next action is due |
| next_action_owner | text | Name of the team member who owns this action |
| archived_at | timestamptz | Soft delete |
| created_at | timestamptz | |
| updated_at | timestamptz |
Opportunity stages (enum opportunity_stage):
| Stage | Description |
|---|---|
researching | Identifying and evaluating the opportunity |
drafting | Preparing pitch or submission materials |
pitched | Outreach sent |
responded | Received a reply (positive, negative, or follow-up) |
in_progress | Active dialogue, materials being exchanged |
placed | Coverage or participation confirmed |
published | Coverage is live / event completed |
won | Successful outcome (award won, major placement) |
lost | Declined, no response after follow-ups, or killed |
opportunity_contact
Many-to-many: which contacts are involved in an opportunity.
| Column | Type | Description |
|---|---|---|
| id | uuid (PK) | |
| opportunity_id | uuid FK | |
| contact_id | uuid FK | |
| role | text | Their role in this opportunity (e.g., "editor", "pitch target") |
| created_at | timestamptz |
coverage
A piece of coverage or result. Can be an article, TV segment, podcast appearance, award, event, etc.
| Column | Type | Description |
|---|---|---|
| id | uuid (PK) | |
| title | text NOT NULL | Headline or title |
| description | text | Summary |
| url | text | Link to original |
| published_date | date | |
| coverage_type | text | article, interview, review, opinion, listicle, roundup, segment, appearance, award_win, other |
| depth | text | mention, quote, feature, dedicated, cover_story |
| sentiment | text | positive, neutral, negative, mixed |
| share_of_voice | text | sole_focus, shared, competitor_included |
| spokesperson_quoted | boolean | Whether our spokesperson was quoted |
| initiated_by | text | pitched, organic |
| full_text | text | Full text copy of the article/transcript |
| notes | text | |
| archived_at | timestamptz | Soft delete |
| created_at | timestamptz | |
| updated_at | timestamptz |
coverage_outlet
Links coverage to outlets, contacts, channels, and opportunities. All links are optional.
| Column | Type | Description |
|---|---|---|
| id | uuid (PK) | |
| coverage_id | uuid FK | |
| outlet_id | uuid FK | Nullable |
| contact_id | uuid FK | Nullable |
| channel_id | uuid FK | Nullable |
| opportunity_id | uuid FK | Nullable |
| created_at | timestamptz |
media
Media files attached to coverage. Stored in Supabase Storage.
| Column | Type | Description |
|---|---|---|
| id | uuid (PK) | |
| coverage_id | uuid FK | |
| type | text | article_pdf, video, video_clip, audio, audio_clip, image, document |
| filename | text | Original filename |
| storage_path | text | Path in Supabase Storage bucket |
| mime_type | text | |
| size_bytes | bigint | |
| duration_seconds | integer | For audio/video |
| metadata | jsonb | Additional metadata |
| archived_at | timestamptz | Soft delete |
| created_at | timestamptz |
email_thread
A Gmail thread containing one or more messages.
| Column | Type | Description |
|---|---|---|
| id | uuid (PK) | |
| gmail_thread_id | text UNIQUE NOT NULL | Gmail's thread ID (dedup key) |
| subject | text | Thread subject line |
| last_message_at | timestamptz | Timestamp of most recent message |
| message_count | integer DEFAULT 0 | Number of messages in thread |
| archived_at | timestamptz | Soft delete |
| created_at | timestamptz | |
| updated_at | timestamptz |
email_message
An individual email message within a thread. Deduped by Gmail message ID.
| Column | Type | Description |
|---|---|---|
| id | uuid (PK) | |
| thread_id | uuid FK | |
| gmail_message_id | text UNIQUE NOT NULL | Gmail's message ID (dedup key) |
| from_address | text NOT NULL | Sender email |
| from_name | text | Sender display name |
| to_addresses | jsonb | Array of { email, name } |
| cc_addresses | jsonb | Array of { email, name } |
| subject | text | Message subject |
| body_text | text | Plain text body |
| body_html | text | HTML body |
| snippet | text | Gmail's auto-generated snippet |
| sent_at | timestamptz NOT NULL | When the email was sent |
| gmail_labels | text[] | Gmail labels at time of sync |
| raw_headers | jsonb | Select headers (In-Reply-To, References, etc.) |
| created_at | timestamptz | When we ingested this message |
email_contact
Links email messages to known contacts.
| Column | Type | Description |
|---|---|---|
| id | uuid (PK) | |
| email_message_id | uuid FK | |
| contact_id | uuid FK | |
| role | text NOT NULL | from, to, cc |
| created_at | timestamptz |
Unique constraint: (email_message_id, contact_id, role)
email_opportunity
Optional link between an email thread and an opportunity.
| Column | Type | Description |
|---|---|---|
| id | uuid (PK) | |
| thread_id | uuid FK | |
| opportunity_id | uuid FK | |
| created_at | timestamptz |
Unique constraint: (thread_id, opportunity_id)
email_sync_state
Tracks the sync cursor for the system Gmail inbox.
| Column | Type | Description |
|---|---|---|
| id | uuid (PK) | |
| gmail_history_id | text | Gmail history ID for incremental sync |
| last_sync_at | timestamptz | When the last successful sync completed |
| emails_synced_total | bigint DEFAULT 0 | Running count of messages synced |
| updated_at | timestamptz |
This table has exactly one row, updated after each sync cycle.
note
A general-purpose note. Can represent meeting recaps, phone call logs, research, observations, or any other information worth recording.
| Column | Type | Description |
|---|---|---|
| id | uuid (PK) | |
| type | note_type ENUM | general, meeting, phone_call, event, research, other |
| title | text | Short title or subject (optional) |
| content | text | Note body (optional) |
| occurred_at | timestamptz DEFAULT now() | When this happened (supports backdating) |
| archived_at | timestamptz | Soft delete |
| created_at | timestamptz | When the note was recorded |
| updated_at | timestamptz | Last modified |
note_link
Links notes to contacts, outlets, opportunities, and/or channels. A single note can have multiple links.
| Column | Type | Description |
|---|---|---|
| id | uuid (PK) | |
| note_id | uuid FK | |
| contact_id | uuid FK | Nullable |
| outlet_id | uuid FK | Nullable |
| opportunity_id | uuid FK | Nullable |
| channel_id | uuid FK | Nullable |
| created_at | timestamptz |
Non-Destructive Operations: Change Log
All mutations are tracked automatically via Postgres triggers. No data is permanently deleted through the application layer.
change_log
| Column | Type | Description |
|---|---|---|
| id | bigserial (PK) | |
| table_name | text NOT NULL | Which table was changed |
| record_id | uuid NOT NULL | PK of the changed record |
| action | text NOT NULL | insert, update, archive, restore |
| old_values | jsonb | Previous state (null for inserts) |
| new_values | jsonb | New state (null for archives) |
| changed_fields | text[] | List of fields that changed (for updates) |
| user_id | uuid | Which user's session |
| agent_id | text | Which agent made the change |
| agent_session | text | Session identifier for grouping related changes |
| created_at | timestamptz DEFAULT now() |
Postgres trigger (applied to all tables)
CREATE OR REPLACE FUNCTION log_change() RETURNS trigger AS $$
BEGIN
IF TG_OP = 'INSERT' THEN
INSERT INTO change_log (table_name, record_id, action, new_values, user_id, agent_id, agent_session)
VALUES (TG_TABLE_NAME, NEW.id, 'insert', to_jsonb(NEW),
current_setting('app.user_id', true)::uuid,
current_setting('app.agent_id', true),
current_setting('app.agent_session', true));
RETURN NEW;
ELSIF TG_OP = 'UPDATE' THEN
INSERT INTO change_log (table_name, record_id, action, old_values, new_values, user_id, agent_id, agent_session)
VALUES (TG_TABLE_NAME, NEW.id, 'update', to_jsonb(OLD), to_jsonb(NEW),
current_setting('app.user_id', true)::uuid,
current_setting('app.agent_id', true),
current_setting('app.agent_session', true));
RETURN NEW;
END IF;
RETURN NULL;
END;
$$ LANGUAGE plpgsql;Rollback mechanism
The MCP server exposes a rollback_change tool that:
- Looks up the change_log entry by ID
- Applies the
old_valuesback to the record - Logs the rollback as a new change_log entry (action:
restore)
For bulk rollback (e.g., "undo everything agent X did in session Y"), the tool can accept an agent_session parameter and revert all changes in reverse chronological order.
Authentication and Authorization
User Authentication
- Google OAuth via Supabase Auth
- Team members sign in with company Google accounts
- Supabase manages sessions and JWTs
Agent Authentication
- Each agent connection uses an API key scoped to a user
- API keys are stored in an
agent_keytable:
| Column | Type | Description |
|---|---|---|
| id | uuid (PK) | |
| user_id | uuid FK | Owner |
| name | text | Human label (e.g., "Claude Cowork - Desktop") |
| key_hash | text | Hashed API key |
| agent_type | text | claude_cowork, claude_code, openclaw, other |
| permissions | jsonb | Optional permission restrictions |
| last_used_at | timestamptz | |
| revoked_at | timestamptz | Soft revoke |
| created_at | timestamptz |
MCP Auth Flow
- User authenticates via Google OAuth in a one-time setup
- User creates an API key via a CLI command or MCP tool
- Agent connects to MCP server with
Authorization: Bearer <api_key> - MCP server validates key, sets
app.user_idandapp.agent_idon the Postgres session - All subsequent queries are scoped to the authenticated user's permissions
Row-Level Security (RLS)
Since this is a single-company system, RLS is simple:
- All authenticated users can read all records
- All authenticated users can create and update records
- No one can hard-delete (only archive)
- Change log is append-only (no updates or deletes)
MCP Server -- Tool Inventory
CRUD Tools (per entity)
Each major entity gets a standard set of tools:
| Tool | Description |
|---|---|
create_outlet | Create a new outlet |
get_outlet | Get outlet by ID with its channels and contacts |
list_outlets | List/search outlets with filters |
update_outlet | Update outlet fields |
archive_outlet | Soft-delete an outlet |
restore_outlet | Restore an archived outlet |
Same pattern for: contact, channel, campaign, opportunity, coverage, media, note.
Note Tools
| Tool | Description |
|---|---|
create_note | Create a note with links to entities. Must link to at least one entity. Supports backdating via occurred_at. |
get_note | Get a note by ID with all its links |
list_notes | List notes with filters by type, contact, outlet, opportunity, or full-text search |
update_note | Update note fields (title, content, type, occurred_at) |
add_note_link | Link an existing note to an additional entity |
remove_note_link | Remove a link between a note and an entity |
archive_note | Soft-delete a note |
restore_note | Restore an archived note |
Interaction Log
| Tool | Description |
|---|---|
get_interaction_log | Unified timeline of all interactions (notes, emails, coverage) for a contact, outlet, or opportunity. Supports date range filtering. |
Relationship Tools
| Tool | Description |
|---|---|
link_contact_outlet | Associate a contact with an outlet (with role, dates) |
unlink_contact_outlet | Remove association (sets end_date) |
link_opportunity_contact | Add a contact to an opportunity |
unlink_opportunity_contact | Remove contact from opportunity |
link_coverage | Link coverage to outlet/contact/channel/opportunity |
unlink_coverage | Remove a coverage link |
Opportunity Pipeline Tools
| Tool | Description |
|---|---|
advance_opportunity | Move to next stage |
set_opportunity_stage | Set to any stage |
list_opportunities_by_stage | Get all opportunities at a given stage |
get_opportunity_pipeline | Full pipeline view with counts per stage |
list_next_actions | List all open opportunities with next actions, grouped by owner |
Slack Tools
| Tool | Description |
|---|---|
send_slack_message | Send a notification to the team's Slack channel |
ask_slack | Post a question to Slack when the agent needs human input |
Search and Query Tools
| Tool | Description |
|---|---|
search | Full-text search across outlets, contacts, coverage, and notes |
recent_coverage | Coverage from last N days with filters |
contact_background | Full dossier on a contact |
outlet_background | Full dossier on an outlet |
opportunity_status | Detailed status of an opportunity with all linked entities |
campaign_summary | Overview of a campaign's opportunities and coverage |
Media Tools
| Tool | Description |
|---|---|
upload_media_url | Get a presigned upload URL for media |
attach_media | Record media metadata after upload |
get_media | Get media details and download URL |
list_media | List media for a coverage item |
Email Tools
| Tool | Description |
|---|---|
list_email_threads | List email threads for a contact |
get_email_thread | Get full thread with all messages |
search_emails | Full-text search across email bodies and subjects |
link_email_opportunity | Link an email thread to an opportunity |
unlink_email_opportunity | Remove email-opportunity link |
get_contact_emails | All email threads involving a specific contact |
trigger_email_sync | Manually trigger an email sync cycle |
get_email_sync_status | Check when the last sync ran |
Audit and Safety Tools
| Tool | Description |
|---|---|
get_change_log | View change history for a record or by agent/session |
rollback_change | Revert a specific change by log ID |
rollback_session | Revert all changes from an agent session |
Notification Tools
| Tool | Description |
|---|---|
subscribe_opportunity | Watch an opportunity for stage changes |
unsubscribe_opportunity | Stop watching |
list_subscriptions | See active watches |
Agent Key Management
| Tool | Description |
|---|---|
create_agent_key | Generate a new API key for an agent |
list_agent_keys | List active keys for the current user |
revoke_agent_key | Revoke an API key |
Media Storage
Supabase Storage Configuration
- Bucket:
pr-media(private) - Path convention:
{coverage_id}/{media_id}/{filename} - Max file size: 500MB (adjustable)
- Access: Presigned URLs with 1-hour expiry
Upload Flow
- Agent calls
upload_media_urlwith filename and MIME type - Server returns a presigned upload URL and a media record ID
- Agent (or user) uploads file to the presigned URL
- Agent calls
attach_mediawith metadata (duration, etc.) - Media record is linked to coverage
Notifications
Slack Integration
- Uses Slack Incoming Webhooks
- Configured via environment variable
SLACK_WEBHOOK_URL - Notifications triggered when opportunity stages change
Notification Events (V1)
| Event | Message |
|---|---|
| Opportunity stage change | "Opportunity '{title}' moved from {old_stage} to {new_stage} by {agent_type}" |
| Opportunity assigned | "Opportunity '{title}' assigned to {user}" |
| Agent needs input | Question posted via ask_slack when agent confidence is low |
Email Capture and Processing
Overview
Email is captured and processed through two complementary mechanisms:
- Ongoing capture: Cloudflare Email Routing receives emails in real-time when team members CC/BCC a system address.
- Historical backfill: Team members ask an agent to scan their existing inbox and update PR Tracker.
Deduplication
email_message.gmail_message_idhas a UNIQUE constraint- If two team members CC the system on the same thread, the message is only stored once
- Thread-level dedup uses
email_thread.gmail_thread_id(also UNIQUE) - The AI agent also deduplicates at the entity level
Seed Data
On initial deployment:
- Create "General" campaign (default)
- Create standard opportunity stages (enum)
- Create standard channel types (enum)
V1 Scope
In Scope
- Full data model with all tables and relationships
- MCP server with all CRUD, search, pipeline, and audit tools
- Google OAuth + agent API keys
- Media storage with presigned uploads
- Change log with rollback capability
- Slack notifications for opportunity changes
- Email capture via shared CC inbox with Gmail API cron sync
- Email threading, contact matching, and deduplication
- Email MCP tools (list, search, link to opportunities)
- Supabase CLI for schema management
- Wrangler CLI for MCP server deployment
Out of Scope (Future)
- Web UI
- Channel metrics population/scraping
- Automated media monitoring
- URL scraping for article capture
- Calculated metrics (reach, media value)
- Reporting dashboards
- Multiple email addresses per contact
- Email thread summarization
- Automatic opportunity linking from email threads
- Advanced permission restrictions per agent