California racket-sports data platform: dbt + Snowflake + DuckDB, built as a lead data engineering portfolio project
저장소 README에서 가져옴
README
README 전체를 불러오는 중입니다. 아래는 저장된 발췌입니다.
Find where to play, who to play with, and where to watch, for every racket sport in California.
Open the live map: 9,935 venues across California, with an honest panel showing which of the 75 sports have no data yet, and a Watch a match mode for events you can go and see.
CourtConnect is a discovery tool: it helps you find where to play or watch, then sends you to the venue's or organizer's own site. It doesn't sell tickets or take bookings.
CourtConnect is a discovery tool for all racket sports: it finds where to play or watch, then sends you to the venue's or organizer's own site. It sells no tickets and takes no bookings or payments.
Tennis, padel, pickleball and squash and dozens of lesser-known and up-and-coming racket sports are booming, but information about where to play is scattered and messy. Court finders each cover one sport, or only places you can book. This project is one free map of all of them, from free park courts and hitting walls to private clubs. It collects the data, cleans it, checks it, and says honestly how complete it is.
Who it is for
Players: find a court, filter by sport, surface and access, then follow link-outs to open play, leagues and lessons.
Curious players: every sport on the master sheet is in one picker with an About card; sports with no place yet say so instead of faking pins.
Spectators: dated pro and amateur events, each with the organizer's own link.
Clubs and organizers: a free pin, an honest label, and a no-account way to fix or add a listing.
Researchers and builders: one public JSON file, a data dictionary and an llms.txt (below).
Live numbers (2026-10-23): 10,277 venues (CA 9,732, NV 545), 1,650 confirmed, 39 of 82 sports with at least one venue, 80 watch events, 21 social series, 923 hitting walls, 276 link-outs. By sport: docs/coverage_snapshot.md.
Venue map from OpenStreetMap, Overture Maps and hand-curated entries, with courts grouped into venues. Filter by sport, surface, indoor/outdoor, access, or search by name, city or ZIP; filters live in the URL.
Honest about confidence: pins show how much to trust each name and sport (confirmed, business listing, inferred), and a How complete is this data? panel lists every sport, including those with no data.
Play / Watch / Social views, plus a hitting-wall layer for solo practice (ADR 0015).
Link-outs to a venue's own pages, each with a verified_on date and evidence note; popups show rules and access as the venue wrote them.
Phone-first: bottom-sheet popup, Near me (location stays in the browser), dark mode, add to home screen; axe-core accessibility check passes in light and dark.
Market insights (inferred): which sports are missing near a city and how many rival venues are nearby (docs/market_insights.md).
Quality checks: dbt tests, a coverage report and a make quality scorecard with freshness SLAs (docs/data_quality.md).
What the data showed
No single source is enough. OSM knows where courts are, business listings know names; joining them is the product (27,493 OSM records plus Overture places became about 10,300 venues).
The map is mostly tennis. Gaps are the story for lesser-known sports, so the app shows them instead of hiding them (snapshot).
Duplicates inflated the count. Merging courts in one park and places at one address cut venues about 3%; a guard test now fails if two venues share a name within 150 m.
Most venues lacked a street address. Now 4,291 show one (OSM tags, nearest Overture address or Nominatim, the last two labelled inferred) and every pin has a city; homes show the city only, by design (ML plan).
Refreshing data does not fix a data gap; a different source does. Pickleball hides on tennis courts and racquetball in gyms; city park pages, chain scans and owner reports found them (gym scan).
Websites go bad. Links are checked and flagged (make check-websites), not trusted.
ML strategy: small models suggest, people decide
Rule: a model may rank or suggest; it never writes to the map. Anything inferred is labelled. Full plan: docs/ml_plan.md.
Review-queue ranker (built): logistic regression on 90 human labels orders uncertain sport matches (leave-one-out accuracy 86%, AUC 0.93).
LLM page reader (built, not run at scale): every extracted field needs an exact quote that code checks is on the page; waits on an API key, spend cap and precision bar (ADR 0017).
Aerial-photo pilot: finds courts but not sport or access, so it only leads people to fix OSM, not a data source.
Lesson: check what a model learned, not just its score. Skewed labels taught the ranker "Tennis means reject" until real clubs were confirmed.
CI on a fixture dataset, export freshness, scorecard no-regression
Scale (measured 2026-10-28, Overture 2026-10-21.0): the extracts query the 88M-place and 475M-address Overture parquet on S3 in place and fetch about 2% of those themes (743 MB of 39.6 GB); the data we keep is small. Method and caveats: planet-scale benchmark.
Suggest a change: a form or issue, a reviewer adds the approved label, a GitHub Action opens a PR that edits one seed row, CI gates it, then merge (ADR 0008).
Community reports: a Google Form (no sign-in) or a GitHub issue; the owner approves, then make sync-reports imports it as a seed edit (plan, CONTRIBUTING.md).
Scheduled refresh, five gated agent runs: on the 1st and 15th Claude Code scheduled tasks run Part 1 at 09:00 and Parts 2-5 at 10:30, 12:00, 13:30 and 15:00 (a scheduled run cannot start another task, so each part first checks that the previous one finished and skips itself if not). Each part is its own session (no subagents) with one job: (1) tennis, pickleball, ITF events, (2) badminton, racquetball, table tennis, squash, (3) padel calendar, beach tennis, padel lead rechecks, (4) Instagram sweep and player-group links, (5) the data refresh runbook with its quality gates. Parts 1-4 add to one tournament PR per cycle; Part 5 opens its own PR stacked on it. They read public pages only and record what they could not read. A merge gate (a script, not a model) merges the refresh PR by itself when it stays inside the R0 limits; tournament PRs are merged by a person, and re-date every event they re-confirm so the freshness SLA stays honest (steps, source runbook, refresh agent).
Agent-assisted, gate-checked: agents propose changes with evidence, automated gates verify them (dbt tests, CI, freshness, scorecard no-regression, diff cap, evidence required), and people still merge. The draft-mode refresh agent is the last link of the scheduled chain; a kind of change may auto-merge only after it has earned that and the owner switches it on (, , ).
Skills shown: SQL and dbt modelling with tests, entity resolution across messy sources, data-quality scorecards and freshness SLAs, CI/CD gates and GitHub Actions automation, small ML with leave-one-out validation, guard-railed LLM and agent workflows, written decision records (ADRs), and scoping work by cost and risk (privacy, terms of use, honesty).
For builders and AI tools
The map reads one public file, app/data/venues.json (about 2.4 MB, rebuilt about twice a month). Keys are single letters, so read the data dictionary first; app/llms.txt is the short guide for AI tools. Both are published next to the map. A read-only MCP server over the file is proposed, not built (ADR 0023).
Privacy and trust
No accounts, no cookies, no personal data. Anonymous visit counts use GoatCounter (cookie-free, honours Do Not Track, counts only named events, never what a visitor typed; ADR 0013).
Location stays in your browser ("Near me" is never sent anywhere). Phone numbers are never exported; homes show the city only.
Nothing is invented: inferred names, sports and addresses are labelled as inferred (ADR 0007).
Open data only: Overture and OSM, with attribution in the app (NOTICE.md).
Quick start
pip install -r requirements.txt
make demo # extract -> load -> build -> test (extract takes ~15 min)
make app-data # export the map data
make app # open http://localhost:8765
make build runs the dbt seeds, models and tests; it is the main check after any change to transform/. A new checkout without a database: make fresh-db (about a minute; loads the shared raw CSVs and runs the full build). On Windows, view_db.bat opens courtconnect.duckdb read-only in the DuckDB UI (close it before running dbt).
Editing the data
Data lives in seeds under transform/seeds/; edit them, then make build and make app-data.
To change
Edit
The list of sports
The Google Sheet, then make sync-sports (never hand-edit all_racquet.csv)
Add a venue no dataset knows
manual_venues.csv
Add a link-out (open play, lessons, ...)
venue_actions.csv
Add an event to watch
watch_events.csv
Confirm or reject an uncertain match
place_sport_overrides.csv
Mark an Overture place as closed
closed_places.csv (with evidence)
Correct a court count for one sport at a venue
venue_court_counts.csv (with evidence; add the missing sport via manual_venues.csv)
California + Nevada (ADR 0014): 545 Nevada venues live; resort and casino courts closed by the owner (2026-10-17)
Street addresses and a city on every pin (OSM tags, nearest Overture address, Nominatim, Census boundaries; inferred ones labelled; homes show city only)
Every sheet sport in the dropdown (81), with About cards and blurbs; sports with no place yet are greyed "(none yet)" with a report-a-venue link, never fake pins (docs/sport_discovery_plan.md)
Padel kept apart from pop tennis, every Padel pin audited, directories swept, "Opening soon" pins (docs/padel_scan.md)
Social phase A: find people to play with (player-matching app links, Watch & Social view with recurring open-play series); anything further, such as own accounts, only after a separate decision (ADR 0024)
Visual polish PRs 1-6 (brand tokens, shape and motion, sport icons, friendlier states, popup grouping and status band, glyph pins), front-door promise lines and About dialog, Near me, add to home screen, axe accessibility pass
Market insights v1, llms.txt and a data dictionary, extra analytics events, a draft-mode refresh agent
Phone and accessibility follow-ups: 44 px touch targets, a tick / "?" badge on zoomed pins so status does not rely on colour, Near me distances (nearest-first lists), better glyph and chip contrast, and a computed "(north courts, 6 courts)" suffix that tells apart venues sharing an inferred name
Next, in order (details in CLAUDE.md "Open, in order"):
Beta prep (owner): exclude your own visits in GoatCounter on each device, then send the invites
Beta feedback round with friends (December; docs/feedback_round.md), then rank what to build with the findings
Facility scans (UC and CSU campuses, military MWR) and club-named venues, one family at a time
Prove the scheduled chain end to end on a real 1st/15th run (five staggered crons, each gated on the previous part), then watch spend against the $20/month ceiling
Review queue (116 rows, parked: needs the page reader or community reports) and ML phase 2-3 (Claude page-reader run waits on an API key, a spend cap and a precision bar; the aerial pilot is not worth scaling, docs/ml_plan.md)
After the beta: cost tier, Watch events split on-site vs screening (ADR 0022), optional read-only MCP server (ADR 0023)
