kipuka/db/
schema.rs

1//! Embedded database migrations and schema version tracking.
2//!
3//! Migrations are defined as SQL strings and executed in order at startup
4//! when `[database].run_migrations = true`.  The `schema_version` table
5//! tracks which migrations have been applied.
6//!
7//! Each migration is idempotent: `CREATE TABLE IF NOT EXISTS` and
8//! `CREATE INDEX IF NOT EXISTS` are used throughout so that re-running
9//! migrations on an already-initialized database is a no-op.
10//!
11//! Dialect-specific constants are selected at runtime based on [`DbKind`].
12
13use crate::db::DbKind;
14use crate::error::KipukaError;
15
16/// Current schema version.  Increment this when adding new migrations.
17pub const SCHEMA_VERSION: i32 = 6;
18
19// ---------------------------------------------------------------------------
20// SQLite migration v1
21// ---------------------------------------------------------------------------
22const MIGRATION_V1_SQLITE: &str = r#"
23-- Schema version tracking
24CREATE TABLE IF NOT EXISTS schema_version (
25    version     INTEGER NOT NULL,
26    applied_at  TEXT    NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%SZ', 'now'))
27);
28
29-- Certificate inventory
30CREATE TABLE IF NOT EXISTS certificates (
31    id              INTEGER PRIMARY KEY AUTOINCREMENT,
32    serial          TEXT    NOT NULL UNIQUE,
33    subject_dn      TEXT    NOT NULL,
34    issuer_dn       TEXT    NOT NULL,
35    not_before      TEXT    NOT NULL,
36    not_after       TEXT    NOT NULL,
37    der_encoded     BLOB   NOT NULL,
38    ca_id           TEXT    NOT NULL,
39    profile         TEXT,
40    status          TEXT    NOT NULL DEFAULT 'active'
41                           CHECK (status IN ('active', 'revoked', 'expired')),
42    revocation_reason TEXT,
43    revocation_time TEXT,
44    created_at      TEXT    NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%SZ', 'now'))
45);
46
47CREATE INDEX IF NOT EXISTS idx_certificates_serial ON certificates (serial);
48CREATE INDEX IF NOT EXISTS idx_certificates_subject_dn ON certificates (subject_dn);
49CREATE INDEX IF NOT EXISTS idx_certificates_ca_id ON certificates (ca_id);
50CREATE INDEX IF NOT EXISTS idx_certificates_status ON certificates (status);
51CREATE INDEX IF NOT EXISTS idx_certificates_not_after ON certificates (not_after);
52
53-- OTP storage (DB backend)
54CREATE TABLE IF NOT EXISTS otp_tokens (
55    id              INTEGER PRIMARY KEY AUTOINCREMENT,
56    token_hash      TEXT    NOT NULL,
57    entity_id       TEXT    NOT NULL,
58    label           TEXT,
59    profile         TEXT,
60    created_at      TEXT    NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
61    expires_at      TEXT    NOT NULL,
62    max_uses        INTEGER NOT NULL DEFAULT 1,
63    current_uses    INTEGER NOT NULL DEFAULT 0,
64    revoked         INTEGER NOT NULL DEFAULT 0,
65    revoked_at      TEXT
66);
67
68CREATE INDEX IF NOT EXISTS idx_otp_tokens_entity_id ON otp_tokens (entity_id);
69CREATE INDEX IF NOT EXISTS idx_otp_tokens_expires_at ON otp_tokens (expires_at);
70CREATE INDEX IF NOT EXISTS idx_otp_tokens_hash ON otp_tokens (entity_id, token_hash);
71
72-- Audit event trail (NIAP CA PP FAU_GEN.1)
73CREATE TABLE IF NOT EXISTS audit_events (
74    id              INTEGER PRIMARY KEY AUTOINCREMENT,
75    timestamp       TEXT    NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%f000Z', 'now')),
76    event_type      TEXT    NOT NULL,
77    actor           TEXT,
78    target          TEXT,
79    detail_json     TEXT,
80    source_ip       TEXT,
81    session_id      TEXT
82);
83
84CREATE INDEX IF NOT EXISTS idx_audit_events_timestamp ON audit_events (timestamp);
85CREATE INDEX IF NOT EXISTS idx_audit_events_event_type ON audit_events (event_type);
86CREATE INDEX IF NOT EXISTS idx_audit_events_actor ON audit_events (actor);
87
88-- CA health tracking
89CREATE TABLE IF NOT EXISTS ca_health (
90    id                    INTEGER PRIMARY KEY AUTOINCREMENT,
91    ca_id                 TEXT    NOT NULL UNIQUE,
92    status                TEXT    NOT NULL DEFAULT 'unknown'
93                                 CHECK (status IN ('healthy', 'unhealthy', 'unknown')),
94    last_check            TEXT,
95    last_success          TEXT,
96    last_failure          TEXT,
97    consecutive_failures  INTEGER NOT NULL DEFAULT 0,
98    response_latency_ms   INTEGER
99);
100
101CREATE INDEX IF NOT EXISTS idx_ca_health_ca_id ON ca_health (ca_id);
102
103-- Enrollment request log
104CREATE TABLE IF NOT EXISTS enrollment_requests (
105    id              INTEGER PRIMARY KEY AUTOINCREMENT,
106    request_type    TEXT    NOT NULL
107                           CHECK (request_type IN ('enroll', 'reenroll', 'serverkeygen', 'fullcmc')),
108    csr_hash        TEXT    NOT NULL,
109    ca_id           TEXT    NOT NULL,
110    label           TEXT,
111    auth_method     TEXT    NOT NULL,
112    entity_id       TEXT,
113    status          TEXT    NOT NULL DEFAULT 'pending'
114                           CHECK (status IN ('pending', 'issued', 'rejected')),
115    certificate_id  INTEGER REFERENCES certificates(id),
116    created_at      TEXT    NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
117    completed_at    TEXT
118);
119
120CREATE INDEX IF NOT EXISTS idx_enrollment_requests_status ON enrollment_requests (status);
121CREATE INDEX IF NOT EXISTS idx_enrollment_requests_ca_id ON enrollment_requests (ca_id);
122CREATE INDEX IF NOT EXISTS idx_enrollment_requests_entity_id ON enrollment_requests (entity_id);
123CREATE INDEX IF NOT EXISTS idx_enrollment_requests_csr_hash ON enrollment_requests (csr_hash);
124
125-- Server-generated keys
126CREATE TABLE IF NOT EXISTS server_generated_keys (
127    id              INTEGER PRIMARY KEY AUTOINCREMENT,
128    enrollment_id   INTEGER NOT NULL REFERENCES enrollment_requests(id),
129    key_type        TEXT    NOT NULL,
130    key_size        INTEGER NOT NULL,
131    archived        INTEGER NOT NULL DEFAULT 0,
132    archive_id      TEXT,
133    created_at      TEXT    NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%SZ', 'now'))
134);
135
136CREATE INDEX IF NOT EXISTS idx_server_generated_keys_enrollment_id ON server_generated_keys (enrollment_id);
137
138-- STAR (Short-Term Automatic Renewal) tables (RFC 8739)
139CREATE TABLE IF NOT EXISTS star_orders (
140    id                  TEXT    PRIMARY KEY,
141    subject_dn          TEXT    NOT NULL,
142    key_type            TEXT    NOT NULL,
143    profile             TEXT    NOT NULL,
144    renewal_interval_secs INTEGER NOT NULL,
145    lifetime_end        TEXT    NOT NULL,
146    max_renewals        INTEGER NOT NULL,
147    current_renewals    INTEGER NOT NULL DEFAULT 0,
148    status              TEXT    NOT NULL DEFAULT 'active'
149                               CHECK (status IN ('active', 'cancelled', 'completed', 'expired')),
150    requestor_dn        TEXT,
151    ca_id               TEXT    NOT NULL,
152    csr_der             BLOB   NOT NULL,
153    created_at          TEXT    NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
154    cancelled_at        TEXT
155);
156
157CREATE TABLE IF NOT EXISTS star_certificates (
158    id              INTEGER PRIMARY KEY AUTOINCREMENT,
159    star_order_id   TEXT    NOT NULL REFERENCES star_orders(id),
160    serial_number   TEXT    NOT NULL,
161    certificate_der BLOB   NOT NULL,
162    not_before      TEXT    NOT NULL,
163    not_after       TEXT    NOT NULL,
164    renewal_number  INTEGER NOT NULL,
165    created_at      TEXT    NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%SZ', 'now'))
166);
167
168CREATE INDEX IF NOT EXISTS idx_star_certs_order ON star_certificates(star_order_id, renewal_number DESC);
169"#;
170
171// ---------------------------------------------------------------------------
172// PostgreSQL migration v1
173// ---------------------------------------------------------------------------
174const MIGRATION_V1_POSTGRES: &str = r#"
175-- Schema version tracking
176CREATE TABLE IF NOT EXISTS schema_version (
177    version     INTEGER NOT NULL,
178    applied_at  TEXT NOT NULL DEFAULT (to_char(NOW(), 'YYYY-MM-DD"T"HH24:MI:SS"Z"'))
179);
180
181-- Enum types
182DO $$ BEGIN
183    CREATE TYPE certificate_status AS ENUM ('active', 'revoked', 'expired');
184EXCEPTION WHEN duplicate_object THEN NULL;
185END $$;
186
187DO $$ BEGIN
188    CREATE TYPE enrollment_type AS ENUM ('enroll', 'reenroll', 'serverkeygen', 'fullcmc');
189EXCEPTION WHEN duplicate_object THEN NULL;
190END $$;
191
192DO $$ BEGIN
193    CREATE TYPE enrollment_status AS ENUM ('pending', 'issued', 'rejected');
194EXCEPTION WHEN duplicate_object THEN NULL;
195END $$;
196
197DO $$ BEGIN
198    CREATE TYPE ca_health_status AS ENUM ('healthy', 'unhealthy', 'unknown');
199EXCEPTION WHEN duplicate_object THEN NULL;
200END $$;
201
202DO $$ BEGIN
203    CREATE TYPE star_order_status AS ENUM ('active', 'cancelled', 'completed', 'expired');
204EXCEPTION WHEN duplicate_object THEN NULL;
205END $$;
206
207-- Certificate inventory
208CREATE TABLE IF NOT EXISTS certificates (
209    id                BIGSERIAL    PRIMARY KEY,
210    serial            TEXT         NOT NULL UNIQUE,
211    subject_dn        TEXT         NOT NULL,
212    issuer_dn         TEXT         NOT NULL,
213    not_before        TEXT  NOT NULL,
214    not_after         TEXT  NOT NULL,
215    der_encoded       BYTEA        NOT NULL,
216    ca_id             TEXT         NOT NULL,
217    profile           TEXT,
218    status            certificate_status NOT NULL DEFAULT 'active',
219    revocation_reason TEXT,
220    revocation_time   TEXT,
221    created_at        TEXT  NOT NULL DEFAULT (to_char(NOW(), 'YYYY-MM-DD"T"HH24:MI:SS"Z"'))
222);
223
224CREATE INDEX IF NOT EXISTS idx_certificates_serial ON certificates (serial);
225CREATE INDEX IF NOT EXISTS idx_certificates_subject_dn ON certificates (subject_dn);
226CREATE INDEX IF NOT EXISTS idx_certificates_ca_id ON certificates (ca_id);
227CREATE INDEX IF NOT EXISTS idx_certificates_status ON certificates (status);
228CREATE INDEX IF NOT EXISTS idx_certificates_not_after ON certificates (not_after);
229
230-- OTP storage
231CREATE TABLE IF NOT EXISTS otp_tokens (
232    id              BIGSERIAL    PRIMARY KEY,
233    token_hash      TEXT         NOT NULL,
234    entity_id       TEXT         NOT NULL,
235    label           TEXT,
236    profile         TEXT,
237    created_at      TEXT  NOT NULL DEFAULT (to_char(NOW(), 'YYYY-MM-DD"T"HH24:MI:SS"Z"')),
238    expires_at      TEXT  NOT NULL,
239    max_uses        INTEGER      NOT NULL DEFAULT 1,
240    current_uses    INTEGER      NOT NULL DEFAULT 0,
241    revoked         BOOLEAN      NOT NULL DEFAULT FALSE,
242    revoked_at      TEXT
243);
244
245CREATE INDEX IF NOT EXISTS idx_otp_tokens_entity_id ON otp_tokens (entity_id);
246CREATE INDEX IF NOT EXISTS idx_otp_tokens_expires_at ON otp_tokens (expires_at);
247CREATE INDEX IF NOT EXISTS idx_otp_tokens_hash ON otp_tokens (entity_id, token_hash);
248
249-- Audit event trail
250CREATE TABLE IF NOT EXISTS audit_events (
251    id              BIGSERIAL    PRIMARY KEY,
252    timestamp       TEXT  NOT NULL DEFAULT (to_char(NOW(), 'YYYY-MM-DD"T"HH24:MI:SS"Z"')),
253    event_type      TEXT         NOT NULL,
254    actor           TEXT,
255    target          TEXT,
256    detail_json     TEXT,
257    source_ip       TEXT,
258    session_id      TEXT
259);
260
261CREATE INDEX IF NOT EXISTS idx_audit_events_timestamp ON audit_events (timestamp);
262CREATE INDEX IF NOT EXISTS idx_audit_events_event_type ON audit_events (event_type);
263CREATE INDEX IF NOT EXISTS idx_audit_events_actor ON audit_events (actor);
264
265-- CA health tracking
266CREATE TABLE IF NOT EXISTS ca_health (
267    id                    BIGSERIAL        PRIMARY KEY,
268    ca_id                 TEXT             NOT NULL UNIQUE,
269    status                ca_health_status NOT NULL DEFAULT 'unknown',
270    last_check            TEXT,
271    last_success          TEXT,
272    last_failure          TEXT,
273    consecutive_failures  INTEGER          NOT NULL DEFAULT 0,
274    response_latency_ms   INTEGER
275);
276
277CREATE INDEX IF NOT EXISTS idx_ca_health_ca_id ON ca_health (ca_id);
278
279-- Enrollment request log
280CREATE TABLE IF NOT EXISTS enrollment_requests (
281    id              BIGSERIAL         PRIMARY KEY,
282    request_type    enrollment_type   NOT NULL,
283    csr_hash        TEXT              NOT NULL,
284    ca_id           TEXT              NOT NULL,
285    label           TEXT,
286    auth_method     TEXT              NOT NULL,
287    entity_id       TEXT,
288    status          enrollment_status NOT NULL DEFAULT 'pending',
289    certificate_id  BIGINT            REFERENCES certificates(id),
290    created_at      TEXT       NOT NULL DEFAULT (to_char(NOW(), 'YYYY-MM-DD"T"HH24:MI:SS"Z"')),
291    completed_at    TEXT
292);
293
294CREATE INDEX IF NOT EXISTS idx_enrollment_requests_status ON enrollment_requests (status);
295CREATE INDEX IF NOT EXISTS idx_enrollment_requests_ca_id ON enrollment_requests (ca_id);
296CREATE INDEX IF NOT EXISTS idx_enrollment_requests_entity_id ON enrollment_requests (entity_id);
297CREATE INDEX IF NOT EXISTS idx_enrollment_requests_csr_hash ON enrollment_requests (csr_hash);
298
299-- Server-generated keys
300CREATE TABLE IF NOT EXISTS server_generated_keys (
301    id              BIGSERIAL    PRIMARY KEY,
302    enrollment_id   BIGINT       NOT NULL REFERENCES enrollment_requests(id),
303    key_type        TEXT         NOT NULL,
304    key_size        INTEGER      NOT NULL,
305    archived        BOOLEAN      NOT NULL DEFAULT FALSE,
306    archive_id      TEXT,
307    created_at      TEXT  NOT NULL DEFAULT (to_char(NOW(), 'YYYY-MM-DD"T"HH24:MI:SS"Z"'))
308);
309
310CREATE INDEX IF NOT EXISTS idx_server_generated_keys_enrollment_id ON server_generated_keys (enrollment_id);
311
312-- STAR (Short-Term Automatic Renewal) tables (RFC 8739)
313CREATE TABLE IF NOT EXISTS star_orders (
314    id                    TEXT             PRIMARY KEY,
315    subject_dn            TEXT             NOT NULL,
316    key_type              TEXT             NOT NULL,
317    profile               TEXT             NOT NULL,
318    renewal_interval_secs INTEGER          NOT NULL,
319    lifetime_end          TEXT      NOT NULL,
320    max_renewals          INTEGER          NOT NULL,
321    current_renewals      INTEGER          NOT NULL DEFAULT 0,
322    status                star_order_status NOT NULL DEFAULT 'active',
323    requestor_dn          TEXT,
324    ca_id                 TEXT             NOT NULL,
325    csr_der               BYTEA            NOT NULL,
326    created_at            TEXT      NOT NULL DEFAULT (to_char(NOW(), 'YYYY-MM-DD"T"HH24:MI:SS"Z"')),
327    cancelled_at          TEXT
328);
329
330CREATE TABLE IF NOT EXISTS star_certificates (
331    id              BIGSERIAL    PRIMARY KEY,
332    star_order_id   TEXT         NOT NULL REFERENCES star_orders(id),
333    serial_number   TEXT         NOT NULL,
334    certificate_der BYTEA        NOT NULL,
335    not_before      TEXT  NOT NULL,
336    not_after       TEXT  NOT NULL,
337    renewal_number  INTEGER      NOT NULL,
338    created_at      TEXT  NOT NULL DEFAULT (to_char(NOW(), 'YYYY-MM-DD"T"HH24:MI:SS"Z"'))
339);
340
341CREATE INDEX IF NOT EXISTS idx_star_certs_order ON star_certificates(star_order_id, renewal_number DESC);
342"#;
343
344// ---------------------------------------------------------------------------
345// MariaDB migration v1
346// ---------------------------------------------------------------------------
347const MIGRATION_V1_MARIADB: &str = r#"
348-- Schema version tracking
349CREATE TABLE IF NOT EXISTS schema_version (
350    version     INT          NOT NULL,
351    applied_at  TEXT  NOT NULL DEFAULT (DATE_FORMAT(NOW(6), '%Y-%m-%dT%H:%i:%S.%fZ'))
352) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
353
354-- Certificate inventory
355CREATE TABLE IF NOT EXISTS certificates (
356    id                BIGINT       AUTO_INCREMENT PRIMARY KEY,
357    serial            VARCHAR(255) NOT NULL UNIQUE,
358    subject_dn        TEXT         NOT NULL,
359    issuer_dn         TEXT         NOT NULL,
360    not_before        TEXT  NOT NULL,
361    not_after         TEXT  NOT NULL,
362    der_encoded       LONGBLOB     NOT NULL,
363    ca_id             VARCHAR(255) NOT NULL,
364    profile           VARCHAR(255),
365    status            ENUM('active', 'revoked', 'expired') NOT NULL DEFAULT 'active',
366    revocation_reason VARCHAR(255),
367    revocation_time   TEXT,
368    created_at        TEXT  NOT NULL DEFAULT (DATE_FORMAT(NOW(6), '%Y-%m-%dT%H:%i:%S.%fZ'))
369) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
370
371CREATE INDEX IF NOT EXISTS idx_certificates_serial ON certificates (serial);
372CREATE INDEX IF NOT EXISTS idx_certificates_subject_dn ON certificates (subject_dn(255));
373CREATE INDEX IF NOT EXISTS idx_certificates_ca_id ON certificates (ca_id);
374CREATE INDEX IF NOT EXISTS idx_certificates_status ON certificates (status);
375CREATE INDEX IF NOT EXISTS idx_certificates_not_after ON certificates (not_after);
376
377-- OTP storage
378CREATE TABLE IF NOT EXISTS otp_tokens (
379    id              BIGINT       AUTO_INCREMENT PRIMARY KEY,
380    token_hash      VARCHAR(512)         NOT NULL,
381    entity_id       VARCHAR(255) NOT NULL,
382    label           VARCHAR(255),
383    profile         VARCHAR(255),
384    created_at      VARCHAR(64)  NOT NULL DEFAULT (DATE_FORMAT(NOW(6), '%Y-%m-%dT%H:%i:%S.%fZ')),
385    expires_at      VARCHAR(64)  NOT NULL,
386    max_uses        INT          NOT NULL DEFAULT 1,
387    current_uses    INT          NOT NULL DEFAULT 0,
388    revoked         BOOLEAN      NOT NULL DEFAULT FALSE,
389    revoked_at      VARCHAR(64)
390) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
391
392CREATE INDEX IF NOT EXISTS idx_otp_tokens_entity_id ON otp_tokens (entity_id);
393CREATE INDEX IF NOT EXISTS idx_otp_tokens_expires_at ON otp_tokens (expires_at);
394CREATE INDEX IF NOT EXISTS idx_otp_tokens_hash ON otp_tokens (entity_id, token_hash);
395
396-- Audit event trail
397CREATE TABLE IF NOT EXISTS audit_events (
398    id              BIGINT       AUTO_INCREMENT PRIMARY KEY,
399    timestamp       TEXT  NOT NULL DEFAULT (DATE_FORMAT(NOW(6), '%Y-%m-%dT%H:%i:%S.%fZ')),
400    event_type      VARCHAR(255) NOT NULL,
401    actor           VARCHAR(255),
402    target          VARCHAR(255),
403    detail_json     TEXT,
404    source_ip       VARCHAR(45),
405    session_id      VARCHAR(255)
406) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
407
408CREATE INDEX IF NOT EXISTS idx_audit_events_timestamp ON audit_events (timestamp);
409CREATE INDEX IF NOT EXISTS idx_audit_events_event_type ON audit_events (event_type);
410CREATE INDEX IF NOT EXISTS idx_audit_events_actor ON audit_events (actor);
411
412-- CA health tracking
413CREATE TABLE IF NOT EXISTS ca_health (
414    id                    BIGINT       AUTO_INCREMENT PRIMARY KEY,
415    ca_id                 VARCHAR(255) NOT NULL UNIQUE,
416    status                ENUM('healthy', 'unhealthy', 'unknown') NOT NULL DEFAULT 'unknown',
417    last_check            TEXT,
418    last_success          TEXT,
419    last_failure          TEXT,
420    consecutive_failures  INT          NOT NULL DEFAULT 0,
421    response_latency_ms   INT
422) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
423
424CREATE INDEX IF NOT EXISTS idx_ca_health_ca_id ON ca_health (ca_id);
425
426-- Enrollment request log
427CREATE TABLE IF NOT EXISTS enrollment_requests (
428    id              BIGINT       AUTO_INCREMENT PRIMARY KEY,
429    request_type    ENUM('enroll', 'reenroll', 'serverkeygen', 'fullcmc') NOT NULL,
430    csr_hash        VARCHAR(255) NOT NULL,
431    ca_id           VARCHAR(255) NOT NULL,
432    label           VARCHAR(255),
433    auth_method     VARCHAR(255) NOT NULL,
434    entity_id       VARCHAR(255),
435    status          ENUM('pending', 'issued', 'rejected') NOT NULL DEFAULT 'pending',
436    certificate_id  BIGINT,
437    created_at      TEXT  NOT NULL DEFAULT (DATE_FORMAT(NOW(6), '%Y-%m-%dT%H:%i:%S.%fZ')),
438    completed_at    TEXT,
439    CONSTRAINT fk_enrollment_certificate
440        FOREIGN KEY (certificate_id) REFERENCES certificates(id)
441) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
442
443CREATE INDEX IF NOT EXISTS idx_enrollment_requests_status ON enrollment_requests (status);
444CREATE INDEX IF NOT EXISTS idx_enrollment_requests_ca_id ON enrollment_requests (ca_id);
445CREATE INDEX IF NOT EXISTS idx_enrollment_requests_entity_id ON enrollment_requests (entity_id);
446CREATE INDEX IF NOT EXISTS idx_enrollment_requests_csr_hash ON enrollment_requests (csr_hash);
447
448-- Server-generated keys
449CREATE TABLE IF NOT EXISTS server_generated_keys (
450    id              BIGINT       AUTO_INCREMENT PRIMARY KEY,
451    enrollment_id   BIGINT       NOT NULL,
452    key_type        VARCHAR(255) NOT NULL,
453    key_size        INT          NOT NULL,
454    archived        BOOLEAN      NOT NULL DEFAULT FALSE,
455    archive_id      VARCHAR(255),
456    created_at      TEXT  NOT NULL DEFAULT (DATE_FORMAT(NOW(6), '%Y-%m-%dT%H:%i:%S.%fZ')),
457    CONSTRAINT fk_sgk_enrollment
458        FOREIGN KEY (enrollment_id) REFERENCES enrollment_requests(id)
459) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
460
461CREATE INDEX IF NOT EXISTS idx_server_generated_keys_enrollment_id ON server_generated_keys (enrollment_id);
462
463-- STAR (Short-Term Automatic Renewal) tables (RFC 8739)
464CREATE TABLE IF NOT EXISTS star_orders (
465    id                    VARCHAR(255) PRIMARY KEY,
466    subject_dn            TEXT         NOT NULL,
467    key_type              VARCHAR(255) NOT NULL,
468    profile               TEXT         NOT NULL,
469    renewal_interval_secs INT          NOT NULL,
470    lifetime_end          TEXT  NOT NULL,
471    max_renewals          INT          NOT NULL,
472    current_renewals      INT          NOT NULL DEFAULT 0,
473    status                ENUM('active', 'cancelled', 'completed', 'expired') NOT NULL DEFAULT 'active',
474    requestor_dn          TEXT,
475    ca_id                 VARCHAR(255) NOT NULL,
476    csr_der               LONGBLOB     NOT NULL,
477    created_at            TEXT  NOT NULL DEFAULT (DATE_FORMAT(NOW(6), '%Y-%m-%dT%H:%i:%S.%fZ')),
478    cancelled_at          TEXT
479) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
480
481CREATE TABLE IF NOT EXISTS star_certificates (
482    id              BIGINT       AUTO_INCREMENT PRIMARY KEY,
483    star_order_id   VARCHAR(255) NOT NULL,
484    serial_number   VARCHAR(255) NOT NULL,
485    certificate_der LONGBLOB     NOT NULL,
486    not_before      TEXT  NOT NULL,
487    not_after       TEXT  NOT NULL,
488    renewal_number  INT          NOT NULL,
489    created_at      TEXT  NOT NULL DEFAULT (DATE_FORMAT(NOW(6), '%Y-%m-%dT%H:%i:%S.%fZ')),
490    CONSTRAINT fk_star_certs_order
491        FOREIGN KEY (star_order_id) REFERENCES star_orders(id)
492) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
493
494CREATE INDEX IF NOT EXISTS idx_star_certs_order ON star_certificates(star_order_id, renewal_number DESC);
495"#;
496
497// ---------------------------------------------------------------------------
498// SQLite migration v3 — pending CSRs for deferred/disconnected signing
499// ---------------------------------------------------------------------------
500const MIGRATION_V3_SQLITE: &str = r#"
501CREATE TABLE IF NOT EXISTS pending_csrs (
502    id              INTEGER PRIMARY KEY AUTOINCREMENT,
503    csr_der         BLOB    NOT NULL,
504    ca_id           TEXT    NOT NULL,
505    subject_dn      TEXT,
506    identity        TEXT    NOT NULL,
507    auth_method     TEXT,
508    status          TEXT    NOT NULL DEFAULT 'pending'
509                           CHECK (status IN ('pending', 'signed', 'rejected', 'expired')),
510    created_at      TEXT    NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
511    signed_at       TEXT,
512    certificate_id  INTEGER REFERENCES certificates(id)
513);
514
515CREATE INDEX IF NOT EXISTS idx_pending_csrs_ca_id ON pending_csrs (ca_id);
516CREATE INDEX IF NOT EXISTS idx_pending_csrs_status ON pending_csrs (status);
517CREATE INDEX IF NOT EXISTS idx_pending_csrs_identity ON pending_csrs (identity);
518"#;
519
520// ---------------------------------------------------------------------------
521// PostgreSQL migration v3
522// ---------------------------------------------------------------------------
523const MIGRATION_V3_POSTGRES: &str = r#"
524DO $$ BEGIN
525    CREATE TYPE pending_csr_status AS ENUM ('pending', 'signed', 'rejected', 'expired');
526EXCEPTION WHEN duplicate_object THEN NULL;
527END $$;
528
529CREATE TABLE IF NOT EXISTS pending_csrs (
530    id              BIGSERIAL          PRIMARY KEY,
531    csr_der         BYTEA              NOT NULL,
532    ca_id           TEXT               NOT NULL,
533    subject_dn      TEXT,
534    identity        TEXT               NOT NULL,
535    auth_method     TEXT,
536    status          pending_csr_status NOT NULL DEFAULT 'pending',
537    created_at      TEXT        NOT NULL DEFAULT (to_char(NOW(), 'YYYY-MM-DD"T"HH24:MI:SS"Z"')),
538    signed_at       TEXT,
539    certificate_id  BIGINT             REFERENCES certificates(id)
540);
541
542CREATE INDEX IF NOT EXISTS idx_pending_csrs_ca_id ON pending_csrs (ca_id);
543CREATE INDEX IF NOT EXISTS idx_pending_csrs_status ON pending_csrs (status);
544CREATE INDEX IF NOT EXISTS idx_pending_csrs_identity ON pending_csrs (identity);
545"#;
546
547// ---------------------------------------------------------------------------
548// MariaDB migration v3
549// ---------------------------------------------------------------------------
550const MIGRATION_V3_MARIADB: &str = r#"
551CREATE TABLE IF NOT EXISTS pending_csrs (
552    id              BIGINT       AUTO_INCREMENT PRIMARY KEY,
553    csr_der         LONGBLOB     NOT NULL,
554    ca_id           VARCHAR(255) NOT NULL,
555    subject_dn      TEXT,
556    identity        VARCHAR(255) NOT NULL,
557    auth_method     VARCHAR(255),
558    status          ENUM('pending', 'signed', 'rejected', 'expired') NOT NULL DEFAULT 'pending',
559    created_at      TEXT  NOT NULL DEFAULT (DATE_FORMAT(NOW(6), '%Y-%m-%dT%H:%i:%S.%fZ')),
560    signed_at       TEXT,
561    certificate_id  BIGINT,
562    CONSTRAINT fk_pending_csrs_certificate
563        FOREIGN KEY (certificate_id) REFERENCES certificates(id)
564) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
565
566CREATE INDEX IF NOT EXISTS idx_pending_csrs_ca_id ON pending_csrs (ca_id);
567CREATE INDEX IF NOT EXISTS idx_pending_csrs_status ON pending_csrs (status);
568CREATE INDEX IF NOT EXISTS idx_pending_csrs_identity ON pending_csrs (identity);
569"#;
570
571/// Run all pending migrations.
572///
573/// The `kind` parameter selects the dialect-specific DDL so that the
574/// correct data types, defaults, and syntax are used for each backend.
575pub async fn run_migrations(pool: &sqlx::AnyPool, kind: DbKind) -> Result<(), KipukaError> {
576    // Check current schema version
577    let current = current_version(pool).await?;
578
579    if current < 1 {
580        tracing::info!("applying migration v1 (initial schema)");
581
582        let migration_sql = match kind {
583            DbKind::Sqlite => MIGRATION_V1_SQLITE,
584            DbKind::Postgres => MIGRATION_V1_POSTGRES,
585            DbKind::MariaDb => MIGRATION_V1_MARIADB,
586        };
587
588        // Execute migration statements one at a time (sqlx Any doesn't
589        // support multi-statement execution on all backends).
590        //
591        // PostgreSQL DO blocks contain semicolons inside $$ delimiters,
592        // so we use a smarter splitter that respects dollar-quoting.
593        for statement in split_sql_statements(migration_sql) {
594            let stripped: String = statement
595                .lines()
596                .filter(|l| !l.trim_start().starts_with("--"))
597                .collect::<Vec<_>>()
598                .join("\n");
599            let trimmed = stripped.trim();
600            if trimmed.is_empty() {
601                continue;
602            }
603            sqlx::query(sqlx::AssertSqlSafe(trimmed.to_string()))
604                .execute(pool)
605                .await
606                .map_err(|e| KipukaError::Db(format!("migration v1 failed on [{trimmed}]: {e}")))?;
607        }
608
609        // Record the version
610        sqlx::query("INSERT INTO schema_version (version) VALUES (1)")
611            .execute(pool)
612            .await
613            .map_err(|e| KipukaError::Db(format!("recording schema version: {e}")))?;
614
615        tracing::info!("migration v1 applied successfully");
616    }
617
618    if current < 2 {
619        tracing::info!("applying migration v2 (OTP hash index)");
620        sqlx::query(
621            "CREATE INDEX IF NOT EXISTS idx_otp_tokens_hash ON otp_tokens (entity_id, token_hash)",
622        )
623        .execute(pool)
624        .await
625        .map_err(|e| KipukaError::Db(format!("migration v2 failed: {e}")))?;
626
627        sqlx::query("INSERT INTO schema_version (version) VALUES (2)")
628            .execute(pool)
629            .await
630            .map_err(|e| KipukaError::Db(format!("recording schema version: {e}")))?;
631
632        tracing::info!("migration v2 applied successfully");
633    }
634
635    if current < 3 {
636        tracing::info!("applying migration v3 (pending_csrs table)");
637
638        let migration_sql = match kind {
639            DbKind::Sqlite => MIGRATION_V3_SQLITE,
640            DbKind::Postgres => MIGRATION_V3_POSTGRES,
641            DbKind::MariaDb => MIGRATION_V3_MARIADB,
642        };
643
644        for statement in split_sql_statements(migration_sql) {
645            let stripped: String = statement
646                .lines()
647                .filter(|l| !l.trim_start().starts_with("--"))
648                .collect::<Vec<_>>()
649                .join("\n");
650            let trimmed = stripped.trim();
651            if trimmed.is_empty() {
652                continue;
653            }
654            sqlx::query(sqlx::AssertSqlSafe(trimmed.to_string()))
655                .execute(pool)
656                .await
657                .map_err(|e| KipukaError::Db(format!("migration v3 failed on [{trimmed}]: {e}")))?;
658        }
659
660        sqlx::query("INSERT INTO schema_version (version) VALUES (3)")
661            .execute(pool)
662            .await
663            .map_err(|e| KipukaError::Db(format!("recording schema version: {e}")))?;
664
665        tracing::info!("migration v3 applied successfully");
666    }
667
668    if current < 4 {
669        sqlx::query("CREATE TABLE IF NOT EXISTS audit_writer_lock (id INTEGER PRIMARY KEY, revision INTEGER NOT NULL)")
670            .execute(pool).await.map_err(|e| KipukaError::Db(format!("audit lock migration: {e}")))?;
671        let insert = match kind {
672            DbKind::MariaDb => "INSERT IGNORE INTO audit_writer_lock (id, revision) VALUES (1, 0)",
673            _ => {
674                "INSERT INTO audit_writer_lock (id, revision) VALUES (1, 0) ON CONFLICT (id) DO NOTHING"
675            }
676        };
677        sqlx::query(insert)
678            .execute(pool)
679            .await
680            .map_err(|e| KipukaError::Db(e.to_string()))?;
681        sqlx::query("INSERT INTO schema_version (version) VALUES (4)")
682            .execute(pool)
683            .await
684            .map_err(|e| KipukaError::Db(e.to_string()))?;
685    }
686
687    if current < 5 {
688        if kind == DbKind::MariaDb {
689            // STAR now stores the complete authorized policy snapshot, not a name.
690            sqlx::query("ALTER TABLE star_orders MODIFY COLUMN profile TEXT NOT NULL")
691                .execute(pool)
692                .await
693                .map_err(|e| KipukaError::Db(format!("STAR policy migration: {e}")))?;
694        }
695        sqlx::query("INSERT INTO schema_version (version) VALUES (5)")
696            .execute(pool)
697            .await
698            .map_err(|e| KipukaError::Db(e.to_string()))?;
699    }
700
701    if current < 6 {
702        tracing::info!("applying migration v6 (audit hash-chain columns)");
703        // NIAP CA PP FAU_STG.1: each audit row carries the hash of the
704        // previous row (`prev_hash`) and its own chained hash (`record_hash`),
705        // forming a tamper-evident trail.  Added as nullable columns so the
706        // migration is non-destructive on an existing populated table; rows
707        // written before this migration have NULL hashes and mark the start of
708        // the verifiable chain segment.  Each backend gets one ADD COLUMN per
709        // statement (SQLite permits only one column per ALTER).
710        let alters: &[&str] = match kind {
711            DbKind::Postgres => &[
712                "ALTER TABLE audit_events ADD COLUMN IF NOT EXISTS prev_hash TEXT",
713                "ALTER TABLE audit_events ADD COLUMN IF NOT EXISTS record_hash TEXT",
714            ],
715            DbKind::MariaDb => &[
716                "ALTER TABLE audit_events ADD COLUMN IF NOT EXISTS prev_hash TEXT",
717                "ALTER TABLE audit_events ADD COLUMN IF NOT EXISTS record_hash TEXT",
718            ],
719            DbKind::Sqlite => &[
720                "ALTER TABLE audit_events ADD COLUMN prev_hash TEXT",
721                "ALTER TABLE audit_events ADD COLUMN record_hash TEXT",
722            ],
723        };
724        for stmt in alters {
725            sqlx::query(*stmt)
726                .execute(pool)
727                .await
728                .map_err(|e| KipukaError::Db(format!("migration v6 failed on [{stmt}]: {e}")))?;
729        }
730        sqlx::query("INSERT INTO schema_version (version) VALUES (6)")
731            .execute(pool)
732            .await
733            .map_err(|e| KipukaError::Db(e.to_string()))?;
734        tracing::info!("migration v6 applied successfully");
735    }
736
737    Ok(())
738}
739
740/// Split SQL text into individual statements, respecting PostgreSQL
741/// dollar-quoted blocks (`$$ ... $$` or `$tag$ ... $tag$`).
742///
743/// A naive `split(';')` would break inside `DO $$ ... END $$;` blocks
744/// that contain internal semicolons.
745fn split_sql_statements(sql: &str) -> Vec<&str> {
746    let mut statements = Vec::new();
747    let bytes = sql.as_bytes();
748    let len = bytes.len();
749    let mut start = 0;
750    let mut i = 0;
751    let mut in_dollar_quote = false;
752    let mut dollar_tag = String::new();
753
754    while i < len {
755        if bytes[i] == b'$' && !in_dollar_quote {
756            // Try to read a dollar-quote tag: $tag$ or $$
757            if let Some(tag) = read_dollar_tag(sql, i) {
758                in_dollar_quote = true;
759                dollar_tag = tag.clone();
760                i += tag.len();
761                continue;
762            }
763        } else if bytes[i] == b'$' && in_dollar_quote {
764            // Check if this is the closing dollar-quote tag
765            if sql[i..].starts_with(&dollar_tag) {
766                in_dollar_quote = false;
767                i += dollar_tag.len();
768                dollar_tag.clear();
769                continue;
770            }
771        }
772
773        if bytes[i] == b';' && !in_dollar_quote {
774            let stmt = &sql[start..i];
775            if !stmt.trim().is_empty() {
776                statements.push(stmt);
777            }
778            start = i + 1;
779        }
780
781        i += 1;
782    }
783
784    // Trailing text after the last semicolon
785    let tail = &sql[start..];
786    if !tail.trim().is_empty() {
787        statements.push(tail);
788    }
789
790    statements
791}
792
793/// Try to read a dollar-quote tag starting at position `pos`.
794///
795/// Returns the full tag (e.g. `$$` or `$tag$`) if found.
796fn read_dollar_tag(sql: &str, pos: usize) -> Option<String> {
797    let rest = &sql[pos..];
798    if !rest.starts_with('$') {
799        return None;
800    }
801
802    // Look for the closing '$' of the tag
803    for (j, ch) in rest[1..].char_indices() {
804        if ch == '$' {
805            let tag = &rest[..j + 2]; // includes both dollar signs
806            return Some(tag.to_string());
807        }
808        if !ch.is_alphanumeric() && ch != '_' {
809            break;
810        }
811    }
812
813    None
814}
815
816/// Query the current schema version.  Returns 0 if no migrations have
817/// been applied (or the schema_version table does not exist).
818async fn current_version(pool: &sqlx::AnyPool) -> Result<i32, KipukaError> {
819    // The table might not exist yet on a fresh database
820    let row = sqlx::query_as::<_, (i32,)>("SELECT COALESCE(MAX(version), 0) FROM schema_version")
821        .fetch_one(pool)
822        .await;
823
824    match row {
825        Ok((version,)) => Ok(version),
826        Err(_) => {
827            // Table doesn't exist — this is a fresh database
828            Ok(0)
829        }
830    }
831}
kipuka/db/schema.rs

kipuka/db/
schema.rs
