snakkimo-API

REST API server for the snakkimo project. Connects to a PostgreSQL database and stores picture assets on Hetzner Object Storage.

• Base URL: https://hyggecraftery.com/api/snakkimo
• Auth: All /api/* routes require Authorization: Bearer <token>
• Public routes: GET /health, POST /auth/register, POST /auth/login

---

Authentication

Two token types are accepted:

1. Static dev/admin tokens

Configured via the API_TOKENS environment variable (comma-separated). These bypass role checks and are used for server-to-server or CMT admin access.

Authorization: Bearer dev_ccfd6fd1...

2. JWT tokens (end-users)

Issued by POST /auth/register and POST /auth/login. End-users with role end-user receive a 403 on all /api/* routes — JWT auth is currently used to gate app access only.

Authorization: Bearer eyJhbGci...

---

Environment Variables

Variable	Description
DB_HOST	PostgreSQL hostname (Coolify internal)
DB_PORT	PostgreSQL port (default: 5432)
DB_NAME	Database name
DB_USER	Database user
DB_PASSWORD	Database password
DB_SSL	true or false
PORT	API server port (default: 3000)
API_TOKENS	Comma-separated static Bearer tokens
JWT_SECRET	Secret for signing JWTs (long random string)
JWT_EXPIRES_IN	JWT lifetime, e.g. 7d
S3_ACCESS_KEY	Hetzner Object Storage access key
S3_SECRET_KEY	Hetzner Object Storage secret key

---

Storage

Provider: Hetzner Object Storage (S3-compatible)
Endpoint: https://fsn1.your-objectstorage.com
Bucket: snakkimo
Public base URL: https://snakkimo.fsn1.your-objectstorage.com

Files are stored under pictures/<picture_id>/<uuid>.<ext>.
Deleting a picture via the API also deletes the file from the bucket.

---

Database Schema

Host: Coolify internal network · Database: snakkimo · Schema: public

All tables have created_at TIMESTAMPTZ NOT NULL DEFAULT NOW() and updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW() with an auto-update trigger unless noted otherwise.

Relations overview

pictures ──── word_pictures ──────────────────── words ──── word_categories ──── categories
   │                                               │
   └──── object_pictures ──── objects ──── object_words
                                 │
                           object_pairs ──── pairs ──── questions
                                                  │
                                           positive_statement_id ──┐
                                           negative_statement_id ──┴── statements ──── statement_positive_words ──┐
                                                                                  └─── statement_negative_words ──┴── words

users ──── languages (native)
users_public ──── user_names
              └── languages (native + target)

---

pictures

Column	Type	Default	Description
id	UUID PK	gen_random_uuid()
status	VARCHAR(20)	uploaded	uploaded · published · blocked
blocked_reason	VARCHAR(20)	null	regenerate · not_to_use
generation_prompt	TEXT	null
generation_timestamp	TIMESTAMPTZ	null
generation_duration_s	NUMERIC(10,3)	null
published_timestamp	TIMESTAMPTZ	null
blocked_timestamp	TIMESTAMPTZ	null
blurhash	TEXT	null
picture_link	TEXT	null	Public Hetzner URL, set on upload
design	TEXT	null	Design category
objects_created	BOOLEAN	false	Set to true once all objects have been drawn
objects_created_at	TIMESTAMPTZ	null	Auto-set when objects_created → true

Junctions: word_pictures, object_pictures

---

words

Column	Type	Default	Description
id	UUID PK	gen_random_uuid()
titel_de	TEXT	null	German
titel_en	TEXT	null	English
titel_sv	TEXT	null	Swedish (ISO 639-1: sv)
status	VARCHAR(20)	requested	requested · translated · generated · blocked · published
difficulty_level	SMALLINT	null	1–50
requested_at	TIMESTAMPTZ	null
published_at	TIMESTAMPTZ	null	Auto-set on status → published
blocked_at	TIMESTAMPTZ	null	Auto-set on status → blocked

Junctions: word_pictures, word_categories, object_words, statement_positive_words, statement_negative_words

---

categories

Column	Type	Default	Description
id	UUID PK	gen_random_uuid()
titel_de	TEXT	null
titel_en	TEXT	null
titel_sv	TEXT	null
status	VARCHAR(20)	requested	requested · blocked · published
difficulty_level	SMALLINT	null	1–50
published_at	TIMESTAMPTZ	null
blocked_at	TIMESTAMPTZ	null

Junctions: word_categories

---

questions

Column	Type	Default	Description
id	UUID PK	gen_random_uuid()
status	VARCHAR(20)	draft	draft · blocked · published
sentence_de	TEXT	null	German question sentence
sentence_en	TEXT	null	English
sentence_sv	TEXT	null	Swedish
blocked_topic	TEXT	null
published_at	TIMESTAMPTZ	null
blocked_at	TIMESTAMPTZ	null

Sentence text may contain {{uuid}} placeholders referring to a words.id or objects.id.

---

statements

Column	Type	Default	Description
id	UUID PK	gen_random_uuid()
status	VARCHAR(20)	draft	draft · blocked · published
positive_sentence_de	TEXT	null	Positive form, German
positive_sentence_en	TEXT	null
positive_sentence_sv	TEXT	null
negative_sentence_de	TEXT	null	Negative form, German
negative_sentence_en	TEXT	null
negative_sentence_sv	TEXT	null
answer	BOOLEAN	null	For yes/no pairs: true = Ja, false = Nein, null = open
blocked_topic	TEXT	null
published_at	TIMESTAMPTZ	null
blocked_at	TIMESTAMPTZ	null

Sentence text may contain {{uuid}} placeholders.

GET /api/statements/:id also returns positive_word_ids[] and negative_word_ids[].

Junctions: statement_positive_words, statement_negative_words

---

pairs

Column	Type	Default	Description
id	UUID PK	gen_random_uuid()
status	VARCHAR(20)	draft	draft · blocked · published
answer_type	TEXT	—	text · yes_no · question · word (required)
difficulty_level	SMALLINT	null	1–50
blocked_topic	TEXT	null
question_id	UUID FK	null	→ questions.id ON DELETE SET NULL
positive_statement_id	UUID FK	null	→ statements.id ON DELETE SET NULL
negative_statement_id	UUID FK	null	→ statements.id ON DELETE SET NULL
published_at	TIMESTAMPTZ	null
blocked_at	TIMESTAMPTZ	null

answer_type semantics:

Type	question	positive_statement	negative_statement
text	—	sentence (required)	—
yes_no	optional sentence	answer field (true/false/null)	—
question	sentence (required)	sentence (required)	sentence (optional)
word	optional sentence	linked via statement_positive_words	linked via statement_negative_words

Junctions: object_pairs

---

objects

Column	Type	Default	Description
id	UUID PK	gen_random_uuid()
status	VARCHAR(20)	draft	draft · blocked · published
selections	JSONB	null	[{points: [{x: 0-1, y: 0-1}, …]}] — relative coordinates
notes	TEXT	null
blocked_topic	TEXT	null
published_at	TIMESTAMPTZ	null
blocked_at	TIMESTAMPTZ	null

Junctions: object_words, object_pictures, object_pairs

---

users (CMT admin accounts)

Column	Type	Default	Description
id	UUID PK	gen_random_uuid()
email	TEXT UNIQUE	—	Lowercase-indexed
password_hash	TEXT	—	bcrypt hash
role	VARCHAR(20)	end-user	end-user · admin
is_active	BOOLEAN	true	False = locked out
language_native_id	UUID FK	null	→ languages.id ON DELETE SET NULL

---

languages

Column	Type	Default	Description
id	UUID PK	gen_random_uuid()
titel_de	TEXT	null
titel_en	TEXT	null
titel_sv	TEXT	null
short_en	VARCHAR(10)	null	ISO 639-1 code, e.g. de, en, sv
status	VARCHAR(20)	draft	draft · blocked · published
published_at	TIMESTAMPTZ	null
blocked_at	TIMESTAMPTZ	null
blocked_topic	TEXT	null

Seeded: German (short_en = 'de') is automatically inserted on migration.
Default: All users without a native language are assigned German on migration.

---

user_names

Column	Type	Description
id	UUID PK
username_lowercase	TEXT UNIQUE	Lowercase version for uniqueness check
username	TEXT	Display name (original casing)

---

users_public (app user profiles)

Column	Type	Description
id	UUID PK
username_id	UUID FK	→ user_names.id ON DELETE SET NULL
language_native_id	UUID FK	→ languages.id ON DELETE SET NULL
language_target_id	UUID FK	→ languages.id ON DELETE SET NULL

---

blocklist

Column	Type	Description
id	UUID PK
is_blocked	BOOLEAN	true = active block
username	TEXT	nullable
email	TEXT	nullable
phone	TEXT	nullable
ip	INET	nullable
blocked_at	TIMESTAMPTZ
unblocked_at	TIMESTAMPTZ

Indexed on lowercase email, lowercase username, phone, ip.

---

Junction tables

Table	Columns
word_pictures	word_id → words, picture_id → pictures
word_categories	word_id → words, category_id → categories
object_words	object_id → objects, word_id → words
object_pictures	object_id → objects, picture_id → pictures
object_pairs	object_id → objects, pair_id → pairs
statement_positive_words	statement_id → statements, word_id → words
statement_negative_words	statement_id → statements, word_id → words

All junction tables use ON DELETE CASCADE on both sides.

---

API Endpoints

BASE = https://hyggecraftery.com/api/snakkimo
TOKEN = <your bearer token>

---

Auth (public — no token required)

POST /auth/register

curl -X POST "$BASE/auth/register" \
  -H "Content-Type: application/json" \
  -d '{"email": "user@example.com", "password": "sicher123"}'
# → 201 { user: {id, email, role}, token }
# → 403 if email is on blocklist
# → 409 if email already registered

POST /auth/login

curl -X POST "$BASE/auth/login" \
  -H "Content-Type: application/json" \
  -d '{"email": "user@example.com", "password": "sicher123"}'
# → 200 { user: {id, email, role, native_lang}, token }
# → 401 if wrong credentials
# → 403 if account is inactive

JWT payload: { userId, email, role, native_lang } — native_lang is the ISO 639-1 code of the user's native language (e.g. "de").

---

Pictures

GET /api/pictures

Query params: status, objects_created (true/false), limit (max 500, default 50), offset

curl "$BASE/api/pictures?status=uploaded&objects_created=false" \
  -H "Authorization: Bearer $TOKEN"

GET /api/pictures/:id

POST /api/pictures

Body fields: generation_prompt, generation_timestamp, generation_duration_s, blurhash, design

POST /api/pictures/:id/upload

multipart/form-data, field file, max 20 MB. Sets picture_link.

PATCH /api/pictures/:id

Writable: status, blocked_reason, generation_prompt, generation_timestamp, generation_duration_s, published_timestamp, blocked_timestamp, blurhash, picture_link, design, objects_created, objects_created_at

Auto-behavior:

• status: "published" → auto-sets published_timestamp
• status: "blocked" → auto-sets blocked_timestamp
• objects_created: true → auto-sets objects_created_at

DELETE /api/pictures/:id

Deletes DB row + Hetzner file. Returns 204.

Word links

POST   /api/pictures/:id/words/:wordId
DELETE /api/pictures/:id/words/:wordId
GET    /api/pictures/:id/words

---

Words

GET /api/words

Query params: status, search (ILIKE on titel_de/en/sv), limit, offset

GET /api/words/:id

Returns word + picture_ids[] + category_ids[]

POST /api/words

curl -X POST "$BASE/api/words" \
  -H "Authorization: Bearer $TOKEN" \
  -H "Content-Type: application/json" \
  -d '{"titel_de": "Hund", "titel_en": "Dog", "titel_sv": "Hund", "difficulty_level": 5}'

PATCH /api/words/:id

Writable: titel_de, titel_en, titel_sv, status, difficulty_level, requested_at, published_at, blocked_at
Auto: status: "published" → published_at, status: "blocked" → blocked_at

DELETE /api/words/:id

Links

POST   /api/words/:id/pictures/:pictureId
DELETE /api/words/:id/pictures/:pictureId
POST   /api/words/:id/categories/:categoryId
DELETE /api/words/:id/categories/:categoryId

---

Categories

GET /api/categories — ?status=&limit=&offset=

GET /api/categories/:id — includes word_ids[]

POST /api/categories

curl -X POST "$BASE/api/categories" \
  -H "Authorization: Bearer $TOKEN" \
  -H "Content-Type: application/json" \
  -d '{"titel_de": "Tiere", "titel_en": "Animals", "titel_sv": "Djur"}'

PATCH /api/categories/:id

DELETE /api/categories/:id

---

Questions

GET /api/questions — ?status=&limit=&offset=

GET /api/questions/:id

POST /api/questions

curl -X POST "$BASE/api/questions" \
  -H "Authorization: Bearer $TOKEN" \
  -H "Content-Type: application/json" \
  -d '{"sentence_de": "Was ist das?", "status": "draft"}'

PATCH /api/questions/:id

Writable: status, sentence_de, sentence_en, sentence_sv, blocked_topic, published_at, blocked_at

DELETE /api/questions/:id

---

Statements

GET /api/statements — ?status=&limit=&offset=

GET /api/statements/:id

Returns statement + positive_word_ids[] + negative_word_ids[]

POST /api/statements

curl -X POST "$BASE/api/statements" \
  -H "Authorization: Bearer $TOKEN" \
  -H "Content-Type: application/json" \
  -d '{
    "positive_sentence_de": "Das ist ein {{word-uuid}}.",
    "answer": true,
    "status": "draft"
  }'

Body fields: positive_sentence_de/en/sv, negative_sentence_de/en/sv, answer (boolean|null), blocked_topic

PATCH /api/statements/:id

Writable: status, positive_sentence_de/en/sv, negative_sentence_de/en/sv, answer, blocked_topic, published_at, blocked_at

DELETE /api/statements/:id

Word links

POST   /api/statements/:id/positive-words/:wordId
DELETE /api/statements/:id/positive-words/:wordId
POST   /api/statements/:id/negative-words/:wordId
DELETE /api/statements/:id/negative-words/:wordId

---

Pairs

GET /api/pairs — ?status=&answer_type=&limit=&offset=

GET /api/pairs/:id

POST /api/pairs

curl -X POST "$BASE/api/pairs" \
  -H "Authorization: Bearer $TOKEN" \
  -H "Content-Type: application/json" \
  -d '{
    "answer_type": "question",
    "question_id": "<uuid>",
    "positive_statement_id": "<uuid>",
    "negative_statement_id": "<uuid>",
    "difficulty_level": 10
  }'

answer_type is required. Valid values: text, yes_no, question, word.

PATCH /api/pairs/:id

Writable: status, answer_type, difficulty_level, blocked_topic, question_id, positive_statement_id, negative_statement_id, published_at, blocked_at

DELETE /api/pairs/:id

---

Objects

GET /api/objects — ?status=&picture_id=&limit=&offset=

GET /api/objects/:id — includes word_ids[], picture_ids[], pair_ids[]

POST /api/objects

curl -X POST "$BASE/api/objects" \
  -H "Authorization: Bearer $TOKEN" \
  -H "Content-Type: application/json" \
  -d '{
    "selections": [{"points": [{"x": 0.1, "y": 0.2}, {"x": 0.5, "y": 0.2}, {"x": 0.3, "y": 0.7}]}],
    "status": "draft"
  }'

selections is a JSONB array of polygons with relative (0–1) coordinates.

PATCH /api/objects/:id

Writable: status, selections, notes, blocked_topic, published_at, blocked_at

DELETE /api/objects/:id

Links

GET    /api/objects/:id/words
POST   /api/objects/:id/words/:wordId
DELETE /api/objects/:id/words/:wordId
POST   /api/objects/:id/pictures/:pictureId
DELETE /api/objects/:id/pictures/:pictureId
GET    /api/objects/:id/pairs
POST   /api/objects/:id/pairs/:pairId
DELETE /api/objects/:id/pairs/:pairId

---

Blocklist

GET /api/blocklist — ?is_blocked=true&limit=&offset=

GET /api/blocklist/:id

POST /api/blocklist

curl -X POST "$BASE/api/blocklist" \
  -H "Authorization: Bearer $TOKEN" \
  -H "Content-Type: application/json" \
  -d '{"email": "spam@example.com", "is_blocked": true}'

PATCH /api/blocklist/:id

DELETE /api/blocklist/:id

POST /api/blocklist/check

Check if an email/username/phone/IP is blocked.

curl -X POST "$BASE/api/blocklist/check" \
  -H "Authorization: Bearer $TOKEN" \
  -H "Content-Type: application/json" \
  -d '{"email": "test@example.com"}'
# → { blocked: false }
# → { blocked: true, entry: {...} }

---

Utilities

GET /api/tables

List all tables in the public schema.

GET /api/tables/:table

Read rows from any table (?limit=100&offset=0).

POST /api/query

Execute raw SQL. Destructive statements (DROP, TRUNCATE, DELETE FROM) require the header X-Confirm-Destructive: yes.

curl -X POST "$BASE/api/query" \
  -H "Authorization: Bearer $TOKEN" \
  -H "Content-Type: application/json" \
  -d '{"sql": "SELECT count(*) FROM pictures"}'

---

Placeholder format

Sentence fields in questions and statements store word/object references as {{uuid}}:

"Der {{abc-123}} bellt."

• {{wordId}} — links to words.id
• {{objectId}} — links to objects.id

The CMT resolves placeholders back to human-readable labels when displaying text for editing.

---

Quick Start

export TOKEN="dev_ccfd6fd149806a900311e253b0c3b5d1e107a68ab5619a5fde61f107ce9098ce"
export BASE="https://hyggecraftery.com/api/snakkimo"

# Create + upload a picture
ID=$(curl -s -X POST "$BASE/api/pictures" \
  -H "Authorization: Bearer $TOKEN" \
  -H "Content-Type: application/json" \
  -d '{"generation_prompt": "A red apple on a table", "generation_duration_s": 3.1}' \
  | python3 -c "import sys,json; print(json.load(sys.stdin)['id'])")

curl -X POST "$BASE/api/pictures/$ID/upload" \
  -H "Authorization: Bearer $TOKEN" \
  -F "file=@apple.jpg"

# Create a word
WID=$(curl -s -X POST "$BASE/api/words" \
  -H "Authorization: Bearer $TOKEN" \
  -H "Content-Type: application/json" \
  -d '{"titel_de": "Apfel", "titel_en": "Apple", "titel_sv": "Äpple"}' \
  | python3 -c "import sys,json; print(json.load(sys.stdin)['id'])")

# Create a question pair
QID=$(curl -s -X POST "$BASE/api/questions" \
  -H "Authorization: Bearer $TOKEN" \
  -H "Content-Type: application/json" \
  -d "{\"sentence_de\": \"Was ist das?\", \"status\": \"draft\"}" \
  | python3 -c "import sys,json; print(json.load(sys.stdin)['id'])")

SID=$(curl -s -X POST "$BASE/api/statements" \
  -H "Authorization: Bearer $TOKEN" \
  -H "Content-Type: application/json" \
  -d "{\"positive_sentence_de\": \"Das ist ein {{$WID}}.\", \"status\": \"draft\"}" \
  | python3 -c "import sys,json; print(json.load(sys.stdin)['id'])")

curl -s -X POST "$BASE/api/pairs" \
  -H "Authorization: Bearer $TOKEN" \
  -H "Content-Type: application/json" \
  -d "{\"answer_type\": \"question\", \"question_id\": \"$QID\", \"positive_statement_id\": \"$SID\"}"

---

Deployment

• Platform: Coolify (snakkimo project, production environment)
• App UUID: kmuk5zfthcfov4irjb07y6tx
• Gitea repo: https://git.hyggecraftery.com/admin/snakkimo-API
• Docker: Node 20 Alpine, port 3000
• Health check: GET /health (Docker-native HEALTHCHECK)
• Migrations: Run automatically on every server start (src/db-migrate.js) — fully idempotent