Read VGMQ_PATH from env, create handler, init playlist in goroutine
6. HTTP Handlers
File: internal/server/vgmq_handler.go
VGMQResponse type with all fields
12 handlers for all endpoints
7. Routes
File: internal/server/routes.go
All /api/v1/vgmq/* protected by tokenAuthMiddleware
# VGMQ Full Implementation with Playlist and File Synchronization
**Status**: Not Started
**Priority**: High
**Estimate**: 8-12 hours
---
## Summary
Implement a complete VGMQ (Video Game Music Quiz) system with:
- Database table for VGMQ entries (migrated from existing vgmq table)
- CSV data import with Swedish-to-English column mapping
- File synchronization between CSV data and Kan Ej/ folder
- In-memory playlist management for unknown entries
- Current song tracking and metadata editing
- MP3 file streaming endpoint
- REST API endpoints under /api/v1/vgmq/ with token authentication
---
## Requirements
### 1. Environment Configuration
Add to .env: `VGMQ_PATH=/Users/sebastian/ResilioSync/VGMQ_Test/`
Add to .env.test: `VGMQ_PATH=/Users/sebastian/projects/MusicServer/testVGMQ/`
### 2. Database Schema
Migration: `internal/db/migrations/YYYYMMDDHHMMSS_create_new_vgmq.sql`
```sql
DROP TABLE IF EXISTS vgmq;
DROP SEQUENCE IF EXISTS vgmq_song_no_seq;
CREATE TABLE vgmq (
song_number INTEGER PRIMARY KEY,
game_title VARCHAR(255),
song_title VARCHAR(255),
console VARCHAR(100),
link VARCHAR(500),
guess_info TEXT,
is_unknown BOOLEAN DEFAULT false,
file_path VARCHAR(500),
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);
CREATE INDEX idx_vgmq_game_title ON vgmq(game_title);
CREATE INDEX idx_vgmq_console ON vgmq(console);
CREATE INDEX idx_vgmq_is_unknown ON vgmq(is_unknown);
CREATE INDEX idx_vgmq_song_number ON vgmq(song_number);
```
### 3. SQL Queries
File: `internal/db/queries/vgmq.sql`
- CreateVgmq, GetVgmqBySongNumber, ListAllVgmq, ListUnknownVgmq
- UpdateVgmq, DeleteVgmq, DeleteAllVgmq, GetVgmqCount, SearchVgmq
- GetUnknownVgmqCount
Update sqlc.yaml: Add `vgmq` to tables list, run `just sqlc-generate`
### 4. Backend Implementation
File: `internal/backend/vgmq.go`
Types: VGMQ, VGMQCSV, VGMQHandler
Global: vgmqPlaylist, currentVGMQIndex, playlistMutex
Functions: NewVGMQHandler, readCSV, syncFilesWithCSV, SyncFromCSV, GetAllVGMQ, GetVGMQBySongNumber, GetUnknownVGMQ, SearchVGMQ, InitVGMQPlaylist, GetCurrentVGMQ, GetFirstUnknownVGMQ, GetNextUnknownVGMQ, GetPreviousUnknownVGMQ, GetRandomUnknownVGMQ, GetVGMQByNumber, UpdateCurrentVGMQ, GetVGMQPlaylist, GetVGMQCurrentInfo
### 5. Server Integration
File: `internal/server/server.go`
- Add vgmqHandler to Server struct
- Read VGMQ_PATH from env, create handler, init playlist in goroutine
### 6. HTTP Handlers
File: `internal/server/vgmq_handler.go`
- VGMQResponse type with all fields
- 12 handlers for all endpoints
### 7. Routes
File: `internal/server/routes.go`
All /api/v1/vgmq/* protected by tokenAuthMiddleware
Status check (2026-10-09): nothing of this is built yet. The vgmq table from migration 000001 is still the old one, internal/db/queries/vgmq.sql is empty, and there is no testVGMQ/ folder. The plan above still holds, but the server has been restructured since it was written, so it needs these changes:
Migration. Use the next number, 000007_create_new_vgmq.up/down.sql, not a timestamp name. 000006 is still undeployed, so the vgmq table could also be folded into it, like #28 was folded into 000005.
Config. Add VGMQPath to internal/config/config.go (VGMQ_PATH), read in load() with the other paths. When it is empty, VGMQ is off and its routes are not registered.
Service.
A VGMQService in internal/backend/vgmq.go, made with the repository.Querier like SyncService and ReleaseService.
The playlist and current index should be fields on the service with a mutex, not package globals.
Routes.
A new internal/server/vgmq_handler.go with registerVGMQ(a), called next to registerSync/registerMusic in routes.go.
Each endpoint uses register(s, a, route{op: ...}) (Huma), so it appears in openapi.json.
The token middleware covers /api/v1 already.
Errors use the shared {"error": "..."} format.
CSV import.
The real sheet (VGMQ_Test/VGMQ! - Sheet1.csv) has a header row with columns Spel, Låttitel, Konsol, Länk, Gissning/info and an empty first column holding the song number.
The second row is a note, not a song ("Senaste kända spelet är från November 17, 2008."), so rows without a number must be skipped.
Where do edits go? Should edits to an entry (game, song, guess) be written back to the CSV, or does the database become the source of truth after the first import?
Kan Ej. When an unknown song gets identified, should the server move its file out of Kan Ej/?
Status check (2026-10-09): nothing of this is built yet. The `vgmq` table from migration 000001 is still the old one, `internal/db/queries/vgmq.sql` is empty, and there is no `testVGMQ/` folder. The plan above still holds, but the server has been restructured since it was written, so it needs these changes:
- **Migration.** Use the next number, `000007_create_new_vgmq.up/down.sql`, not a timestamp name. 000006 is still undeployed, so the vgmq table could also be folded into it, like #28 was folded into 000005.
- **Config.** Add `VGMQPath` to `internal/config/config.go` (`VGMQ_PATH`), read in `load()` with the other paths. When it is empty, VGMQ is off and its routes are not registered.
- **Service.**
- A `VGMQService` in `internal/backend/vgmq.go`, made with the `repository.Querier` like `SyncService` and `ReleaseService`.
- The playlist and current index should be fields on the service with a mutex, not package globals.
- **Routes.**
- A new `internal/server/vgmq_handler.go` with `registerVGMQ(a)`, called next to `registerSync`/`registerMusic` in `routes.go`.
- Each endpoint uses `register(s, a, route{op: ...})` (Huma), so it appears in `openapi.json`.
- The token middleware covers `/api/v1` already.
- Errors use the shared `{"error": "..."}` format.
- **CSV import.**
- The real sheet (`VGMQ_Test/VGMQ! - Sheet1.csv`) has a header row with columns Spel, Låttitel, Konsol, Länk, Gissning/info and an empty first column holding the song number.
- The second row is a note, not a song ("Senaste kända spelet är från November 17, 2008."), so rows without a number must be skipped.
- Mapping: Spel→game_title, Låttitel→song_title, Konsol→console, Länk→link, Gissning/info→guess_info.
- **Files.**
- Songs are `<number>.mp3` in `VGMQ_PATH`; unknown ones are in `VGMQ_PATH/Kan Ej/`.
- Streaming should reuse `streamOpenFile` from `files.go` (Range support, already used for songs).
- **Testing.** Use small test data, a few mp3s plus a trimmed CSV, in `testVGMQ/`, and integration tests like `sync_integration_test.go`.
## Questions
- **Which client uses this?** None of the issues says where the VGMQ playlist is played and edited. Options:
- a page in MusicFrontend (Sansan/MusicFrontend#4);
- a templ page in the server like `/search`;
- a mode in MusicPlayer.
- **Where do edits go?** Should edits to an entry (game, song, guess) be written back to the CSV, or does the database become the source of truth after the first import?
- **Kan Ej.** When an unknown song gets identified, should the server move its file out of `Kan Ej/`?
Data source: the Google Sheet instead of a CSV export
The VGMQ list lives in a Google Sheet. The server reads it and writes to it directly through the Google Sheets API, replacing the CSV import in sections 1 and 4 above.
Sheets
Production uses the real sheet. Only the production server's service account is shared on it.
Test and development use a copy (File → Make a copy). The copy is shared with a separate test service account, so a local or test server can never touch the real sheet.
Integration tests in CI don't call Google. They use a fake behind a small interface (below).
The sheet IDs are not written in the repo: this repo is public. They are set as environment variables per deployment.
Configuration
Replaces VGMQ_PATH-only:
VGMQ_SHEET_ID: the ID from the sheet's URL (/spreadsheets/d/<id>/edit).
VGMQ_SHEET_RANGE: e.g. Sheet1!A:F (default).
GOOGLE_CREDENTIALS_FILE: path to the service account's JSON key, mounted as a secret in compose.yaml and never committed.
VGMQ_PATH stays for the mp3 files.
Without VGMQ_SHEET_ID, VGMQ is off.
Setting up the service account (once)
Create a project in Google Cloud Console and enable the Google Sheets API.
Create two service accounts, vgmq-prod and vgmq-test, and download a JSON key for each.
Share the real sheet with vgmq-prod@… and the copy with vgmq-test@…, both as Editor.
Server design
A VGMQSheet interface with ReadRows(ctx) ([]VGMQRow, error) and UpdateRow(ctx, songNumber, fields) error.
The real one uses google.golang.org/api/sheets/v4 with option.WithCredentialsFile.
The tests use an in-memory fake.
Reading.
Read the whole range.
Rows are identified by the song number in column A; rows without a number are skipped, e.g. the note row "Senaste kända spelet…".
Headers are matched by name (Spel, Låttitel, Konsol, Länk, Gissning/info), not by position, so moving a column doesn't break it.
The database is a cache of the sheet.
It is reloaded at startup, every few minutes, and from a new endpoint POST /api/v1/vgmq/reload.
Search, the unknown list and the playlist read from the database.
Writing.
Saving an entry first re-reads column A to find the song's current row, since others may have inserted or sorted rows.
Then it updates only that row's cells (spreadsheets.values.update on e.g. B57:F57), never the whole sheet, so other people's edits aren't overwritten.
Afterwards the database copy is updated.
Rate limits. About 60 writes per minute per account is far above what this needs. On HTTP 429, retry with backoff.
Unknown flag.is_unknown is not a column in the sheet today: it comes from the file being in Kan Ej/. See the question below.
Questions
When a song is identified, should the server also move its mp3 out of Kan Ej/, so the folder and the sheet agree?
Should "unknown" be its own column in the sheet, or keep being decided by the folder?
## Data source: the Google Sheet instead of a CSV export
The VGMQ list lives in a Google Sheet. The server reads it and writes to it directly through the Google Sheets API, replacing the CSV import in sections 1 and 4 above.
### Sheets
- **Production** uses the real sheet. Only the production server's service account is shared on it.
- **Test and development** use a copy (File → Make a copy). The copy is shared with a separate test service account, so a local or test server can never touch the real sheet.
- **Integration tests** in CI don't call Google. They use a fake behind a small interface (below).
The sheet IDs are not written in the repo: this repo is public. They are set as environment variables per deployment.
### Configuration
Replaces `VGMQ_PATH`-only:
- `VGMQ_SHEET_ID`: the ID from the sheet's URL (`/spreadsheets/d/<id>/edit`).
- `VGMQ_SHEET_RANGE`: e.g. `Sheet1!A:F` (default).
- `GOOGLE_CREDENTIALS_FILE`: path to the service account's JSON key, mounted as a secret in `compose.yaml` and never committed.
- `VGMQ_PATH` stays for the mp3 files.
Without `VGMQ_SHEET_ID`, VGMQ is off.
### Setting up the service account (once)
1. Create a project in Google Cloud Console and enable the **Google Sheets API**.
2. Create two service accounts, `vgmq-prod` and `vgmq-test`, and download a JSON key for each.
3. Share the real sheet with `vgmq-prod@…` and the copy with `vgmq-test@…`, both as Editor.
### Server design
- A `VGMQSheet` interface with `ReadRows(ctx) ([]VGMQRow, error)` and `UpdateRow(ctx, songNumber, fields) error`.
- The real one uses `google.golang.org/api/sheets/v4` with `option.WithCredentialsFile`.
- The tests use an in-memory fake.
- **Reading.**
- Read the whole range.
- Rows are identified by the song number in column A; rows without a number are skipped, e.g. the note row "Senaste kända spelet…".
- Headers are matched by name (Spel, Låttitel, Konsol, Länk, Gissning/info), not by position, so moving a column doesn't break it.
- **The database is a cache of the sheet.**
- It is reloaded at startup, every few minutes, and from a new endpoint `POST /api/v1/vgmq/reload`.
- Search, the unknown list and the playlist read from the database.
- **Writing.**
- Saving an entry first re-reads column A to find the song's current row, since others may have inserted or sorted rows.
- Then it updates only that row's cells (`spreadsheets.values.update` on e.g. `B57:F57`), never the whole sheet, so other people's edits aren't overwritten.
- Afterwards the database copy is updated.
- **Rate limits.** About 60 writes per minute per account is far above what this needs. On HTTP 429, retry with backoff.
- **Unknown flag.** `is_unknown` is not a column in the sheet today: it comes from the file being in `Kan Ej/`. See the question below.
### Questions
- When a song is identified, should the server also move its mp3 out of `Kan Ej/`, so the folder and the sheet agree?
- Should "unknown" be its own column in the sheet, or keep being decided by the folder?
Blocking a user prevents them from interacting with repositories, such as opening or commenting on pull requests or issues. Learn more about blocking a user.
VGMQ Full Implementation with Playlist and File Synchronization
Status: Not Started
Priority: High
Estimate: 8-12 hours
Summary
Implement a complete VGMQ (Video Game Music Quiz) system with:
Requirements
1. Environment Configuration
Add to .env:
VGMQ_PATH=/Users/sebastian/ResilioSync/VGMQ_Test/Add to .env.test:
VGMQ_PATH=/Users/sebastian/projects/MusicServer/testVGMQ/2. Database Schema
Migration:
internal/db/migrations/YYYYMMDDHHMMSS_create_new_vgmq.sql3. SQL Queries
File:
internal/db/queries/vgmq.sqlUpdate sqlc.yaml: Add
vgmqto tables list, runjust sqlc-generate4. Backend Implementation
File:
internal/backend/vgmq.goTypes: VGMQ, VGMQCSV, VGMQHandler
Global: vgmqPlaylist, currentVGMQIndex, playlistMutex
Functions: NewVGMQHandler, readCSV, syncFilesWithCSV, SyncFromCSV, GetAllVGMQ, GetVGMQBySongNumber, GetUnknownVGMQ, SearchVGMQ, InitVGMQPlaylist, GetCurrentVGMQ, GetFirstUnknownVGMQ, GetNextUnknownVGMQ, GetPreviousUnknownVGMQ, GetRandomUnknownVGMQ, GetVGMQByNumber, UpdateCurrentVGMQ, GetVGMQPlaylist, GetVGMQCurrentInfo
5. Server Integration
File:
internal/server/server.go6. HTTP Handlers
File:
internal/server/vgmq_handler.go7. Routes
File:
internal/server/routes.goAll /api/v1/vgmq/* protected by tokenAuthMiddleware
Status check (2026-10-09): nothing of this is built yet. The
vgmqtable from migration 000001 is still the old one,internal/db/queries/vgmq.sqlis empty, and there is notestVGMQ/folder. The plan above still holds, but the server has been restructured since it was written, so it needs these changes:000007_create_new_vgmq.up/down.sql, not a timestamp name. 000006 is still undeployed, so the vgmq table could also be folded into it, like #28 was folded into 000005.VGMQPathtointernal/config/config.go(VGMQ_PATH), read inload()with the other paths. When it is empty, VGMQ is off and its routes are not registered.VGMQServiceininternal/backend/vgmq.go, made with therepository.QuerierlikeSyncServiceandReleaseService.internal/server/vgmq_handler.gowithregisterVGMQ(a), called next toregisterSync/registerMusicinroutes.go.register(s, a, route{op: ...})(Huma), so it appears inopenapi.json./api/v1already.{"error": "..."}format.VGMQ_Test/VGMQ! - Sheet1.csv) has a header row with columns Spel, Låttitel, Konsol, Länk, Gissning/info and an empty first column holding the song number.<number>.mp3inVGMQ_PATH; unknown ones are inVGMQ_PATH/Kan Ej/.streamOpenFilefromfiles.go(Range support, already used for songs).testVGMQ/, and integration tests likesync_integration_test.go.Questions
/search;Kan Ej/?Data source: the Google Sheet instead of a CSV export
The VGMQ list lives in a Google Sheet. The server reads it and writes to it directly through the Google Sheets API, replacing the CSV import in sections 1 and 4 above.
Sheets
The sheet IDs are not written in the repo: this repo is public. They are set as environment variables per deployment.
Configuration
Replaces
VGMQ_PATH-only:VGMQ_SHEET_ID: the ID from the sheet's URL (/spreadsheets/d/<id>/edit).VGMQ_SHEET_RANGE: e.g.Sheet1!A:F(default).GOOGLE_CREDENTIALS_FILE: path to the service account's JSON key, mounted as a secret incompose.yamland never committed.VGMQ_PATHstays for the mp3 files.Without
VGMQ_SHEET_ID, VGMQ is off.Setting up the service account (once)
vgmq-prodandvgmq-test, and download a JSON key for each.vgmq-prod@…and the copy withvgmq-test@…, both as Editor.Server design
VGMQSheetinterface withReadRows(ctx) ([]VGMQRow, error)andUpdateRow(ctx, songNumber, fields) error.google.golang.org/api/sheets/v4withoption.WithCredentialsFile.POST /api/v1/vgmq/reload.spreadsheets.values.updateon e.g.B57:F57), never the whole sheet, so other people's edits aren't overwritten.is_unknownis not a column in the sheet today: it comes from the file being inKan Ej/. See the question below.Questions
Kan Ej/, so the folder and the sheet agree?