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 = 2;
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               VARCHAR(255) 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/// Run all pending migrations.
498///
499/// The `kind` parameter selects the dialect-specific DDL so that the
500/// correct data types, defaults, and syntax are used for each backend.
501pub async fn run_migrations(pool: &sqlx::AnyPool, kind: DbKind) -> Result<(), KipukaError> {
502    // Check current schema version
503    let current = current_version(pool).await?;
504
505    if current < 1 {
506        tracing::info!("applying migration v1 (initial schema)");
507
508        let migration_sql = match kind {
509            DbKind::Sqlite => MIGRATION_V1_SQLITE,
510            DbKind::Postgres => MIGRATION_V1_POSTGRES,
511            DbKind::MariaDb => MIGRATION_V1_MARIADB,
512        };
513
514        // Execute migration statements one at a time (sqlx Any doesn't
515        // support multi-statement execution on all backends).
516        //
517        // PostgreSQL DO blocks contain semicolons inside $$ delimiters,
518        // so we use a smarter splitter that respects dollar-quoting.
519        for statement in split_sql_statements(migration_sql) {
520            let stripped: String = statement
521                .lines()
522                .filter(|l| !l.trim_start().starts_with("--"))
523                .collect::<Vec<_>>()
524                .join("\n");
525            let trimmed = stripped.trim();
526            if trimmed.is_empty() {
527                continue;
528            }
529            sqlx::query(trimmed)
530                .execute(pool)
531                .await
532                .map_err(|e| KipukaError::Db(format!("migration v1 failed on [{trimmed}]: {e}")))?;
533        }
534
535        // Record the version
536        sqlx::query("INSERT INTO schema_version (version) VALUES (1)")
537            .execute(pool)
538            .await
539            .map_err(|e| KipukaError::Db(format!("recording schema version: {e}")))?;
540
541        tracing::info!("migration v1 applied successfully");
542    }
543
544    if current < 2 {
545        tracing::info!("applying migration v2 (OTP hash index)");
546        sqlx::query(
547            "CREATE INDEX IF NOT EXISTS idx_otp_tokens_hash ON otp_tokens (entity_id, token_hash)",
548        )
549        .execute(pool)
550        .await
551        .map_err(|e| KipukaError::Db(format!("migration v2 failed: {e}")))?;
552
553        sqlx::query("INSERT INTO schema_version (version) VALUES (2)")
554            .execute(pool)
555            .await
556            .map_err(|e| KipukaError::Db(format!("recording schema version: {e}")))?;
557
558        tracing::info!("migration v2 applied successfully");
559    }
560
561    Ok(())
562}
563
564/// Split SQL text into individual statements, respecting PostgreSQL
565/// dollar-quoted blocks (`$$ ... $$` or `$tag$ ... $tag$`).
566///
567/// A naive `split(';')` would break inside `DO $$ ... END $$;` blocks
568/// that contain internal semicolons.
569fn split_sql_statements(sql: &str) -> Vec<&str> {
570    let mut statements = Vec::new();
571    let bytes = sql.as_bytes();
572    let len = bytes.len();
573    let mut start = 0;
574    let mut i = 0;
575    let mut in_dollar_quote = false;
576    let mut dollar_tag = String::new();
577
578    while i < len {
579        if bytes[i] == b'$' && !in_dollar_quote {
580            // Try to read a dollar-quote tag: $tag$ or $$
581            if let Some(tag) = read_dollar_tag(sql, i) {
582                in_dollar_quote = true;
583                dollar_tag = tag.clone();
584                i += tag.len();
585                continue;
586            }
587        } else if bytes[i] == b'$' && in_dollar_quote {
588            // Check if this is the closing dollar-quote tag
589            if sql[i..].starts_with(&dollar_tag) {
590                in_dollar_quote = false;
591                i += dollar_tag.len();
592                dollar_tag.clear();
593                continue;
594            }
595        }
596
597        if bytes[i] == b';' && !in_dollar_quote {
598            let stmt = &sql[start..i];
599            if !stmt.trim().is_empty() {
600                statements.push(stmt);
601            }
602            start = i + 1;
603        }
604
605        i += 1;
606    }
607
608    // Trailing text after the last semicolon
609    let tail = &sql[start..];
610    if !tail.trim().is_empty() {
611        statements.push(tail);
612    }
613
614    statements
615}
616
617/// Try to read a dollar-quote tag starting at position `pos`.
618///
619/// Returns the full tag (e.g. `$$` or `$tag$`) if found.
620fn read_dollar_tag(sql: &str, pos: usize) -> Option<String> {
621    let rest = &sql[pos..];
622    if !rest.starts_with('$') {
623        return None;
624    }
625
626    // Look for the closing '$' of the tag
627    for (j, ch) in rest[1..].char_indices() {
628        if ch == '$' {
629            let tag = &rest[..j + 2]; // includes both dollar signs
630            return Some(tag.to_string());
631        }
632        if !ch.is_alphanumeric() && ch != '_' {
633            break;
634        }
635    }
636
637    None
638}
639
640/// Query the current schema version.  Returns 0 if no migrations have
641/// been applied (or the schema_version table does not exist).
642async fn current_version(pool: &sqlx::AnyPool) -> Result<i32, KipukaError> {
643    // The table might not exist yet on a fresh database
644    let row = sqlx::query_as::<_, (i32,)>("SELECT COALESCE(MAX(version), 0) FROM schema_version")
645        .fetch_one(pool)
646        .await;
647
648    match row {
649        Ok((version,)) => Ok(version),
650        Err(_) => {
651            // Table doesn't exist — this is a fresh database
652            Ok(0)
653        }
654    }
655}
kipuka/db/schema.rs

kipuka/db/
schema.rs
