Skip to content

DB_SCHEMA.md

Latest commit

 

History

History
142 lines (103 loc) · 4.72 KB

DB_SCHEMA.md

File metadata and controls

142 lines (103 loc) · 4.72 KB
Raw

PKI Database Schema

This document describes the PKI database model as of the current codebase.

Source of truth:

  • Schema DDL: backend/schema/pki_schema.sql
  • Runtime usage: backend/db.py, backend/app.py

Snapshot

Tables

Table Notes
organizations Created/read/listed by app.
certificates Primary certificate metadata store; issuance + revocation state updates.
certificate_audit_log Issuance, revocation, renewal, download, and password-view flows write audit records.
subject_alternative_names Populated on cert creation; read by popup with PEM fallback for legacy certs.
certificate_extensions Generic extension storage; populated on cert creation for audit trail.
basic_constraints Normalized extension storage; populated on cert creation, read by popup.
key_usage Normalized extension storage; populated on cert creation, read by popup.
extended_key_usage Normalized extension storage; populated on cert creation, read by popup.
crls Populated during revocation after CRL generation succeeds.
revoked_certificates Populated during revocation; links CRL history to revoked certs.

Views

View Notes
certificate_summary Used by dashboard statistics queries in backend/db.py.

Indexes

Index Group Notes
certificates indexes Useful for current read paths by org/type/status/issuer.
Extension table indexes Present for extension-related tables.
Audit table indexes Present for audit-log access patterns.

Data Model (What app actually relies on)

1. organizations

Stores organization identity and folder mapping.

  • Used by:
    • create organization
    • list organizations
    • fetch by id / by directory

2. certificates

Stores all issued cert metadata for:

  • root
  • intermediate
  • server
  • client
  • email

Actively used for:

  • certificate listing and dashboard stats
  • chain relation via issuer_cert_id
  • revocation status (status, revoked_at, revocation_reason)
  • expiring certificate queries

3. certificate_audit_log

  • db.log_certificate_operation(...) is actively called by certificate lifecycle and download flows.
  • Current app flows record create, renew, revoke, artifact download, and PKCS#12 password view events.

Extension and CRL Tables

The following extension and CRL tables are used by the current application:

  • subject_alternative_names — populated on cert creation; read by popup with PEM fallback
  • certificate_extensions — generic extension audit trail
  • basic_constraints — normalized extension storage
  • key_usage — normalized extension storage
  • extended_key_usage — normalized extension storage with OID→name mapping
  • crls — CRL metadata including generation timestamp and next update time
  • revoked_certificates — per-CRL revocation history with linkage to revoked certs

Impact: Certificate extension details (SAN, KU, EKU, BasicConstraints) are now persisted to DB during issuance and retrieved preferentially by popup/metadata routes (with PEM parse fallback for legacy certs). CRL artifacts are now tracked relationally, not just on disk.

Current App-Safe Query Examples

These examples reflect objects actively used today.

List certificates for one org

SELECT c.id, c.cert_name, c.cert_type, c.issuer_cert_id, c.status, c.not_after,
       issuer.cert_type AS issuer_cert_type
FROM certificates c
LEFT JOIN certificates issuer ON c.issuer_cert_id = issuer.id
WHERE c.organization_id = :org_id
ORDER BY c.created_at DESC, c.id DESC;

Note: The issuer_cert_type field is included to enable UI-level logic (e.g., showing password fields for root-issued certificates). This join is optional for filtering queries but recommended for dashboard/listing queries.

Check chain linkage quality

SELECT cert_type,
       COUNT(*) AS total,
       SUM(CASE WHEN issuer_cert_id IS NULL THEN 1 ELSE 0 END) AS missing_issuer
FROM certificates
GROUP BY cert_type;

Find revoked certificates signed by one issuer

SELECT id, serial_number, revoked_at, revocation_reason
FROM certificates
WHERE issuer_cert_id = :issuer_cert_id
  AND status = 'revoked'
ORDER BY revoked_at;

Organization and certificate counts (health-like)

SELECT (SELECT COUNT(*) FROM organizations) AS organizations,
       (SELECT COUNT(*) FROM certificates) AS certificates;

Notes for Contributors

  • If you add writes to new tables, update this document in the same PR.
  • If you switch app logic to read from views, update the relevant section here.
  • Keep this doc aligned with both backend/schema/pki_schema.sql and runtime behavior in backend/db.py / backend/app.py.