Skip to main content

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 FieldOpen Navigator FieldNotes
event_datetimeevent_datetimeCombined date+time timestamp
body_refbody_nameMeeting body (e.g., "City Council")
agenda_uriagenda_urlLink to meeting agenda PDF
minutes_uriminutes_urlLink to meeting minutes PDF
external_source_idexternal_source_idCross-system tracking ID
N/Avideo_urlExtended field for YouTube/streaming

Session Model Mapping​

CDP FieldOpen Navigator FieldNotes
video_urivideo_urlVideo URL for the meeting
session_content_hashsession_content_hashHash for deduplication
session_datetimeevent_datetimeWhen the session occurred
event_refidReference to parent event

Body Model Mapping​

CDP FieldOpen Navigator FieldNotes
namebody_name"City Council", "Planning Commission"
descriptionbody_descriptionFull description of the body
external_source_idexternal_source_idSource system identifier

Transcript Model Mapping​

CDP FieldOpen Navigator FieldNotes
session_refevent_id (in events_text_ai)Links to session/event
file_refvideo_idReference to video file
confidencetranscript_qualityTranscript quality score
generatorai_modelAI 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​

📜 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}
}