web_docs/docs/data-sources/council-data-project-compatibility.md

metadata

sidebar_position: 15

Council Data Project (CDP) Compatibility

Open Navigator's data model is designed to be compatible with the Council Data Project (CDP) backend schema, enabling interoperability with CDP instances deployed across cities nationwide.

🏛️ About Council Data Project

Council Data Project (CDP) is an open-source project that makes local government more accessible by providing infrastructure to index, archive, and make searchable city council meetings. CDP instances are deployed in cities across the United States.

Website: https://councildataproject.org/
Documentation: https://councildataproject.org/cdp-backend/
GitHub: https://github.com/CouncilDataProject/cdp-backend

🔗 Data Model Mapping

Our data model maps to CDP's core models while maintaining flexibility for additional data sources (YouTube, LocalView, Legistar, etc.):

Event Model Mapping

CDP Field	Open Navigator Field	Notes
event_datetime	event_datetime	Combined date+time timestamp
body_ref	body_name	Meeting body (e.g., "City Council")
agenda_uri	agenda_url	Link to meeting agenda PDF
minutes_uri	minutes_url	Link to meeting minutes PDF
external_source_id	external_source_id	Cross-system tracking ID
N/A	video_url	Extended field for YouTube/streaming

Session Model Mapping

CDP Field	Open Navigator Field	Notes
video_uri	video_url	Video URL for the meeting
session_content_hash	session_content_hash	Hash for deduplication
session_datetime	event_datetime	When the session occurred
event_ref	id	Reference to parent event

Body Model Mapping

CDP Field	Open Navigator Field	Notes
name	body_name	"City Council", "Planning Commission"
description	body_description	Full description of the body
external_source_id	external_source_id	Source system identifier

Transcript Model Mapping

CDP Field	Open Navigator Field	Notes
session_ref	event_id (in events_text_ai)	Links to session/event
file_ref	video_id	Reference to video file
confidence	transcript_quality	Transcript quality score
generator	ai_model	AI model used (Gemini, Whisper, etc.)

🗄️ Database Schema

Bronze Layer (open_navigator_bronze)

Our bronze tables include all CDP fields plus additional fields for YouTube and other sources:

CREATE TABLE bronze_event (
    -- CDP-compatible core fields
    event_datetime TIMESTAMP,
    body_name VARCHAR(200),
    body_description TEXT,
    agenda_url TEXT,              -- CDP: agenda_uri
    minutes_url TEXT,             -- CDP: minutes_uri
    external_source_id VARCHAR(255),
    
    -- Extended fields for multi-source ingestion
    video_url TEXT,               -- YouTube, Granicus, etc.
    channel_id VARCHAR(50),       -- YouTube channel tracking
    source VARCHAR(50),           -- 'youtube', 'localview', 'legistar'
    jurisdiction_name VARCHAR(200),
    state_code VARCHAR(2),
    -- ... additional fields
);

Production Layer (event)

The production event table (built via dbt) maintains CDP compatibility while providing a unified view across all data sources.

🔄 Data Flow

CDP Instance (e.g., Seattle) ──┐
YouTube (Municipal Channels) ──┼──► Bronze Layer ──► dbt Staging ──► dbt Marts ──► event (CDP-compatible)
LocalView Dataset ─────────────┤
Legistar API ──────────────────┘

🎯 Benefits of CDP Compatibility

1. Interoperability: Data can be shared with CDP instances in other cities
2. Standardization: Follows civic tech best practices for meeting data
3. Community: Leverage tools built by the CDP ecosystem
4. Federation: Potential to federate queries across CDP instances nationwide

📚 CDP Models Reference

Event

An event can be a normally scheduled meeting, a special event such as a press conference or election debate, and can be upcoming or historical.

Key Fields:

• event_datetime: When the event occurs
• body_ref: Reference to the meeting body
• agenda_uri: Link to the agenda
• minutes_uri: Link to the minutes

Body

A meeting body. This can be full council, a subcommittee, or "off-council" matters such as election debates.

Key Fields:

• name: Name of the body (e.g., "City Council")
• description: Full description
• is_active: Whether the body is currently active

Session

A session is a working period for an event. For example, an event could have a morning and afternoon session.

Key Fields:

• video_uri: Link to the video
• session_content_hash: Hash for deduplication
• session_datetime: When the session occurred

Transcript

A transcript is a document per-session.

Key Fields:

• session_ref: Reference to the session
• confidence: Transcript confidence score
• generator: What generated the transcript (e.g., "whisper-large-v3")

Matter

A matter is a specific legislative document (bill, resolution, initiative, etc.).

Key Fields:

• name: Matter identifier (e.g., "CB 120001")
• title: Full title
• matter_type: Type (bill, resolution, etc.)

Vote

A reference tying a specific person and an event minutes item together.

Key Fields:

• person_ref: Reference to the person voting
• event_minutes_item_ref: What they voted on
• decision: approve/reject/abstain

🔍 Querying CDP-Compatible Data

Get all City Council meetings in 2024

SELECT 
    event_datetime,
    body_name,
    title,
    agenda_url,
    minutes_url,
    video_url
FROM event
WHERE body_name = 'City Council'
  AND event_datetime BETWEEN '2024-01-01' AND '2024-12-31'
ORDER BY event_datetime DESC;

Find meetings with transcripts

SELECT 
    e.event_datetime,
    e.body_name,
    e.title,
    t.transcript_quality,
    t.ai_model
FROM event e
JOIN events_text_search t ON e.id = t.event_id
WHERE t.has_transcript = true
ORDER BY e.event_datetime DESC;

🤝 Contributing to CDP Ecosystem

Open Navigator extends CDP's vision by:

1. Multi-source ingestion: YouTube, LocalView, Legistar, Granicus
2. AI-powered transcription: Gemini, Whisper for automatic transcripts
3. Nationwide coverage: 90,000+ jurisdictions tracked
4. Policy analysis: AI models for policy opportunity detection

📖 Further Reading

• CDP Documentation
• CDP GitHub Repository
• CDP Database Models
• Open Navigator Data Sources
• Events Bronze Migration Guide

📜 Citation

When citing Council Data Project compatibility in Open Navigator:

@software{council_data_project,
  title = {Council Data Project: Indexing City Council Meetings},
  author = {{Council Data Project Team}},
  year = {2020},
  url = {https://councildataproject.org/},
  note = {Open-source infrastructure for making local government more accessible}
}