Database reference

Every table the schema creates, what it holds, and the rules that matter when you query it directly.

The authoritative schema is database/schema.sql in the package. php bin/migrate.php applies it, and re-applying it is safe: existing tables, columns and indexes are left alone.

Tables

The schema creates 41 tables. It uses utf8mb4 throughout, so names in any script are stored and printed correctly.

Table	What it holds
`api_certificate_issuances`	The external reference and owning API client for each certificate created through the API.
`api_clients`	External systems allowed to call the versioned certificate API, with their status and rate limit.
`api_idempotency_keys`	Canonical request hashes and completed write responses used to make concurrent API retries safe.
`api_tokens`	Scoped integration credentials stored as hashes, with expiry, revocation and last-used timestamps.
`audit_logs`	Append-only record of who did what, with sanitised metadata, address and user agent.
`certificate_attachments`	Supporting files attached to a certificate.
`certificate_document_versions`	Previous render snapshots, kept when a name correction replaces a PDF.
`certificate_documents`	One row per issued PDF, with its file hash, approved business values and last-render presentation. Appearance follows the current template and verification follows the current certificate.
`certificate_integration_mappings`	Client-owned external course and run keys mapped to a local occurrence, template and optional verifier.
`certificate_status_history`	Every status transition, with who made it and when.
`certificate_versions`	Pre-edit snapshots of certificate and participant details, written before any edit.
`certificates`	The certificate record: its numbers, dates, status, verifier assignment and public token.
`course_certificate_templates`	Named certificate layouts for a course, each with a background image and field positions.
`course_runs`	Dated occurrences of a course, with their own description.
`courses`	The training programmes certificates are issued for.
`email_queue`	Outgoing mail waiting to be sent, with attempt counters.
`email_verification_tokens`	One-time activation links, stored as hashes rather than as the link itself.
`export_jobs`	Requested exports and the files produced for them.
`import_batches`	One row per uploaded import file, with its staged, confirmed and completed state.
`import_rows`	One row per data row in an import, with its validation state and resulting certificate.
`login_attempts`	Rate-limit counters for sign-in, two-factor, activation, reset and public verification.
`notifications`	In-application notices for internal users.
`organizations`	Verifier organisations, so a verifier account can be shown with the body it belongs to.
`participants`	The people certificates are issued to. There is deliberately no national identity-number column.
`password_reset_tokens`	One-time password-reset links, stored as hashes.
`permissions`	The individual permissions a role can hold.
`role_permissions`	Which permissions a role holds. This is the only source of authority; per-user grants are removed by the seed.
`roles`	The four roles: System Admin, Manager, Verifier and Viewer.
`saved_filters`	Saved list filters, per user.
`security_events`	Security-relevant events such as lockouts and refused hosts.
`system_settings`	Runtime settings owned by administrators: branding, sender address, SMTP connection and feature switches.
`two_factor_recovery_codes`	Single-use recovery codes, stored as password hashes.
`two_factor_secrets`	Authenticator secrets, encrypted with the application key.
`user_permissions`	Kept for older installations and deliberately emptied by the seed, so no account can exceed its role.
`user_roles`	Which role an account holds. One account holds exactly one role.
`user_sessions`	Active sessions, so disabling an account or changing its role can revoke them immediately.
`users`	Every account, internal and verifier, with status, role requirement for two-factor authentication and locale preference.
`verification_assignments`	Which verifier account is responsible for which certificate.
`verification_decisions`	Verify, return and reject decisions, with the note the verifier left.
`verification_requests`	Requests sent to a verifier organisation for approval.
`webhook_deliveries`	Immutable signed event payloads and at-least-once delivery attempts for API clients.

How the main records relate

courses
  |- course_runs                  dated occurrences of the course
  |- course_certificate_templates named layouts, one background each
       |
certificates                      one per issued credential
  |- participants                 the person it was issued to
  |- verification_assignments      which verifier is responsible
  |- verification_decisions        verify, return, reject, with the note
  |- certificate_status_history    every transition
  |- certificate_versions          pre-edit snapshots
  |- certificate_documents         one issued PDF, with its render snapshot
       |- certificate_document_versions   previous renders after a name correction

Rules to respect if you query directly

Public visibility is a query, not a column. A certificate is public only when its status is verified, it is not revoked and not deleted. Reproduce all three conditions in any report that claims to count public certificates.
Numbers are unique installation-wide. Both certificates.certificate_number and certificates.unique_id carry unique keys, including against revoked records.
The public token is the address. certificates.public_token is 48 hexadecimal characters and is what a verification link and a QR code contain. Never expose it in a report you publish.
Do not add an identity-number column. The absence of one is a design constraint of the product, and imports, exports, logs and metadata all assume it.
Audit rows are append-only. Insert nothing into audit_logs by hand and delete nothing from it: the archiving job is the only removal path, and it exports before it deletes.
Encrypted values are unreadable without the key. two_factor_secrets and the SMTP entry in system_settings are encrypted with APP_KEY. A dump restored without that key keeps the rows and loses their meaning.

Character set and time

Every table is utf8mb4 with a Unicode collation, so names in Cyrillic, Greek or any other script are stored and printed correctly. Keep DB_CHARSET=utf8mb4: a narrower connection charset corrupts names on the way in.

Timestamps are written using the offset of APP_TIMEZONE. Changing that setting later does not rewrite existing rows, so pick it during installation and leave it.

Applying the schema and the seed

php bin/migrate.php        # create or update the tables
php bin/seed.php           # roles, permissions and default settings
php bin/create-admin.php   # an administrator, if you have no browser installer

bin/seed.php is safe to re-run and never touches accounts or certificates. It does, however, empty user_permissions and rebuild the role grants, which is how the product guarantees that authority comes from the role alone.