--- /dev/null
+/* -*-sql-*-
+ *
+ * Set up the database for tracking certificates and requests
+ */
+
+/*----- Metadata table ----------------------------------------------------*/
+
+create table meta (
+ version integer,
+ request_seq integer
+);
+
+insert into meta values (1, 0);
+
+/*----- Profiles ----------------------------------------------------------*/
+
+create table profile (
+
+ -- A label by which this profile is known.
+ label varchar(16) primary key,
+
+ -- The name of the extensions section in OpenSSL's configuration
+ -- file.
+ extensions varchar(32) not null,
+
+ -- The time at which to issue the next certificate. This has the
+ -- form of an ISO8601 date (YYYY-MM-DD HH:MM:SS), though the
+ -- components can be wildcards. Specifically, * means any value, and
+ -- */n means any value which is a multiple of n. Sorry: there isn't
+ -- currently a way of saying `next Thursday'.
+ issue_time varchar(64) not null,
+
+ -- The time, in hours, that a certificate should appear to have been
+ -- valid /before/ it was issued. This is useful to prevent
+ -- unnecessary problems with reliers whose clocks are broken.
+ start_skew integer not null,
+
+ -- The time, in hours, that a certificate remains valid after issue.
+ expire_interval integer not null,
+
+ -- A marker that this profile shouldn't be used any more.
+ tombstone boolean default 0
+);
+
+/*----- Certificate requests ----------------------------------------------*/
+
+create table request (
+
+ -- We need a stable way to talk about requests. Unfortunately, we
+ -- don't have an exernally stable name, so we'll get the database to
+ -- make one up for us.
+ id integer primary key,
+
+ -- The tag is a vaguely human-readable name for this certificate
+ -- request. THere should only be one active request with a given
+ -- tag, though there may be several withdrawn or revoked requests.
+ tag varchar(128) not null,
+
+ -- The distinguished name for the request. Again, there should only
+ -- be one active request with a given DN, but there may be several
+ -- withdrawn or revoked requests. The DN is given in OpenSSL's terse
+ -- form, as written by `openssl req -subject -noout'.
+ dn text not null,
+
+ -- The distinguished name as it will appear in certificates. We
+ -- expect all certificates to have the same DN, though OpenSSL's
+ -- certificate construction will mangle the DN (in particular, it
+ -- moves email addresses to the `subjectAltName'). If this field is
+ -- null then no certificate has been issued for the request;
+ -- otherwise we fill it in with the certificate's DN.
+ cert_dn text,
+
+ -- The key hash from the request. This is simply the SHA256 hash of
+ -- the DER-encoded public key, in lowercase hex.
+ hash varchar(64) not null,
+
+ -- The state can be one of `active', `withdrawn', or `revoked'.
+ -- Requests in the `archived' state aren't stored in the database.
+ st varchar(12) not null,
+
+ -- The profile tells us how we should generate certificates. It
+ -- refers to a chunk of the OpenSSL configuration file.
+ profile varchar(16) not null,
+
+ -- If the state is `revoked' then we should have the reason and maybe
+ -- a detail here. The reason is one of the names: `unspecified',
+ -- `key-compromise', `ca-compromise', `affiliation-changed',
+ -- `superceded', `cessation-of-operation', `certificate-hold', or
+ -- `remove-from-crl'. The detail's contents depends on the reason.
+ -- If the reason is `certificate-hold' then the reason is the hold
+ -- instruction, probably `none', `call-issuer', or `reject'; if the
+ -- reason is `key-compromise' or `ca-compromise' then the detail is
+ -- the compromise time; other reasons don't carry details.
+ revoke_reason varchar(32),
+ revoke_detail text,
+
+ -- The time at which we should issue the next certificate for this
+ -- request.
+ t_reissue timestamp default 0,
+
+ -- Ensure that we have a revocation reason if the state is
+ -- `revoked'.
+ check (st != 'revoked' or revoke_reason is not null),
+
+ -- Check that the profile matches one that's already known.
+ foreign key (profile) references profile(label)
+);
+
+create index request_tag on request(tag);
+create index request_dn on request(dn);
+create index request_hash on request(hash);
+create index request_reissue on request(t_reissue asc);
+create index request_prifile on request(profile);
+
+/*----- Certificates ------------------------------------------------------*/
+
+create table certificate (
+
+ -- The sequence number of the certificate, as issued by OpenSSL's
+ -- `ca' program.
+ seq integer primary key,
+
+ -- The associated certificate request.
+ req integer not null,
+
+ -- The state can be one of `active', `superceded', `withdrawn',
+ -- `revoked', or `expired'. Certificates in the `archived' state
+ -- aren't stored in the database.
+ st varchar(12) not null,
+
+ -- The expiry time for the certificate. This may be in the past.
+ t_expire timestamp not null,
+
+ -- Match the certificate up with its request.
+ foreign key (req) references request(id)
+);
+
+create index cert_st on certificate(st);
+create index cert_req on certificate(req);
+create index cert_expire on certificate(t_expire asc);
+
+/*----- That's all, folks -------------------------------------------------*/
~mdw / ca / blobdiff