Certificates

Every certificate once, by SHA-256: PEM, subject, issuer, SAN names, validity, key and signature algorithm.

17 columns

Column	Type	Null	Default
`id` PK	`bigint(20) unsigned`
`sha256` unique	`varchar(64)`
`common_name`	`varchar(255)`	yes
`subject`	`text`	yes
`issuer`	`varchar(255)`	yes
`sans`	`longtext`	yes
`wildcard`	`tinyint(1)`		`0`
`serial`	`varchar(100)`	yes
`key`	`varchar(50)`	yes
`signature_algorithm`	`varchar(50)`	yes
`not_before`	`timestamp`	yes
`not_after`	`timestamp`	yes
`pem`	`text`	yes
`first_seen_at`	`timestamp`	yes
`last_seen_at`	`timestamp`	yes
`created_at`	`timestamp`	yes
`updated_at`	`timestamp`	yes

Indexes: sha256 (unique)

Which certificate an endpoint (host, address, port, SNI name, STARTTLS protocol) serves, seen by which satellite, with trust and revocation status.

15 columns

Column	Type	Null	Default	References
`id` PK	`bigint(20) unsigned`
`tenant_id`	`bigint(20) unsigned`			`tenants` on delete cascade
`certificate_id`	`bigint(20) unsigned`			`certificates` on delete cascade
`host_id`	`bigint(20) unsigned`	yes		`hosts` on delete cascade
`domain_id`	`bigint(20) unsigned`	yes		`domains` on delete cascade
`satellite_id`	`bigint(20) unsigned`	yes		`satellites` on delete set null
`endpoint`	`varchar(255)`
`port`	`int(10) unsigned`
`sni`	`varchar(255)`
`protocol`	`varchar(8)`		`tls`
`status`	`longtext`	yes
`first_seen_at`	`timestamp`	yes
`last_seen_at`	`timestamp`	yes
`created_at`	`timestamp`	yes
`updated_at`	`timestamp`	yes

Indexes: certificate_id · domain_id · host_id · satellite_id · tenant_id, endpoint, sni, satellite_id (unique)

An endpoint started serving another certificate (notification cert.changed).

9 columns

Column	Type	Null	References
`id` PK	`bigint(20) unsigned`
`tenant_id`	`bigint(20) unsigned`		`tenants` on delete cascade
`host_id`	`bigint(20) unsigned`	yes	`hosts` on delete cascade
`satellite_id`	`bigint(20) unsigned`	yes	`satellites` on delete set null
`endpoint`	`varchar(255)`
`sni`	`varchar(255)`
`old_certificate_id`	`bigint(20) unsigned`	yes	`certificates` on delete set null
`new_certificate_id`	`bigint(20) unsigned`	yes	`certificates` on delete set null
`detected_at`	`timestamp`

Indexes: host_id, detected_at · new_certificate_id · old_certificate_id · satellite_id · tenant_id

Certificates found in Certificate Transparency logs for a monitored domain. The first poll is the baseline.

13 columns

Column	Type	Null	Default	References
`id` PK	`bigint(20) unsigned`
`tenant_id`	`bigint(20) unsigned`			`tenants` on delete cascade
`domain_id`	`bigint(20) unsigned`			`domains` on delete cascade
`serial`	`varchar(128)`
`log_id`	`bigint(20) unsigned`	yes
`issuer`	`varchar(500)`	yes
`common_name`	`varchar(255)`	yes
`names`	`longtext`	yes
`not_before`	`timestamp`	yes
`not_after`	`timestamp`	yes
`logged_at`	`timestamp`	yes
`first_seen_at`	`timestamp`	yes
`baseline`	`tinyint(1)`		`0`

Indexes: domain_id, serial (unique) · tenant_id, first_seen_at