Database Schema

This document covers all license-related tables in the unified MySQL database.

The schema name is deployment-specific and is not hard-coded in the application. It is read from the FOREN_DB_NAME environment variable (see config.php and license/config.php; both fall back to the placeholder foren). Below, <DB> and <DB_USER> stand for whatever those variables are set to. See DEPLOYMENT.md for how to confirm the live values.

Entity-Relationship Diagram (ASCII)

                ┌─────────────┐
                │  admins     │
                └─────────────┘

  ┌──────────────────┐
  │  licenses        │◄─────┐
  │──────────────────│      │
  │ id (PK)          │      │
  │ license_key (U)  │      │
  │ product          │      │
  │ plan (FK→plans)  │──────┼──►┌───────────────┐
  │ holder           │      │   │  license_      │
  │ ...              │      │   │  plans        │
  └──────────────────┘      │   │──────────────│
         │                        │     slug (PK)  │
         │ 1:N                    │     features  │
         ▼                        └───────────────┘
  ┌──────────────────┐
  │  license_devices │      ┌─────────────────────────┐
  │──────────────────│      │ license_product_        │
  │ id (PK)          │      │ authorizations          │
  │ license_id (FK)  │      │─────────────────────────│
  │ machine_code     │      │ license_id (FK)         │
  │ machine_fingerprint│    │ product_slug            │
  │ is_revoked       │      │ is_active               │
  └──────────────────┘      │ expires_at              │
                            └─────────────────────────┘
                                     │
                                     ▼
                            ┌─────────────────────────┐
                            │ license_authorization_  │
                            │ tokens                  │
                            │─────────────────────────│
                            │ token_hash (U)          │
                            │ license_id (FK)         │
                            │ product_slug            │
                            │ expires_at              │
                            └─────────────────────────┘

  ┌──────────────────┐  ┌─────────────────┐  ┌────────────────┐
  │ activation_logs  │  │ certificates    │  │ license_events │
  │──────────────────│  │─────────────────│  │────────────────│
  │ id (PK)          │  │ id (PK)         │  │ id (PK)        │
  │ license_id (FK)  │  │ serial (U)      │  │ license_id(FK) │
  │ activation_code  │  │ cert_pem        │  │ event_type     │
  │ machine_         │  │ key_pem_        │  │ delivered     │
  │   fingerprint    │  │   encrypted     │  │ retry_count    │
  │ result           │  │ status          │  │ last_error    │
  │ ...              │  │ key_download_   │  └────────────────┘
  └──────────────────┘  │   token         │         │
                       │ expires_at      │         ▼
                       └─────────────────┘  ┌────────────────┐
                                            │ license_       │
                                            │ webhooks       │
                                            │────────────────│
                                            │ url            │
                                            │ secret        │
                                            │ scope         │
                                            └────────────────┘

  ┌──────────────────┐  ┌─────────────────┐
  │ api_tokens       │  │ products        │
  │──────────────────│  │─────────────────│
  │ id (PK)          │  │ id (PK)         │
  │ token_hash (U)   │  │ slug (U)        │
  │ name             │  │ name            │
  │ scope            │  │ is_active       │
  │ expires_at       │  └─────────────────┘
  └──────────────────┘

Tables

`licenses` — Core license records

Column	Type	Notes
---	---	---
`id`	BIGINT UNSIGNED PK
`license_key`	VARCHAR	UNIQUE. Format: `COF-YYYYMMDD-XXXXXX`
`batch_no`	VARCHAR	Group identifier for batch-generated licenses
`product`	VARCHAR	e.g., `CoForen`
`product_version`	VARCHAR	e.g., `0.8.1`
`holder`	VARCHAR	Customer name
`issued_to`	VARCHAR	Optional issued-to name
`plan`	VARCHAR	References `license_plans.slug` (logical FK)
`features_json`	JSON	Active feature list
`max_members`	INT	Plan limit
`max_cases`	INT	Plan limit
`issued_at`	DATETIME
`expires_at`	DATETIME	NULL = never expires
`offline_allowed`	TINYINT(1)	Allow offline license generation
`grace_days`	INT	Override plan default grace days
`is_active`	TINYINT(1)	1=active
`notes`	TEXT	Admin notes
`created_at`	DATETIME
`updated_at`	DATETIME

Indexes: PRIMARY, UNIQUE(license_key), idx(expires_at), idx(is_active, expires_at)

`license_devices` — Activated devices

Column	Type	Notes
---	---	---
`id`	BIGINT UNSIGNED PK
`license_id`	BIGINT UNSIGNED FK→licenses(id)	CASCADE DELETE
`activation_code_id`	BIGINT UNSIGNED	FK to activation_codes
`machine_code`	VARCHAR	1-128 chars `[A-Za-z0-9_\-.]+`
`machine_fingerprint`	VARCHAR	Format `sha256:[a-f0-9]{64}`
`device_name`	VARCHAR
`os`	VARCHAR
`client_version`	VARCHAR
`first_activated_at`	DATETIME
`last_seen_at`	DATETIME	Updated on each activation
`is_revoked`	TINYINT(1)	1=revoked (device blocked)
`notes`	TEXT
`created_at`	DATETIME
`updated_at`	DATETIME

Indexes: PRIMARY, UNIQUE(license_id, machine_fingerprint) (uniq_license_machine), idx(license_id, is_revoked), idx(machine_fingerprint)

`activation_logs` — Activation audit trail

Column	Type	Notes
---	---	---
`id`	BIGINT UNSIGNED PK
`license_id`	BIGINT UNSIGNED FK→licenses(id)
`activation_code`	VARCHAR	The code presented
`machine_fingerprint`	VARCHAR
`machine_code`	VARCHAR
`ip`	VARCHAR	Client IP
`user_agent`	VARCHAR	Client User-Agent
`result`	ENUM	'success', 'failed', 'error'
`message`	TEXT	Reason / status
`license_format`	VARCHAR	'v1' or 'v2'
`request_json`	JSON	Original request (debug)
`created_at`	DATETIME

Indexes: PRIMARY, idx(license_id, created_at), idx(created_at)

`license_product_authorizations` — Per-product feature gates

Column	Type	Notes
---	---	---
`id`	BIGINT UNSIGNED PK
`license_id`	BIGINT UNSIGNED FK→licenses(id)	CASCADE DELETE
`product_slug`	VARCHAR(64)
`authorized_at`	DATETIME
`expires_at`	DATETIME	NULL = perpetual
`is_active`	TINYINT(1)
`created_at`	DATETIME
`updated_at`	DATETIME

Indexes: PRIMARY, UNIQUE(license_id, product_slug), idx(license_id, product_slug)

`license_authorization_tokens` — Per-product token grants

Column	Type	Notes
---	---	---
`id`	BIGINT UNSIGNED PK
`token_hash`	CHAR(64) UNIQUE	SHA-256 hex of plaintext token
`license_id`	BIGINT UNSIGNED FK→licenses(id)	CASCADE DELETE
`product_slug`	VARCHAR(64)
`name`	VARCHAR(128)	Label
`is_active`	TINYINT(1)
`expires_at`	DATETIME
`last_used_at`	DATETIME
`created_at`	DATETIME
`updated_at`	DATETIME

Indexes: PRIMARY, UNIQUE(token_hash), idx(license_id), idx(active)

`license_plans` — Plan definitions

Column	Type	Notes
---	---	---
`id`	BIGINT UNSIGNED PK
`slug`	VARCHAR(64) UNIQUE	e.g., 'team', 'enterprise'
`name`	VARCHAR(128)	Display name
`description`	TEXT
`features_json`	JSON	Feature list
`max_members`	INT
`max_cases`	INT
`grace_days`	INT	Days after expiry during which license still validates
`is_active`	TINYINT(1)
`sort_order`	INT	Display order
`created_at`	DATETIME
`updated_at`	DATETIME

Indexes: PRIMARY, UNIQUE(slug)

`certificates` — X.509 client certificates

Column	Type	Notes
---	---	---
`id`	BIGINT UNSIGNED PK
`serial`	VARCHAR(64) UNIQUE	X.509 serial in hex
`cert_pem`	TEXT	Public certificate
`key_pem_encrypted`	TEXT	AES-256-CBC encrypted private key
`owner_license_id`	BIGINT UNSIGNED FK→licenses(id)	NULL = orphan
`owner_name`	VARCHAR(255)	CN in cert
`status`	ENUM	'active', 'revoked', 'expired'
`created_at`	DATETIME
`expires_at`	DATETIME	X.509 notAfter
`revoked_at`	DATETIME
`created_by_admin_id`	BIGINT UNSIGNED FK→admins(id)
`key_download_token`	VARCHAR(64)	Single-use, NULL after consumption
`key_download_expires_at`	DATETIME	30 min from issuance

Indexes: PRIMARY, UNIQUE(serial), UNIQUE(key_download_token), idx(owner_license_id), idx(status), idx(expires_at)

`license_events` — Outbound event log

Column	Type	Notes
---	---	---
`id`	BIGINT UNSIGNED PK
`license_id`	BIGINT UNSIGNED FK→licenses(id)
`event_type`	VARCHAR(64)	e.g., 'license.activated'
`payload_json`	JSON	Event data
`delivered`	TINYINT(1)	1 = delivered to at least one webhook
`delivered_at`	DATETIME
`retry_count`	INT	Retry attempts (max 5)
`last_error`	TEXT	Last delivery error
`created_at`	DATETIME

Indexes: PRIMARY, idx(license_id), idx(event_type), idx(created_at), idx(delivered, retry_count)

`license_webhooks` — Webhook subscriptions

Column	Type	Notes
---	---	---
`id`	BIGINT UNSIGNED PK
`url`	VARCHAR(512)	Consumer URL
`secret`	VARCHAR(128)	HMAC shared secret (shown once)
`scope`	VARCHAR(32)	'all' or specific event type
`is_active`	TINYINT(1)
`created_by_admin_id`	BIGINT UNSIGNED
`created_at`	DATETIME
`updated_at`	DATETIME

Indexes: PRIMARY, idx(is_active)

`api_tokens` — API authentication tokens

Column	Type	Notes
---	---	---
`id`	BIGINT UNSIGNED PK
`name`	VARCHAR	Label
`token_hash`	VARCHAR UNIQUE	SHA-256 of plaintext
`scope`	VARCHAR	'license' for license module
`expires_at`	DATETIME	NULL = no expiry
`last_used_at`	DATETIME
`is_active`	TINYINT(1)
`created_at`	DATETIME
`updated_at`	DATETIME

`products` — Product catalog

Column	Type	Notes
---	---	---
`id`	BIGINT UNSIGNED PK
`slug`	VARCHAR UNIQUE	e.g., 'CoForen'
`name`	VARCHAR
`default_version`	VARCHAR
`default_features_json`	JSON
`default_grace_days`	INT
`is_active`	TINYINT(1)
`notes`	TEXT
`created_at`	DATETIME
`updated_at`	DATETIME

`admins` — Admin users

Column	Type	Notes
---	---	---
`id`	BIGINT UNSIGNED PK
`username`	VARCHAR UNIQUE
`password_hash`	VARCHAR	bcrypt
`is_active`	TINYINT(1)
`last_login_at`	DATETIME
`created_at`	DATETIME
`updated_at`	DATETIME
`totp_secret`	VARCHAR(255)	AES-256-GCM ciphertext of the TOTP secret; `NULL` = 2FA disabled (migration 012)
`failed_logins`	INT UNSIGNED	Consecutive failed password attempts (migration 012)
`locked_until`	DATETIME	Account locked until this UTC time; `NULL` = not locked (migration 012)
`failed_login_at`	DATETIME	Timestamp of the last failed attempt (migration 012)

`admin_audit_logs` — Admin action audit

Column	Type	Notes
---	---	---
`id`	BIGINT UNSIGNED PK
`admin_id`	BIGINT UNSIGNED
`action`	VARCHAR(64)	e.g., 'login', 'revoke', 'create', 'totp_enable', 'use_backup_code'
`resource_type`	VARCHAR(64)	e.g., 'certificate', 'license'
`resource_id`	VARCHAR
`detail_json`	JSON	Additional context
`ip`	VARCHAR
`created_at`	DATETIME

`admin_sessions` — Admin session registry (migration 012)

Backs the session list and revocation UI on System → Security.

Column	Type	Notes
---	---	---
`id`	VARCHAR(64) PK	SHA-256 hex of the PHP session id; the raw id is never stored
`admin_id`	INT UNSIGNED FK	Owning administrator (`ON DELETE CASCADE`)
`ip`	VARCHAR(45)	Client IP at login (fits IPv6)
`user_agent`	VARCHAR(512)	Browser / client fingerprint
`created_at`	DATETIME	Login time
`last_active_at`	DATETIME	Last observed request
`revoked_at`	DATETIME	`NULL` while active; set on revocation (kept for audit)

Indexes: idx_admin_id (admin_id), idx_revoked (revoked_at).

`admin_2fa_backup_codes` — Single-use 2FA backup codes (migration 012)

Column	Type	Notes
---	---	---
`id`	INT UNSIGNED PK
`admin_id`	INT UNSIGNED FK	Owning administrator (`ON DELETE CASCADE`)
`code_hash`	CHAR(64)	SHA-256 hex of the normalised code (`XXXX-XXXX-XXXX-XXXX`, upper-cased, separators stripped)
`created_at`	DATETIME	Issuance time
`used_at`	DATETIME	`NULL` = unused; set on first redemption
`used_ip`	VARCHAR(45)	IP that redeemed the code

Only the hash is stored, so a database leak does not yield usable codes. Redemption is a conditional UPDATE ... WHERE used_at IS NULL, so concurrent requests cannot both succeed with the same code.

Index: idx_admin_unused (admin_id, used_at).

Migrations

File	Purpose
---	---
`001_mysql.sql`	Initial MySQL schema (licenses, devices, activations)
`002_signature_compat.sql`	Adds machine_code column for v2 signature
`003_batch_v2_admin.sql`	Adds batch_no for batch operations
`004_products.sql`	Product registry (legacy path; superseded by 005)
`005_unified_catalog.sql`	Adds product catalog tables
`006_license_product_authorizations.sql`	Adds `license_product_authorizations`
`007_certificates.sql`	Adds `certificates` table
`008_license_plans.sql`	Adds `license_plans` table
`009_license_events_webhooks.sql`	Adds `license_events` + `license_webhooks`
`010_license_authorization_tokens.sql`	Adds `license_authorization_tokens`
`011_security_and_performance.sql`	Adds `certificates.key_download_token/expires_at` columns and performance indexes (idempotent)
`012_admin_security.sql`	Adds `admins` 2FA/lockout columns, `admin_sessions`, `admin_2fa_backup_codes`

Running migrations

Migrations are SQL files in license/migrations/. Run them in numeric order (skipping legacy/, which holds the superseded 004_products.sql):

cd /www/wwwroot/foren.dev
php license/scripts/init.php          # preferred: skips 004, records what it ran

Manual equivalent:

for f in license/migrations/[0-9][0-9][0-9]_*.sql; do
  [ -f "$f" ] || continue
  case "$(basename "$f")" in 004_*) continue ;; esac
  echo "== $f"
  mysql -u '<DB_USER>' -p '<DB>' < "$f" || break
done

Migration 011 is idempotent (safe to run multiple times).

Migration 012 is only partially idempotent: its `CREATE TABLE IF NOT EXISTS statements are safe to re-run, but the ALTER TABLE admins` block at the top adds columns unconditionally and will fail with Duplicate column name on a database that already has them. Check first:
SELECT column_name FROM information_schema.columns
 WHERE table_schema = DATABASE() AND table_name = 'admins'
   AND column_name IN ('totp_secret','failed_logins','locked_until','failed_login_at');
If all four rows come back, the ALTER block is already applied — comment it out, or use php license/scripts/init.php, which checks column presence before altering.

Backup Strategy

Automated backups are handled by license/scripts/backup.php (see DEPLOYMENT.md for the cron entry). It dumps MySQL, the legacy SQLite database and the signing keys, and prunes old copies.

The script refuses to guess the database name. With FOREN_DB_NAME / FOREN_DB_USER unset it exits 2 and writes nothing — this is deliberate. Pass them (or source an env file) before running it.

Manual equivalent, if you need a one-off dump:

mysqldump -u '<DB_USER>' -p '<DB>' \
  | gzip > /backup/foren/<DB>-$(date +%Y-%m-%d).sql.gz

# Rotation (keep 7 days)
find /backup/foren -name '<DB>-*.sql.gz' -mtime +7 -delete

Restore:

gunzip -c /backup/foren/<DB>-YYYY-MM-DD.sql.gz \
  | mysql -u '<DB_USER>' -p '<DB>'

Database Schema

Entity-Relationship Diagram (ASCII)

Tables

licenses — Core license records

license_devices — Activated devices

activation_logs — Activation audit trail

license_product_authorizations — Per-product feature gates

license_authorization_tokens — Per-product token grants

license_plans — Plan definitions

certificates — X.509 client certificates

license_events — Outbound event log

license_webhooks — Webhook subscriptions

api_tokens — API authentication tokens

products — Product catalog

admins — Admin users

admin_audit_logs — Admin action audit

admin_sessions — Admin session registry (migration 012)

admin_2fa_backup_codes — Single-use 2FA backup codes (migration 012)