Database — Schema and Queries
Pool configuration
File: src/lib/postgres.ts
const pool = new Pool({
host: process.env.PG_HOST,
port: parseInt(process.env.PG_PORT || '5432'),
database: process.env.PG_DATABASE,
user: process.env.PG_USER,
password: process.env.PG_PASSWORD,
ssl: process.env.PG_SSL === 'true' ? { rejectUnauthorized: false } : false,
max: 20, // max simultaneous connections
idleTimeoutMillis: 30000, // delay before closing an idle connection
connectionTimeoutMillis: 10000, // wait time to acquire a connection
});
Table schema
Table languages
CREATE TABLE languages (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
name VARCHAR(255) NOT NULL UNIQUE,
code VARCHAR(10) NOT NULL UNIQUE, -- e.g. 'pul', 'wol'
access_code VARCHAR(255) NOT NULL UNIQUE, -- player code
speaker_access_code VARCHAR(255), -- speaker code
speaker_name VARCHAR(255), -- speaker name
is_active BOOLEAN DEFAULT false, -- publicly visible
display_order INTEGER DEFAULT 0, -- display order
primary_color VARCHAR(7) DEFAULT '#D97706', -- e.g. '#D97706'
icon_url TEXT, -- URL of the icon in MinIO
created_at TIMESTAMPTZ DEFAULT NOW()
);
Indexes: code, access_code, is_active
Table cards
CREATE TABLE cards (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
language_id UUID REFERENCES languages(id) ON DELETE CASCADE,
type VARCHAR(20) NOT NULL CHECK (type IN ('word', 'phrase')),
card_number INTEGER NOT NULL,
theme VARCHAR(255), -- e.g. 'Family', 'Food'
created_at TIMESTAMPTZ DEFAULT NOW(),
UNIQUE(language_id, type, card_number) -- unique per language and type
);
Table items
CREATE TABLE items (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
card_id UUID REFERENCES cards(id) ON DELETE CASCADE,
position INTEGER NOT NULL, -- 1-6 for words, 1-4 for phrases
original_text TEXT NOT NULL, -- text in the African language
translation TEXT NOT NULL, -- French translation
audio_url TEXT, -- URL to MinIO (null if no audio)
lot_number INTEGER, -- 1 or 2 for phrases (null for words)
item_type VARCHAR(20), -- 'question' or 'answer' (null for words)
created_at TIMESTAMPTZ DEFAULT NOW(),
UNIQUE(card_id, position)
);
-- Index added in v3 (for IndexedDB)
CREATE INDEX idx_items_card_id ON items(card_id);
Table speakers
CREATE TABLE speakers (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
language_id UUID REFERENCES languages(id) ON DELETE CASCADE,
name VARCHAR(255) NOT NULL,
access_code VARCHAR(255) NOT NULL UNIQUE,
is_active BOOLEAN DEFAULT true,
created_at TIMESTAMPTZ DEFAULT NOW()
);
Table admins
CREATE TABLE admins (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
email VARCHAR(255) NOT NULL UNIQUE,
password_hash TEXT NOT NULL, -- format: pbkdf2$iter$salt$hash
created_at TIMESTAMPTZ DEFAULT NOW()
);
Table statistics
CREATE TABLE statistics (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
language_id UUID REFERENCES languages(id) ON DELETE CASCADE,
card_id UUID REFERENCES cards(id) ON DELETE CASCADE,
item_id UUID REFERENCES items(id) ON DELETE CASCADE,
event_type VARCHAR(50) NOT NULL, -- 'unlock_language', 'view_card', 'play_audio'
count INTEGER DEFAULT 1,
event_date DATE NOT NULL, -- aggregated by day
created_at TIMESTAMPTZ DEFAULT NOW(),
UNIQUE(language_id, card_id, item_id, event_type, event_date)
);
Behavior: Events are aggregated by day via INSERT ... ON CONFLICT DO UPDATE SET count = count + 1 (upsert).
Table contact_messages
CREATE TABLE contact_messages (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
name VARCHAR(255) NOT NULL,
email VARCHAR(255) NOT NULL,
subject VARCHAR(255) NOT NULL,
message TEXT NOT NULL,
status VARCHAR(50) DEFAULT 'unread', -- 'unread', 'read', 'replied', 'archived'
admin_notes TEXT, -- internal admin notes
created_at TIMESTAMPTZ DEFAULT NOW()
);
Table quiz_sessions
CREATE TABLE quiz_sessions (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
language_id UUID REFERENCES languages(id) ON DELETE CASCADE,
category_slug VARCHAR(255) NOT NULL, -- URL slug of the theme
category_name VARCHAR(255), -- human-readable theme name
total_questions INTEGER NOT NULL, -- 15, 18, or 20
score INTEGER, -- null if session not completed
started_at TIMESTAMPTZ DEFAULT NOW(),
completed_at TIMESTAMPTZ, -- null if session abandoned
created_at TIMESTAMPTZ DEFAULT NOW()
);
Table relationships
languages ─┬─< cards ─┬─< items ─── statistics
│ └─────────────────────────── statistics
├─< speakers
└─< quiz_sessions
admins (independent table)
contact_messages (independent table)
Useful SQL queries
Get the full content of a language (for offline download)
SELECT
c.id AS card_id, c.type, c.card_number, c.theme,
i.id AS item_id, i.position, i.original_text, i.translation,
i.audio_url, i.lot_number, i.item_type
FROM cards c
JOIN items i ON i.card_id = c.id
WHERE c.language_id = $1
ORDER BY c.type, c.card_number, i.position;
Audio completion progress for a language
SELECT
COUNT(*) AS total_items,
COUNT(i.audio_url) AS items_with_audio,
ROUND(COUNT(i.audio_url)::numeric / COUNT(*)::numeric * 100, 1) AS completion_pct
FROM cards c
JOIN items i ON i.card_id = c.id
WHERE c.language_id = $1;
Search an item by text
SELECT c.card_number, c.type, i.position, i.original_text, i.translation
FROM items i
JOIN cards c ON i.card_id = c.id
WHERE c.language_id = $1
AND (i.original_text ILIKE $2 OR i.translation ILIKE $2)
ORDER BY c.card_number, i.position;
-- $2 = '%term%'
Cards without audio (for the speaker)
SELECT c.card_number, c.type, i.position, i.original_text
FROM items i
JOIN cards c ON i.card_id = c.id
WHERE c.language_id = $1
AND i.audio_url IS NULL
AND (c.type = 'word' OR i.position IN (1, 4)) -- words: all positions, phrases: 1 and 4
ORDER BY c.type, c.card_number, i.position;
Migrations
Migration files are in the migrations/ folder:
| File | Content |
|---|---|
001_initial_schema.sql |
Initial schema: languages, cards, items, statistics, speakers, contact_messages |
002_create_admins.sql |
admins table |
003_idb_card_id_index_note.sql |
Documentation note (IndexedDB v3) — no SQL to execute |
004_quiz_results.sql |
quiz_sessions table |
To apply a migration:
psql -h $PG_HOST -U $PG_USER -d $PG_DATABASE -f migrations/00X_name.sql