Learn English DXB Worksheet Studio

Version: 0.1.040

Learn English DXB Worksheet Studio is a self-hosted web app for creating, editing, storing, duplicating, regenerating, and exporting ESL worksheets/tests. AI can draft worksheets, layout ideas, and future image assets, but worksheets are stored as editable structured JSON so a teacher can fix small mistakes without regenerating the whole worksheet.

0.1.040 is a handoff/documentation refresh after the editor and preview workflow improvements through 0.1.039. It records the current build state so the next session can continue without rediscovering completed work.

Architecture

• Backend: Python 3.11+, FastAPI, SQLModel, SQLite, Playwright
• Frontend: Vite, React, TypeScript
• Storage: local filesystem under storage/
• AI: mock provider by default, OpenAI behind an adapter
• Backend source of truth for app version: backend/worksheet_studio/version.py
• Frontend package metadata mirrors the current app version in frontend/package.json

The frontend must not implement worksheet generation, persistence, validation, export, AI orchestration, or state correctness rules. When behavior affects saved truth, implement it in the backend first and expose it through an API.

Architecture Decisions

• Worksheet content remains structured JSON, never final-image-only.
• Add a separate worksheet content schema version, starting at 1, independent of the app version.
• Validate AI output and frontend save payloads through the same backend Pydantic content schema.
• Backend services should normalize optional worksheet fields after validation; persistence should receive one complete validated content object.
• Worksheet saves should snapshot the previous content into WorksheetVersion before overwrite and keep a bounded history.
• SQLite should use WAL mode and foreign keys on every connection.
• Default runtime database storage should live under storage/db/.
• Themes remain DB-backed JSON records because they are user-customizable.
• Template definitions should be code-owned and seeded into DB rows for listing/selection.
• AI jobs should record provider name, model name, request, raw response where possible, status, and errors.
• Asset storage should use backend-owned IDs and relative paths; worksheet JSON should reference assets, never inline binary data.
• Frontend A4 preview is an approximate renderer for editing. Backend PDF export remains authoritative.

Environment Variables

• WORKSHEET_STUDIO_DB_PATH: SQLite DB path, default storage/db/worksheet_studio.sqlite3
• WORKSHEET_STUDIO_STORAGE_DIR: runtime storage directory, default storage
• WORKSHEET_STUDIO_AI_PROVIDER: mock or openai, default mock
• WORKSHEET_STUDIO_BACKUP_DIR: SQLite backup directory, default storage/backups
• WORKSHEET_STUDIO_BACKUP_RETENTION_DAYS: backup retention in days, default 30
• OPENAI_API_KEY: required only for OpenAI mode
• OPENAI_MODEL: OpenAI model, default gpt-4.1-mini
• VITE_API_BASE_URL: frontend API base URL at build time, default /api

Backend settings read process environment first and can also load ignored .env files from backend/.env or repo-root .env when commands are run from backend/.

Backend Setup

cd backend
python -m venv .venv
source .venv/bin/activate
pip install -e ".[dev]"
playwright install chromium
export WORKSHEET_STUDIO_STORAGE_DIR=../storage
export WORKSHEET_STUDIO_DB_PATH=../storage/db/worksheet_studio.sqlite3
export WORKSHEET_STUDIO_AI_PROVIDER=mock
python -m worksheet_studio.seed
uvicorn worksheet_studio.main:app --reload

The API runs at http://127.0.0.1:8000.

Useful endpoints:

• GET /api/health
• POST /api/worksheets
• GET /api/worksheets
• GET /api/worksheets/{worksheet_id}
• PUT /api/worksheets/{worksheet_id}
• POST /api/worksheets/{worksheet_id}/duplicate
• GET /api/worksheets/{worksheet_id}/versions
• GET /api/worksheets/{worksheet_id}/versions/{version_id}
• POST /api/worksheets/{worksheet_id}/versions/{version_id}/restore
• POST /api/worksheets/{worksheet_id}/sections/{section_id}/regenerate
• POST /api/worksheets/{worksheet_id}/export/pdf
• GET /api/worksheets/{worksheet_id}/preview/html
• POST /api/worksheets/{worksheet_id}/preview/png
• GET /api/templates
• GET /api/themes
• GET /api/assets
• POST /api/assets
• PUT /api/assets/{asset_id}
• DELETE /api/assets/{asset_id}
• POST /api/ai/generate-worksheet

Seed the database any time with:

cd backend
source .venv/bin/activate
WORKSHEET_STUDIO_STORAGE_DIR=../storage WORKSHEET_STUDIO_DB_PATH=../storage/db/worksheet_studio.sqlite3 python -m worksheet_studio.seed

Frontend Setup

cd frontend
npm install
npm run dev

The UI runs at http://127.0.0.1:5173. In development, Vite proxies /api and /storage to the backend at http://127.0.0.1:8000.

To build the UI against a different backend origin, set VITE_API_BASE_URL before npm run build.

Editor Workflow

The worksheet editor keeps unsaved edits in local draft state until Save is clicked. The toolbar shows a saved/unsaved indicator, browser refresh/navigation is guarded when changes are dirty, and Back asks before discarding unsaved edits.

The edit panel supports worksheet metadata, title/subtitle/instructions, teacher notes, structured answer-key rows, additional answer-key entries, template/theme selection, worksheet-type section templates, section type/points, item reading roles, section/item duplicate controls, and paste import for item lists. Saved truth still belongs to the backend; frontend edits are temporary until a full worksheet save succeeds.

The editor can switch between the live React editing preview and the saved backend-rendered preview. The versions panel can preview a saved version before restore. Restoring a version requires explicit confirmation and replaces the current saved worksheet content on the backend.

Mock AI Mode

Mock AI is the default. It supports spelling, grammar, vocabulary, reading, and speaking worksheet drafts without external API calls:

export WORKSHEET_STUDIO_AI_PROVIDER=mock

OpenAI Mode

OpenAI mode is available behind the backend provider adapter:

export WORKSHEET_STUDIO_AI_PROVIDER=openai
export OPENAI_API_KEY=...
export OPENAI_MODEL=gpt-4.1-mini

The OpenAI provider sends structured system/user prompts, requests JSON-object output, validates worksheet and section JSON through the same backend Pydantic schemas used for saved data, and stores raw provider output on AI jobs for debugging. Local development should stay in mock mode unless you explicitly want API calls.

Content And Versioning

Worksheet content is validated by backend Pydantic schema models before it reaches services or persistence. Worksheet.content_schema_version starts at 1 and is independent of the application version. The current v1 save contract requires a complete worksheet content object on PUT; partial patch/merge semantics are intentionally not implemented yet.

Every worksheet create/save path keeps backend-owned structured JSON. On update and section regeneration, the previous content is snapshotted into WorksheetVersion before overwrite and history is capped to the latest 50 versions per worksheet. Regenerating one section also refreshes answer-key entries for that section so exports do not keep stale answers.

Assets

Uploaded assets are stored by the backend under content-addressed relative paths:

storage/assets/{sha256-prefix}/{sha256}/{original_filename}

Worksheet JSON should reference asset IDs/URLs in section layout or item metadata. The frontend must not construct storage paths directly; it uses the Asset.url returned by the API.

Asset references currently render in:

• the React A4 editor preview
• backend-generated PDF HTML/export

Assets can be renamed through PUT /api/assets/{id}. Assets can be deleted through DELETE /api/assets/{id} only when no worksheet content references the asset ID or URL. Use delete_file=true to also remove the stored file when no other asset record points at the same content-addressed path.

PDF Export

PDF export is available through POST /api/worksheets/{id}/export/pdf. The backend renders structured worksheet content to printable HTML, uses Playwright/Chromium to create an A4 PDF, saves it under storage/exports/, creates an Export database record, and returns a /storage/... download path. The frontend Export PDF button uses that returned path to start a browser download/open action.

The backend-rendered PDF/preview keeps student worksheet pages separate from answers. Item answers render as blank answer spaces on the student pages, while item answers, extra answer-key entries, and teacher notes render on a trailing Answer Key page. Backend print CSS avoids splitting sections, items, and answer-key entries across pages where Chromium can honor the rule. Dense worksheets automatically use tighter typography, item numbering, score badges, and denser answer-key columns.

Backend Preview

The authoritative saved worksheet preview is available through:

• GET /api/worksheets/{id}/preview/html
• POST /api/worksheets/{id}/preview/png

The editor’s “Backend preview” action opens the HTML preview for the saved worksheet. Save local edits first if you want them reflected there.

Themes And Templates

Themes are stored as DB-backed JSON tokens and are applied by the authoritative backend renderer. Supported v1 tokens include:

• mode: adult or kids
• primary: main heading/accent color
• accent: secondary accent color
• background: page background color
• font_family: CSS font family used by preview/export

Template definitions are code-owned and seeded into DB rows. Their layout hints, such as sections and style, are applied by the backend renderer for saved worksheet preview and export.

If Chromium is missing, run:

cd backend
source .venv/bin/activate
playwright install chromium

Debian Notes

For a Debian 13 VM, run:

./scripts/install-debian.sh

For development, run the backend and frontend using scripts/dev-backend.sh and scripts/dev-frontend.sh.

Systemd Deployment

The repository includes systemd unit templates under scripts/ for a Debian VM installed at /opt/LEL-Worksheet with a service user/group named worksheet.

Prepare the app once:

sudo useradd --system --home /opt/LEL-Worksheet --shell /usr/sbin/nologin worksheet
sudo chown -R worksheet:worksheet /opt/LEL-Worksheet/storage
cd /opt/LEL-Worksheet/backend
python3 -m venv .venv
source .venv/bin/activate
pip install -e ".[dev]"
playwright install chromium
WORKSHEET_STUDIO_STORAGE_DIR=../storage WORKSHEET_STUDIO_DB_PATH=../storage/db/worksheet_studio.sqlite3 python -m worksheet_studio.seed
cd ../frontend
npm install
npm run build

Install and start services:

sudo cp scripts/worksheet-studio-*.service scripts/worksheet-studio-*.timer /etc/systemd/system/
sudo systemctl daemon-reload
sudo systemctl enable --now worksheet-studio-backend.service
sudo systemctl enable --now worksheet-studio-frontend.service
sudo systemctl enable --now worksheet-studio-backup.timer

Useful operations:

systemctl status worksheet-studio-backend.service
systemctl status worksheet-studio-frontend.service
systemctl list-timers worksheet-studio-backup.timer
journalctl -u worksheet-studio-backend.service -f
journalctl -u worksheet-studio-backup.service

The frontend unit uses npm run preview on 127.0.0.1:4173. Put a reverse proxy in front of the backend/frontend services for browser access beyond localhost.

Reverse Proxy

The app does not require nginx on the same VM if you already have an external reverse proxy such as Nginx Proxy Manager. The browser-facing proxy must route:

• /api/ to the backend service on the app VM port 8000
• /storage/ to the backend service on the app VM port 8000
• / to the frontend preview service on the app VM port 4173

For Nginx Proxy Manager, create a proxy host for the frontend service and add custom locations for /api/ and /storage/ pointing to the backend service. Enable Websockets only if you later need them; current API calls do not require them.

The repository also includes scripts/nginx-worksheet-studio.conf as an optional same-VM nginx example. Use it only if you want nginx installed on the app VM:

sudo apt-get install nginx
sudo cp scripts/nginx-worksheet-studio.conf /etc/nginx/sites-available/worksheet-studio
sudo ln -s /etc/nginx/sites-available/worksheet-studio /etc/nginx/sites-enabled/worksheet-studio
sudo nginx -t
sudo systemctl reload nginx

Set server_name in the copied nginx file before using a real hostname. Add TLS with certbot or your preferred certificate process before exposing the app outside a trusted network.

Deployment Smoke Check

After systemd services and the reverse proxy are configured, run:

WORKSHEET_STUDIO_PUBLIC_URL=https://worksheets.example.com ./scripts/deploy-smoke.sh

The smoke script checks:

• GET /api/health
• GET /api/worksheets
• frontend HTML at /
• storage route reachability at /storage/

Optional overrides:

• WORKSHEET_STUDIO_API_URL: direct API URL if it is not ${WORKSHEET_STUDIO_PUBLIC_URL}/api
• WORKSHEET_STUDIO_STORAGE_URL: direct storage URL if it is not ${WORKSHEET_STUDIO_PUBLIC_URL}/storage
• WORKSHEET_STUDIO_SMOKE_BACKUP=1: also run scripts/backup-sqlite.sh

Backups And Restore

Back up the SQLite database with:

./scripts/backup-sqlite.sh

The script uses WORKSHEET_STUDIO_DB_PATH and WORKSHEET_STUDIO_STORAGE_DIR if set. By default it backs up storage/db/worksheet_studio.sqlite3 to timestamped files under storage/backups/, verifies the backup with PRAGMA integrity_check, and deletes backups older than WORKSHEET_STUDIO_BACKUP_RETENTION_DAYS.

Manual restore procedure:

# Stop the backend first if it is running.
cp storage/db/worksheet_studio.sqlite3 storage/db/worksheet_studio.sqlite3.before-restore
cp storage/backups/worksheet_studio_YYYYMMDDTHHMMSSZ.sqlite3 storage/db/worksheet_studio.sqlite3
sqlite3 storage/db/worksheet_studio.sqlite3 "PRAGMA integrity_check;"
# Start the backend again.

Keep copies of storage/backups/ off the VM for real deployment use.

Git Workflow

Work inside /opt/LEL-Worksheet, make small logical commits, and push completed commits to the configured repo server at origin/main. Do not leave completed milestone commits only in the local clone unless the project owner explicitly asks to pause pushing.

Versioning

The project starts at 0.1.000; the current version is 0.1.040.

• Project owner controls major/minor changes such as 0.1 to 0.2.
• Increment only the final numeric part for meaningful changes.
• Update CHANGELOG.md and CONTINUATION_PROMPT.md for each meaningful versioned change.