Platform
Files, certificates and backup
Files live on a Docker volume and are served only through the authenticated API. Certificates are rendered from an HTML template by Gotenberg. Two cron jobs handle nightly database dumps and a weekly check that the latest dump restores.
Storage layout
Storage and backup
| Folder | Contents | Written by |
|---|---|---|
uploads/<course_id>/ | Course resources: slides, PDFs, transcripts | saveUpload |
certs/<trainee_id>/<course_id>.pdf | Issued certificates | issueCertificate |
snapshots/YYYY-MM-DD.sql.gz | Nightly database dumps | cron snapshot.sh |
Serving files
There is no public URL for any file. A download is a normal authenticated request. The handler looks the row up under RLS, so a caller who cannot read the row gets a 404 and never learns the path.
import path from 'node:path';
const ROOT = path.resolve(process.env.UPLOAD_ROOT ?? '/data');
export async function streamResource(req, res) {
const { rows } = await req.db.query(
'SELECT file_path FROM learning_resources WHERE id = $1', [req.params.id]);
if (!rows[0]) return res.sendStatus(404); // RLS hides rows the caller may not read
const abs = path.resolve(ROOT, rows[0].file_path);
if (!abs.startsWith(ROOT + path.sep)) return res.sendStatus(400); // path traversal guard
res.sendFile(abs);
}Check size (Nginx caps the body at 25 MB), an extension allowlist and the sniffed MIME type. Store under a generated uuid name, never the client filename.
Write the file, then insert the row in a transaction. If the insert fails, delete the file. A sweep for files with no row keeps the volume clean.
Certificates follow the same pattern with a streamCertificate handler. The trainee sees their own, admins see all.
Certificate issuance
Certificate sequence
| Function | Does |
|---|---|
issueCertificate(db, traineeId, courseId) | Checks completion and score, renders, writes the PDF, inserts the row. Route: POST /api/certificates/issue. |
renderCertificateHtml(data) | Fills the HTML template with name, course, score, date and verification code. |
escapeHtml(value) | Applied to every value before it enters the template. |
renderPdf(html) | Sends the HTML to Gotenberg and returns the PDF bytes. |
issueCohortCertificates(courseId) | Loops issueCertificate for all completed enrollments. Run from node-cron. |
export async function renderPdf(html: string): Promise<Buffer> {
const form = new FormData();
// Gotenberg looks for a file named index.html in the multipart body
form.append('files', new Blob([html], { type: 'text/html' }), 'index.html');
const r = await fetch(`${process.env.GOTENBERG_URL}/forms/chromium/convert/html`,
{ method: 'POST', body: form });
if (!r.ok) throw new Error(`gotenberg ${r.status}`);
return Buffer.from(await r.arrayBuffer());
}Names go into HTML that a headless browser executes. Escape every value.
Only HTML built on the server goes to Gotenberg. Never pass a user-supplied URL to its URL endpoint.
Gotenberg has no published port. It is reachable only on the Docker network.
Each certificate carries a verification_code, printed as text and a QR code, so anyone can check it against the database.
Backups
# in crontab syntax a literal % must be escaped as \%
0 2 * * * pg_dump "$DATABASE_URL" | gzip > /data/snapshots/$(date +\%F).sql.gz
0 3 * * 0 /opt/capacity-connect/infra/cron/restore-check.shRestores the newest dump into a scratch database, runs a row count on a few tables, drops the database and exits non-zero on any failure. A backup nobody has restored is only a hope.