192 lines
12 KiB
Markdown
192 lines
12 KiB
Markdown
# 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:
|
|
1. **MariaDB Persistence Layer**: Secure user profiles, rounds history, hole-by-hole stats, and verified career achievements stored persistently in MariaDB tables with transactional integrity.
|
|
2. **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.
|
|
3. **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
|
|
1. **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).
|
|
2. **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."*
|
|
3. **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
|
|
```sql
|
|
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)
|
|
|
|
1. **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.
|
|
|
|
2. **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.
|
|
|
|
3. **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.
|