Schema Planning vs Implementation — Discrepancy Report

Comparison between the planning spreadsheet (CSV) and the actual implementation (migrations/0001_init.up.sql, sql/*.sql, internal/db/models.go, handlers, repos).

Table-by-Table Analysis

users

# Planning Implementation Status
1 id SERIAL with note "change to uuid?" id SERIAL PRIMARY KEY (int32) ⚠️ UUID never adopted; all downstream code uses int32
2 email_verified BOOLEAN Column does not exist. Verification is inferred from email_verification_token IS NULL 🔴 Missing column
3 username defaults to ${first_name} ${last_name} username VARCHAR(50) UNIQUE NOT NULL DEFAULT '' ⚠️ Default is empty string, not computed from name
4 oauth_provider, oauth_id present Present in schema ✅
5 home_club_id INTEGER home_club_id INTEGER — no FK constraint to clubs(club_id) ⚠️ Orphaned reference
6 friend_count, round_count as stored integers Stored as integers ⚠️ Not computed/denormalized — will drift out of sync
7 password_hash VARCHAR(255) NOT NULL password_hash VARCHAR(255) NOT NULL — but CreateOAuthUser query does not set it ⚠️ OAuth users get NULL password_hash (violates NOT NULL in theory; works only because oauth query omits the column)

Auth SQL inconsistency: Two different user-creation queries exist:

clubs

# Planning Implementation Status
1 club_id BIGINT club_id BIGINT PRIMARY KEY ✅
2 All address/contact columns present All present, matching types ✅

No major discrepancies. Schema matches planning closely.

courses

# Planning Implementation Status
1 par(n), hcp(n) as generic notation Expanded into par1–par18, hcp1–hcp18 (individual columns) ✅ (expected expansion)
2 par_w(n), hcp_w(n) Expanded into par_w1–par_w18, hcp_w1–hcp_w18 ✅
3 match_index(n), split_index(n) Expanded into 18 columns each ✅
4 club_id BIGINT - REF clubs(club_id) FK constraint present ✅

No major discrepancies. The per-hole expansion is the correct implementation of the planning notation.

coordinates

# Planning Implementation Status
1 id SERIAL id SERIAL PRIMARY KEY ✅
2 All columns match All columns match ✅

No discrepancies.

tees

# Planning Implementation Status
1 length(n) as generic notation Expanded into length1–length18 ✅ (expected expansion)
2 tee_id BIGINT tee_id BIGINT PRIMARY KEY ✅
3 Women's slope/CR columns All present ✅

No major discrepancies.

rounds

# Planning Implementation Status
1 hasPrize ? — "ID (FK from prize table)" has_prize BIGINT with FK to prize_info(id) ✅
2 // permissions todo Implemented as 6 boolean flags: is_public, leaderboard_visible, number_of_players_visible, activity_tab_visible, activity_tab_visible_for_spectators, schedule_tab_visible ✅ (expanded)
3 round_id BIGINT round_id BIGINT PRIMARY KEY — no auto-generation (caller must supply) ⚠️ Handled in CreateRound params
4 idx_rounds_starttime Present ✅

No major discrepancies. Permissions were fleshed out beyond the planning.

scores

# Planning Implementation Status
1 id score_id BIGINT PRIMARY KEY — renamed ⚠️ Minor naming difference
2 ENUM (fairway_hit - LEFT, RIGHT, HIT, MISS) fairway_hit ENUM type created ✅
3 No created_at / updated_at mentioned Both timestamps present ✅ (added)
4 strokes, putts, penalty_strokes, etc. All present, matching types ✅

No major discrepancies.

game_players

# Planning Implementation Status
1 scoring_group ? (question mark = uncertain) scoring_group INTEGER NOT NULL — required ⚠️ Decided to make it mandatory
2 is_admin BOOLEAN is_admin BOOLEAN DEFAULT FALSE ✅
3 player_group INTEGER player_group INTEGER (nullable) ✅

No major discrepancies.

game_settings

# Planning Implementation Status
1 Setting1, setting2, .... with BOOL, BOOL Expanded into 30+ specific boolean/integer settings across categories (Scoring, Player, Leaderboard, Notifications) ✅ (fleshed out)
2 mode_id INTEGER mode_id INTEGER (nullable) ✅
3 No unique constraint on round_id No unique constraint — allows multiple settings rows per round ⚠️ Likely should be unique

Concern: game_settings has no UNIQUE (round_id) constraint. A round could accumulate multiple settings rows with no way to distinguish which is "current".

prize_info

# Planning Implementation Status
1 prize_settings JSONB Column does not exist 🔴 Missing column
2 id BIGINT id SERIAL (int32) ⚠️ Type mismatch (planning says BIGINT, impl uses SERIAL/int32)
3 round_id BIGINT FK Present with FK ✅

The prize_info table is essentially a stub — only id, round_id, created_at, updated_at. The planned prize_settings JSONB column was never added.

Proposed Tables — Missing from Implementation

The planning spreadsheet listed four "Proposed" tables. None have been implemented:

Table Purpose Status
tournaments Tournament management linked to rounds 🔴 Not implemented
challenges User challenges with JSONB settings 🔴 Not implemented
achievements User achievements with timestamps 🔴 Not implemented
notifications Push/in-app notifications per user 🔴 Not implemented

These represent significant planned features that are entirely absent from the codebase.

Cross-Cutting Concerns

1. No ON DELETE Behavior on Foreign Keys

None of the FK constraints specify ON DELETE CASCADE or ON DELETE SET NULL. Deleting a user, club, course, or round will fail if child records exist. This may be intentional (protective), but is worth noting.

2. ID Type Inconsistency

This mix is functional but means FK references from internal tables to external BIGINT IDs require careful type handling (Go models use int64 for course/club/round/tee IDs and int32 for user/internal IDs).

3. home_club_id Orphaned

users.home_club_id is an INTEGER with no FK constraint to clubs(club_id) (which is BIGINT). The types don't even match (int32 vs int64), making an FK impossible without altering the column.

4. Denormalized Counters

friend_count and round_count on the users table are stored integers. These will drift out of sync unless every friend/round mutation also updates the counter. Consider computed views or triggers.

5. Dead SQL Query

CreateUser in userQueries.sql inserts 15 columns but is never called from any handler or repo. The auth flow uses CreateUserWithVerification (7 columns) instead. CreateUser appears to be dead code.

6. OAuth Flow Incomplete

CreateOAuthUser and GetUserByOAuth queries exist in authQueries.sql, but there are no corresponding handler endpoints for OAuth signup/login. The AuthHandler only covers email/password signup, login, verify, and logout.

7. No Score/GamePlayer/GameSettings CRUD Endpoints

The schema defines scores, game_players, and game_settings tables, and the Go models exist, but there are no handlers, repos, or SQL queries for creating/updating/deleting scores, game players, or game settings. Rounds can be created but scores cannot be entered.

8. Course & Tee Data Loading

The data/ directory contains CSVs for clubs, courses, coordinates, and tees, but there is no data seeding/import endpoint or script in the codebase. The CreateClub, CreateCourse, CreateTee, CreateCoordinate SQL queries exist but are not wired to any handler.

Summary

Category Count
✅ Matches planning 18
⚠️ Minor discrepancy / concern 11
🔴 Missing feature / column 6
🔴 Entirely unimplemented tables 4

Highest priority items to address:

  1. prize_info.prize_settings (JSONB) column missing
  2. users.email_verified column missing (verification is implicit)
  3. Four proposed tables entirely absent (tournaments, challenges, achievements, notifications)
  4. No score/game player/game settings CRUD endpoints despite schema existing
  5. OAuth handler endpoints missing despite queries existing
  6. game_settings missing unique constraint on round_id