Skip to content

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

ComponentTechnologyPurpose
DatabaseSupabase PostgresAll application data, change log, RLS policies
AuthSupabase Auth + Google OAuthUser authentication, agent API keys
Media StorageSupabase StorageArticles, video clips, audio clips
MCP ServerCloudflare Worker (TypeScript)Agent-facing tool interface over SSE
Email SyncCloudflare Worker Cron Trigger + Gmail APIIngest emails from shared system inbox
NotificationsSlack WebhooksAlert users on opportunity changes
CLI AdminSupabase CLI, Wrangler CLISchema 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
                          EmailOpportunity

Interaction 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.

ColumnTypeDescription
iduuid (PK)
nametext NOT NULLOutlet name
descriptiontextAbout this outlet
websitetextPrimary website URL
tiersmallint1 (top-tier), 2, 3
geographic_reachtextlocal, regional, national, global
parent_companytextParent organization if applicable
industry_focustext[]Array of industry/topic tags
notestextFree-form notes
archived_attimestamptzSoft delete
created_attimestamptz
updated_attimestamptz

channel

A specific channel within an outlet. An outlet can have many channels.

ColumnTypeDescription
iduuid (PK)
outlet_iduuid FK
typechannel_type ENUMwebsite, newsletter, podcast, tv_broadcast, print, event, award
nametextChannel name (may differ from outlet name)
descriptiontext
urltextChannel-specific URL
frequencytextdaily, weekly, monthly, quarterly, annual
attributesjsonbChannel-type-specific metrics (see below)
archived_attimestamptzSoft delete
created_attimestamptz
updated_attimestamptz

Channel-type attribute schemas (stored in attributes JSONB, populated as available):

website:

json
{
  "monthly_unique_visitors": null,
  "domain_authority": null,
  "social_followers": null,
  "content_categories": [],
  "syndication_partners": []
}

newsletter:

json
{
  "subscriber_count": null,
  "open_rate": null,
  "click_through_rate": null,
  "paid_vs_free": null,
  "audience_segment": null
}

podcast:

json
{
  "downloads_per_episode": null,
  "total_subscribers": null,
  "platform_distribution": {},
  "average_episode_length_minutes": null,
  "format": null
}

tv_broadcast:

json
{
  "viewership": null,
  "market_rank": null,
  "time_slot": null,
  "segment_length_minutes": null,
  "live_vs_recorded": null
}

print:

json
{
  "circulation": null,
  "readership": null,
  "geographic_distribution": null,
  "ad_rate_cpm": null
}

event:

json
{
  "attendee_count": null,
  "attendee_seniority_mix": null,
  "industry_vertical": null,
  "sponsorship_tiers": [],
  "speaking_formats": []
}

award:

json
{
  "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.

ColumnTypeDescription
iduuid (PK)
first_nametext NOT NULL
last_nametext NOT NULL
emailtextPrimary email
phonetext
titletextJob title
biotext
is_freelanceboolean DEFAULT falseNot tied to a single outlet
social_linksjsonb{ "twitter": "", "linkedin": "", ... }
preferencesjsonbContact preferences, notes on best way to reach
notestext
archived_attimestamptzSoft delete
created_attimestamptz
updated_attimestamptz

contact_outlet

Many-to-many: contacts can work with multiple outlets, and outlets have multiple contacts. Tracks history (contacts move between outlets).

ColumnTypeDescription
iduuid (PK)
contact_iduuid FK
outlet_iduuid FK
roletextTheir role at this outlet
is_primaryboolean DEFAULT trueCurrent (true) or historical (false)
start_datedateWhen they started
end_datedateWhen they left (null = current)
created_attimestamptz

campaign

Groups related opportunities. Every opportunity belongs to a campaign.

ColumnTypeDescription
iduuid (PK)
nametext NOT NULL
descriptiontext
statustext DEFAULT 'active'active, paused, completed
start_datedate
end_datedate
archived_attimestamptzSoft delete
created_attimestamptz
updated_attimestamptz

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.

ColumnTypeDescription
iduuid (PK)
titletext NOT NULL
descriptiontext
campaign_iduuid FKDEFAULT to "General" campaign
channel_iduuid FKThe specific channel this targets (nullable)
outlet_iduuid FKThe outlet (nullable if channel is set)
stageopportunity_stage ENUMSee stages below
prioritytexthigh, medium, low
target_datedateWhen we're aiming for
notestext
owner_user_iduuid FKTeam member who owns this
next_actiontextNext concrete step needed
next_action_datedateWhen the next action is due
next_action_ownertextName of the team member who owns this action
archived_attimestamptzSoft delete
created_attimestamptz
updated_attimestamptz

Opportunity stages (enum opportunity_stage):

StageDescription
researchingIdentifying and evaluating the opportunity
draftingPreparing pitch or submission materials
pitchedOutreach sent
respondedReceived a reply (positive, negative, or follow-up)
in_progressActive dialogue, materials being exchanged
placedCoverage or participation confirmed
publishedCoverage is live / event completed
wonSuccessful outcome (award won, major placement)
lostDeclined, no response after follow-ups, or killed

opportunity_contact

Many-to-many: which contacts are involved in an opportunity.

ColumnTypeDescription
iduuid (PK)
opportunity_iduuid FK
contact_iduuid FK
roletextTheir role in this opportunity (e.g., "editor", "pitch target")
created_attimestamptz

coverage

A piece of coverage or result. Can be an article, TV segment, podcast appearance, award, event, etc.

ColumnTypeDescription
iduuid (PK)
titletext NOT NULLHeadline or title
descriptiontextSummary
urltextLink to original
published_datedate
coverage_typetextarticle, interview, review, opinion, listicle, roundup, segment, appearance, award_win, other
depthtextmention, quote, feature, dedicated, cover_story
sentimenttextpositive, neutral, negative, mixed
share_of_voicetextsole_focus, shared, competitor_included
spokesperson_quotedbooleanWhether our spokesperson was quoted
initiated_bytextpitched, organic
full_texttextFull text copy of the article/transcript
notestext
archived_attimestamptzSoft delete
created_attimestamptz
updated_attimestamptz

coverage_outlet

Links coverage to outlets, contacts, channels, and opportunities. All links are optional.

ColumnTypeDescription
iduuid (PK)
coverage_iduuid FK
outlet_iduuid FKNullable
contact_iduuid FKNullable
channel_iduuid FKNullable
opportunity_iduuid FKNullable
created_attimestamptz

media

Media files attached to coverage. Stored in Supabase Storage.

ColumnTypeDescription
iduuid (PK)
coverage_iduuid FK
typetextarticle_pdf, video, video_clip, audio, audio_clip, image, document
filenametextOriginal filename
storage_pathtextPath in Supabase Storage bucket
mime_typetext
size_bytesbigint
duration_secondsintegerFor audio/video
metadatajsonbAdditional metadata
archived_attimestamptzSoft delete
created_attimestamptz

email_thread

A Gmail thread containing one or more messages.

ColumnTypeDescription
iduuid (PK)
gmail_thread_idtext UNIQUE NOT NULLGmail's thread ID (dedup key)
subjecttextThread subject line
last_message_attimestamptzTimestamp of most recent message
message_countinteger DEFAULT 0Number of messages in thread
archived_attimestamptzSoft delete
created_attimestamptz
updated_attimestamptz

email_message

An individual email message within a thread. Deduped by Gmail message ID.

ColumnTypeDescription
iduuid (PK)
thread_iduuid FK
gmail_message_idtext UNIQUE NOT NULLGmail's message ID (dedup key)
from_addresstext NOT NULLSender email
from_nametextSender display name
to_addressesjsonbArray of { email, name }
cc_addressesjsonbArray of { email, name }
subjecttextMessage subject
body_texttextPlain text body
body_htmltextHTML body
snippettextGmail's auto-generated snippet
sent_attimestamptz NOT NULLWhen the email was sent
gmail_labelstext[]Gmail labels at time of sync
raw_headersjsonbSelect headers (In-Reply-To, References, etc.)
created_attimestamptzWhen we ingested this message

email_contact

Links email messages to known contacts.

ColumnTypeDescription
iduuid (PK)
email_message_iduuid FK
contact_iduuid FK
roletext NOT NULLfrom, to, cc
created_attimestamptz

Unique constraint: (email_message_id, contact_id, role)

email_opportunity

Optional link between an email thread and an opportunity.

ColumnTypeDescription
iduuid (PK)
thread_iduuid FK
opportunity_iduuid FK
created_attimestamptz

Unique constraint: (thread_id, opportunity_id)

email_sync_state

Tracks the sync cursor for the system Gmail inbox.

ColumnTypeDescription
iduuid (PK)
gmail_history_idtextGmail history ID for incremental sync
last_sync_attimestamptzWhen the last successful sync completed
emails_synced_totalbigint DEFAULT 0Running count of messages synced
updated_attimestamptz

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.

ColumnTypeDescription
iduuid (PK)
typenote_type ENUMgeneral, meeting, phone_call, event, research, other
titletextShort title or subject (optional)
contenttextNote body (optional)
occurred_attimestamptz DEFAULT now()When this happened (supports backdating)
archived_attimestamptzSoft delete
created_attimestamptzWhen the note was recorded
updated_attimestamptzLast modified

Links notes to contacts, outlets, opportunities, and/or channels. A single note can have multiple links.

ColumnTypeDescription
iduuid (PK)
note_iduuid FK
contact_iduuid FKNullable
outlet_iduuid FKNullable
opportunity_iduuid FKNullable
channel_iduuid FKNullable
created_attimestamptz

Non-Destructive Operations: Change Log

All mutations are tracked automatically via Postgres triggers. No data is permanently deleted through the application layer.

change_log

ColumnTypeDescription
idbigserial (PK)
table_nametext NOT NULLWhich table was changed
record_iduuid NOT NULLPK of the changed record
actiontext NOT NULLinsert, update, archive, restore
old_valuesjsonbPrevious state (null for inserts)
new_valuesjsonbNew state (null for archives)
changed_fieldstext[]List of fields that changed (for updates)
user_iduuidWhich user's session
agent_idtextWhich agent made the change
agent_sessiontextSession identifier for grouping related changes
created_attimestamptz DEFAULT now()

Postgres trigger (applied to all tables)

sql
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:

  1. Looks up the change_log entry by ID
  2. Applies the old_values back to the record
  3. 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_key table:
ColumnTypeDescription
iduuid (PK)
user_iduuid FKOwner
nametextHuman label (e.g., "Claude Cowork - Desktop")
key_hashtextHashed API key
agent_typetextclaude_cowork, claude_code, openclaw, other
permissionsjsonbOptional permission restrictions
last_used_attimestamptz
revoked_attimestamptzSoft revoke
created_attimestamptz

MCP Auth Flow

  1. User authenticates via Google OAuth in a one-time setup
  2. User creates an API key via a CLI command or MCP tool
  3. Agent connects to MCP server with Authorization: Bearer <api_key>
  4. MCP server validates key, sets app.user_id and app.agent_id on the Postgres session
  5. 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:

ToolDescription
create_outletCreate a new outlet
get_outletGet outlet by ID with its channels and contacts
list_outletsList/search outlets with filters
update_outletUpdate outlet fields
archive_outletSoft-delete an outlet
restore_outletRestore an archived outlet

Same pattern for: contact, channel, campaign, opportunity, coverage, media, note.

Note Tools

ToolDescription
create_noteCreate a note with links to entities. Must link to at least one entity. Supports backdating via occurred_at.
get_noteGet a note by ID with all its links
list_notesList notes with filters by type, contact, outlet, opportunity, or full-text search
update_noteUpdate note fields (title, content, type, occurred_at)
add_note_linkLink an existing note to an additional entity
remove_note_linkRemove a link between a note and an entity
archive_noteSoft-delete a note
restore_noteRestore an archived note

Interaction Log

ToolDescription
get_interaction_logUnified timeline of all interactions (notes, emails, coverage) for a contact, outlet, or opportunity. Supports date range filtering.

Relationship Tools

ToolDescription
link_contact_outletAssociate a contact with an outlet (with role, dates)
unlink_contact_outletRemove association (sets end_date)
link_opportunity_contactAdd a contact to an opportunity
unlink_opportunity_contactRemove contact from opportunity
link_coverageLink coverage to outlet/contact/channel/opportunity
unlink_coverageRemove a coverage link

Opportunity Pipeline Tools

ToolDescription
advance_opportunityMove to next stage
set_opportunity_stageSet to any stage
list_opportunities_by_stageGet all opportunities at a given stage
get_opportunity_pipelineFull pipeline view with counts per stage
list_next_actionsList all open opportunities with next actions, grouped by owner

Slack Tools

ToolDescription
send_slack_messageSend a notification to the team's Slack channel
ask_slackPost a question to Slack when the agent needs human input

Search and Query Tools

ToolDescription
searchFull-text search across outlets, contacts, coverage, and notes
recent_coverageCoverage from last N days with filters
contact_backgroundFull dossier on a contact
outlet_backgroundFull dossier on an outlet
opportunity_statusDetailed status of an opportunity with all linked entities
campaign_summaryOverview of a campaign's opportunities and coverage

Media Tools

ToolDescription
upload_media_urlGet a presigned upload URL for media
attach_mediaRecord media metadata after upload
get_mediaGet media details and download URL
list_mediaList media for a coverage item

Email Tools

ToolDescription
list_email_threadsList email threads for a contact
get_email_threadGet full thread with all messages
search_emailsFull-text search across email bodies and subjects
link_email_opportunityLink an email thread to an opportunity
unlink_email_opportunityRemove email-opportunity link
get_contact_emailsAll email threads involving a specific contact
trigger_email_syncManually trigger an email sync cycle
get_email_sync_statusCheck when the last sync ran

Audit and Safety Tools

ToolDescription
get_change_logView change history for a record or by agent/session
rollback_changeRevert a specific change by log ID
rollback_sessionRevert all changes from an agent session

Notification Tools

ToolDescription
subscribe_opportunityWatch an opportunity for stage changes
unsubscribe_opportunityStop watching
list_subscriptionsSee active watches

Agent Key Management

ToolDescription
create_agent_keyGenerate a new API key for an agent
list_agent_keysList active keys for the current user
revoke_agent_keyRevoke 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

  1. Agent calls upload_media_url with filename and MIME type
  2. Server returns a presigned upload URL and a media record ID
  3. Agent (or user) uploads file to the presigned URL
  4. Agent calls attach_media with metadata (duration, etc.)
  5. 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)

EventMessage
Opportunity stage change"Opportunity '{title}' moved from {old_stage} to {new_stage} by {agent_type}"
Opportunity assigned"Opportunity '{title}' assigned to {user}"
Agent needs inputQuestion posted via ask_slack when agent confidence is low

Email Capture and Processing

Overview

Email is captured and processed through two complementary mechanisms:

  1. Ongoing capture: Cloudflare Email Routing receives emails in real-time when team members CC/BCC a system address.
  2. Historical backfill: Team members ask an agent to scan their existing inbox and update PR Tracker.

Deduplication

  • email_message.gmail_message_id has 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