MariaDB Persistence & Dynamic Achievement System Plan

Comprehensive architecture and design blueprint for integrating a MariaDB Galera cluster backend, implementing dynamic achievement recalculation on round deletion, and introducing a two-phase provisional vs. finalized achievement tracking engine during live rounds.

User Review & Critical Decisions

Important

The architectural directions confirmed during Phase 1 have been integrated:

Database Backend: Configured for your existing MariaDB Galera Cluster via standard Node.js connection pooling with failover resilience, plus an automated schema migration runner and a dev-mode fallback when credentials are not yet set.

Round Deletion & Achievements: Deleting a saved round dynamically recalculates achievements across all remaining completed rounds, revoking badges if their qualification criteria are no longer met.

In-Round Instant Badges (e.g. Hole-in-One): Celebrated instantly with a live in-round toast upon entry, held as provisional during active scoring, revoked if the hole score is modified prior to saving, and permanently committed only upon confirmed round finalization.

1. Overview & Core Concept

What It Does

Pinpoint Golf evolves from a local session tracker into a production-grade multi-device golf companion backed by a dedicated MariaDB Galera cluster. It introduces:

MariaDB Persistence Layer: Secure user profiles, rounds history, hole-by-hole stats, and verified career achievements stored persistently in MariaDB tables with transactional integrity.
Dynamic Career Recalculation Engine: When a saved round is deleted, the backend re-runs the achievement rules against all remaining saved rounds, ensuring career records and trophies strictly reflect actual saved history.
Two-Phase Live Achievement Engine: High-impact milestones (like a Hole-in-One, Eagle, or Birdie Streak) trigger immediate in-round celebration banners. If a player corrects a typo before submitting, the provisional award is retracted. When the round is finalized with confirmation, all verified milestones are locked into the database.

Target Persona & Key Value

Avid Golfers: Enjoy real-time excitement during live scoring without fear of permanent false entries due to typing errors.
System Administrators / DevOps: Benefit from standard MariaDB Galera multi-master clustering support with zero lock-in and zero mock data in production.

2. User Experience & Visual Design

Live In-Round Achievement Workflow

[ Hole Entry Modal ]
        │  Enters score: "1" (Ace / Hole-in-One)
        ▼
[ Instant Celebration Banner ]
  • Dynamic toast: "🎉 Hole in One! (Provisional)"
  • Visual indicator on Hole card: "Provisional Ace"
        │
        ├── User corrects score to "3":
        │     └── Toast: "Provisional badge retracted" (Ace badge removed)
        │
        └── User finishes round & clicks "Save Round":
              └── SaveRoundModal confirmation
                    └── Achievements committed permanently to MariaDB

Key User Flows & Visual Hierarchy

Live Hole Entry & Provisional Badges:
- Scoring controls remain ergonomic for one-handed mobile thumb usage (44\text{px}+ touch targets).
- Instant achievement toasts use rich typography (Plus Jakarta Sans), subtle entrance transitions, and non-blocking dismissals.
- Distinct visual state for provisional badges (clean amber/gold indicator with a subtle "Pending Round Finalization" cue; no pill enclosures).
Save Confirmation Gate:
- The SaveRoundModal displays the round recap (gross score, net score to par, putts, FIR/GIR) and a list of new achievements earned this round.
- Clear warning: "Saving locks this round permanently into your official record. Editing will no longer be possible; only deletion is allowed."
Round Deletion & Dynamic Recalculation:
- In RoundsHistoryModal, each saved round card provides a "Delete Round" action with a two-step confirmation prompt.
- Deletion dialog warns: "Deleting this round will recalculate your career achievements. Badges earned solely from this round will be removed."
- Immediate feedback toast: "Round deleted. Achievements updated (X badges retained)."

3. Key Product Decisions & Trade-Offs

Decision 1: MariaDB Galera Cluster Integration

Approach: Configure a robust Express-based database adapter using mysql2/promise with Galera-ready connection pool settings (keep-alive, reconnection retry logic, multi-host support).
Environment Variables:
- MARIADB_HOST (supports single host or comma-separated cluster nodes)
- MARIADB_PORT (default 3306)
- MARIADB_USER, MARIADB_PASSWORD, MARIADB_DATABASE
- MARIADB_SSL (optional SSL mode)
Zero-Friction Fallback: When MARIADB_HOST is not yet configured in local environment, the server falls back to an integrated local SQLite/JSON storage engine with identical schema interfaces, ensuring the app remains fully functional while credentials are provided.

Decision 2: Achievement Recalculation Engine

Approach: Pure deterministic recalculation function calculateUserAchievements(savedRounds, courses).
Why: Storing achievement IDs as an append-only set risks "ghost achievements" where a deleted bogus round permanently leaves a Hole-in-One badge on the profile. By calculating career achievements dynamically upon deletion, data integrity is 100% mathematically sound.

Decision 3: Provisional In-Round vs. Finalized Achievements

Approach: Two distinct evaluation phases:
- Phase A (Provisional): Runs client-side during active round edits. Evaluates single-hole feats (Hole-in-One, Albatross, Eagle, 3-Birdie Streak). Displays celebration toasts. If score drops below qualification, badge is removed from provisional state.
- Phase B (Finalized): Runs server-side upon POST /api/rounds finalization. Evaluates both round-level feats (Sub-80, Clean Card, Centurion, Marathon) and verified hole-level feats, writing records transactionally to user_achievements.

4. Technical Architecture & Data Strategy

System Architecture & Data Flow

┌────────────────────────────────────────────────────────┐
│                      Client (SPA)                      │
│                                                        │
│  ┌──────────────────┐          ┌────────────────────┐  │
│  │   HoleEntry.tsx   │─────────▶│ Provisional State  │  │
│  │  (Live scoring)  │          │   (Active Round)   │  │
│  └──────────────────┘          └────────────────────┘  │
│            │                                           │
│            ▼                                           │
│  ┌──────────────────┐          ┌────────────────────┐  │
│  │  Scorecard.tsx   │─────────▶│ SaveRoundModal.tsx │  │
│  │  (Lock & Review) │          │  (Confirm Final)   │  │
│  └──────────────────┘          └────────────────────┘  │
└───────────────────────────────────────────┬────────────┘
                                            │ POST /api/rounds
                                            ▼
┌────────────────────────────────────────────────────────┐
│                   Server (Express API)                 │
│                                                        │
│  ┌──────────────────┐          ┌────────────────────┐  │
│  │  /api/rounds     │─────────▶│ Achievement Engine │  │
│  │  (Save / Delete) │          │  (Evaluate / Sync) │  │
│  └──────────────────┘          └────────────────────┘  │
│            │                                           │
│            ▼                                           │
│  ┌──────────────────────────────────────────────────┐  │
│  │        MariaDB Galera Pool (mysql2/promise)       │  │
│  └──────────────────────────────────────────────────┘  │
└───────────────────────────┬────────────────────────────┘
                            │
                            ▼
┌────────────────────────────────────────────────────────┐
│               MariaDB Cluster Database                 │
│                                                        │
│  • users (id, email, password_hash, handicap, created) │
│  • rounds (id, user_id, course_id, date, is_completed) │
│  • round_scores (round_id, hole_num, strokes, putts...)│
│  • user_achievements (user_id, achievement_id, date)   │
└────────────────────────────────────────────────────────┘

MariaDB Relational Schema

CREATE TABLE IF NOT EXISTS users (
  id VARCHAR(64) PRIMARY KEY,
  email VARCHAR(255) UNIQUE NOT NULL,
  display_name VARCHAR(128) NOT NULL,
  handicap_index DECIMAL(4,1) DEFAULT 18.0,
  password_hash VARCHAR(255) NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS rounds (
  id VARCHAR(64) PRIMARY KEY,
  user_id VARCHAR(64) NOT NULL,
  course_id VARCHAR(64) NOT NULL,
  tee_id VARCHAR(64),
  date VARCHAR(32) NOT NULL,
  holes_played INT NOT NULL DEFAULT 18,
  is_completed BOOLEAN NOT NULL DEFAULT 1,
  completed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  gross_score INT NOT NULL,
  net_score INT NOT NULL,
  total_putts INT NOT NULL,
  fir_hit INT NOT NULL DEFAULT 0,
  fir_total INT NOT NULL DEFAULT 0,
  gir_hit INT NOT NULL DEFAULT 0,
  gir_total INT NOT NULL DEFAULT 0,
  settings_json JSON,
  scores_json JSON NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_user_rounds (user_id, date DESC),
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS user_achievements (
  user_id VARCHAR(64) NOT NULL,
  achievement_id VARCHAR(64) NOT NULL,
  round_id VARCHAR(64),
  unlocked_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (user_id, achievement_id),
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

5. Implementation Roadmap (Post-Approval)

Backend Database Driver & Migration:
- Add mysql2 driver and create src/server/mariadb.ts with pool configuration and auto-table creation on startup.
- Configure .env.example with MariaDB connection parameters (MARIADB_HOST, MARIADB_PORT, MARIADB_USER, etc.).
- Support seamless fallback for development environments without an active cluster connection.
Provisional In-Round Achievement Engine:
- Update HoleEntry.tsx and round state to evaluate single-hole feats in real-time.
- Add animated toast notification for provisional unlocks and graceful retraction notifications if scores are adjusted.
Round Finalization & Recalculation Lifecycle:
- Wire SaveRoundModal.tsx to commit verified rounds to MariaDB and lock the round from future editing.
- Implement round deletion with automatic re-evaluation of user achievements, updating user_achievements in MariaDB and syncing client state.

12 KiB Raw Permalink Blame History