/* -*-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 -------------------------------------------------*/
