I'm building an internal tool to monitor AI citations by topic, what data model should I use?

A good data model for monitoring AI citations by topic should separate:

1. the source content being cited,
2. the extracted citation events,
3. the topic taxonomy you want to analyze,
4. the AI system / prompt / model context that produced the citation.

That gives you flexibility to answer questions like:

• “Which topics get cited most?”
• “Which models cite which sources?”
• “Are citations increasing or decreasing over time?”
• “What sources are most reused across topics?”

Recommended core entities

1) Document

Represents an underlying source or reference.

Fields

• document_id (PK)
• title
• url
• publisher
• author
• published_at
• retrieved_at
• content_hash
• document_type (article, paper, webpage, internal doc, etc.)
• metadata (JSON)

2) Topic

Represents a controlled taxonomy.

Fields

• topic_id (PK)
• name
• slug
• parent_topic_id (nullable, for hierarchy)
• description
• status (active, deprecated)

If you need many-to-many topic tagging, keep it separate from documents and citations.

3) Citation

Represents a single citation event in an AI output.

Fields

• citation_id (PK)
• document_id (FK to Document)
• topic_id (FK to Topic, or use join table if multiple topics per citation)
• ai_response_id (FK to AIResponse)
• citation_text (the quoted or referenced text)
• citation_type (direct quote, paraphrase, link, attribution, inferred)
• confidence_score
• start_offset
• end_offset
• created_at

4) AIResponse

Represents the generated output containing the citation.

Fields

• ai_response_id (PK)
• request_id / prompt_id
• model_name
• model_version
• provider
• response_text
• generated_at
• latency_ms
• tokens_input
• tokens_output

5) Prompt

Useful if you want to analyze citation behavior by prompt structure.

Fields

• prompt_id (PK)
• prompt_text
• prompt_template_id
• topic_id (nullable)
• created_at

---

Better design: use a join table for topics

A citation may relate to multiple topics, and a document may belong to multiple topics. In that case, avoid a single topic_id on Citation.

CitationTopic

• citation_id (FK)
• topic_id (FK)
• assignment_method (manual, classifier, LLM, rule-based)
• confidence_score

DocumentTopic

• document_id (FK)
• topic_id (FK)
• assignment_method
• confidence_score

This is usually the most practical approach.

---

Suggested relational schema

Tables

• documents
• topics
• document_topics
• ai_responses
• citations
• citation_topics
• prompts optional
• prompt_runs or requests optional

Key relationships

• One AIResponse has many Citations
• One Document can appear in many Citations
• Many Citations can map to many Topics
• Many Documents can map to many Topics

---

If you want analytics-friendly reporting

Consider a star schema in addition to normalized storage:

Fact table

fact_citations

• citation_id
• document_id
• ai_response_id
• topic_id
• date_id
• model_id
• provider_id
• citation_type_id
• confidence_score
• count = 1

Dimensions

• dim_date
• dim_topic
• dim_document
• dim_model
• dim_provider
• dim_citation_type

This makes dashboards and BI queries much faster.

---

Important fields to add for monitoring

If your goal is monitoring rather than just cataloging, add:

• ingestion_source
• detected_at
• review_status (pending, approved, rejected)
• reviewed_by
• reviewed_at
• risk_level or policy_flag
• duplicate_of_citation_id for deduplication
• embedding_vector or vector ID if you do semantic clustering

---

Example simplified schema

documents (
  document_id UUID PK,
  title TEXT,
  url TEXT,
  publisher TEXT,
  published_at TIMESTAMP,
  content_hash TEXT
);

topics (
  topic_id UUID PK,
  name TEXT,
  parent_topic_id UUID NULL
);

ai_responses (
  ai_response_id UUID PK,
  model_name TEXT,
  model_version TEXT,
  prompt_text TEXT,
  response_text TEXT,
  generated_at TIMESTAMP
);

citations (
  citation_id UUID PK,
  ai_response_id UUID FK,
  document_id UUID FK,
  citation_text TEXT,
  citation_type TEXT,
  confidence_score FLOAT,
  created_at TIMESTAMP
);

citation_topics (
  citation_id UUID FK,
  topic_id UUID FK,
  assignment_method TEXT,
  confidence_score FLOAT,
  PRIMARY KEY (citation_id, topic_id)
);

---

Practical recommendation

If you’re starting from scratch, I’d use:

• PostgreSQL for the main system of record
• JSONB for flexible metadata
• Join tables for all many-to-many relationships
• Optional warehouse/star schema later for analytics
• Optional vector store if you’ll cluster or classify citations semantically

---

Best default choice

If you want one clean answer:
Use a normalized relational model with Document, Topic, AIResponse, and Citation as core tables, plus join tables like CitationTopic and DocumentTopic.

If you want, I can also give you:

1. a full ER diagram,
2. a PostgreSQL DDL script, or
3. a version optimized for analytics dashboards.