Democracy.cc's agenda filter system had hardcoded commissioner names in dropdown menus and a rigid 3-column database schema for sponsors (sponsor1, sponsor2, sponsor3). When commissioners changed or committee meetings had different sponsors, the filters broke.

The Migration

Replaced fixed columns with flexible JSON storage:

-- Before: rigid 3 columns
ALTER TABLE agenda_items DROP COLUMN sponsor1, DROP COLUMN sponsor2, DROP COLUMN sponsor3;

-- After: flexible JSON
ALTER TABLE agenda_items ADD COLUMN sponsors JSON;

Then built a filter extraction system that scans published agenda items and builds the dropdown options dynamically:

public function getAvailableFilters(string $boardType): array
{
    return Cache::remember("agenda_filters_{$boardType}", 120, function () use ($boardType) {
        $items = AgendaItem::where('board_type', $boardType)->get();

        return [
            'sponsors' => $items->pluck('sponsors')->flatten()->unique()->sort()->values(),
            'categories' => $items->pluck('category')->unique()->sort()->values(),
        ];
    });
}

The Parsing Challenge

Government document sponsor formats are inconsistent. A single item might list: "Commissioner Fernandez, Commissioner Suarez and Commissioner Dominguez" or "Co-sponsored by: Comm. Fernandez & Comm. Suarez." I built a parser that handles comma-separated, "and"-joined, "co-sponsored by" prefixed, and mixed-format entries, then normalizes names to a consistent format.

Performance

The original filter extraction queried every agenda item on every page load -- over 2 minutes on boards with 500+ items. With 2-minute caching, subsequent loads take 30ms. A 60x improvement for the common case, with fresh data never more than 2 minutes stale.

The filters now automatically adapt when new commissioners take office, new committee members appear, or new sponsor patterns show up in agenda documents. No code changes needed.