Files
2026-10-31 05:36:03 -05:00

656 lines
23 KiB
PL/PgSQL

BEGIN;
--- WARNING:
--- ALL times must be created as TYPE TIMESTAMP(3) WITH TIME ZONE
CREATE OR REPLACE FUNCTION trustyca.create_constraint_if_not_exists (
s_name text, t_name text, c_name text, constraint_sql text
)
RETURNS void AS
$$
BEGIN
-- Look for our constraint
IF NOT EXISTS (SELECT constraint_name
FROM information_schema.constraint_column_usage
WHERE table_schema = s_name AND table_name = t_name AND constraint_name = c_name) THEN
EXECUTE constraint_sql;
END IF;
END;
$$ LANGUAGE plpgsql;
ALTER FUNCTION trustyca.create_constraint_if_not_exists(text, text, text, text) OWNER TO trustyca;
--
-- Login
--
CREATE TABLE IF NOT EXISTS trustyca.login
(
id BIGINT NOT NULL,
external_id VARCHAR(64) COLLATE pg_catalog."default" NOT NULL,
provider INTEGER NOT NULL,
email VARCHAR(160) COLLATE pg_catalog."default" NOT NULL,
email_verified BOOLEAN NOT NULL,
name VARCHAR(64) COLLATE pg_catalog."default" NOT NULL,
access_token TEXT COLLATE pg_catalog."default" NOT NULL,
refresh_token TEXT COLLATE pg_catalog."default" NOT NULL,
token_expires_at TIMESTAMP(3) WITH TIME ZONE,
count INTEGER NOT NULL DEFAULT 0,
last_at TIMESTAMP(3) WITH TIME ZONE,
CONSTRAINT login_pkey PRIMARY KEY (id)
);
ALTER TABLE trustyca.login
OWNER TO trustyca;
CREATE INDEX IF NOT EXISTS idx_login_email
ON trustyca.login USING btree
(email COLLATE pg_catalog."default")
;
CREATE INDEX IF NOT EXISTS idx_login_last_at
ON trustyca.login USING btree
(last_at)
;
CREATE UNIQUE INDEX IF NOT EXISTS idx_login_provider_email
ON trustyca.login USING btree
(provider,email COLLATE pg_catalog."default")
;
SELECT trustyca.create_constraint_if_not_exists(
'trustyca',
'login',
'unique_login_provider_email',
'ALTER TABLE trustyca.login ADD CONSTRAINT unique_login_provider_email UNIQUE USING INDEX idx_login_provider_email;');
--
-- USERS
--
CREATE TABLE IF NOT EXISTS trustyca.user
(
id bigint NOT NULL,
email VARCHAR(160) COLLATE pg_catalog."default" NOT NULL,
email_verified boolean NOT NULL,
name VARCHAR(64) COLLATE pg_catalog."default" NOT NULL,
CONSTRAINT user_pkey PRIMARY KEY (id)
);
ALTER TABLE trustyca.user
OWNER TO trustyca;
CREATE UNIQUE INDEX IF NOT EXISTS idx_user_email
ON trustyca.user USING btree
(email COLLATE pg_catalog."default")
;
SELECT trustyca.create_constraint_if_not_exists(
'trustyca',
'user',
'unique_user_email',
'ALTER TABLE trustyca.user ADD CONSTRAINT unique_user_email UNIQUE USING INDEX idx_user_email;');
--
-- Orgs
--
CREATE TABLE IF NOT EXISTS trustyca.org
(
id bigint NOT NULL,
alias VARCHAR(32) COLLATE pg_catalog."default" NOT NULL,
name VARCHAR(64) COLLATE pg_catalog."default" NOT NULL,
description TEXT COLLATE pg_catalog."default" NULL,
status INTEGER NOT NULL DEFAULT 0,
created_at TIMESTAMP(3) WITH TIME ZONE NOT NULL DEFAULT NOW(),
CONSTRAINT org_pkey PRIMARY KEY (id)
);
ALTER TABLE trustyca.org
OWNER TO trustyca;
CREATE UNIQUE INDEX IF NOT EXISTS idx_org_alias
ON trustyca.org USING btree
(alias);
-- the orgs listed by name, then by id: a cursor page continues past (name, id)
CREATE INDEX IF NOT EXISTS idx_org_name_id
ON trustyca.org USING btree
(name, id);
SELECT create_constraint_if_not_exists(
'trustyca',
'org',
'unique_org_alias',
'ALTER TABLE trustyca.org ADD CONSTRAINT unique_org_alias UNIQUE USING INDEX idx_org_alias;');
--
-- Projects
--
-- A project owns a collection of resources governed by a common access
-- policy. Membership, invite, event and CA data-plane tables carry
-- (org_id, project_id); project_id IS NULL means the row is org scope.
--
CREATE TABLE IF NOT EXISTS trustyca.project
(
id BIGINT NOT NULL,
org_id BIGINT NOT NULL REFERENCES trustyca.org ON DELETE CASCADE,
alias VARCHAR(64) COLLATE pg_catalog."default" NOT NULL,
name VARCHAR(64) COLLATE pg_catalog."default" NOT NULL,
description TEXT COLLATE pg_catalog."default" NULL,
status INTEGER NOT NULL DEFAULT 0,
created_at TIMESTAMP(3) WITH TIME ZONE NOT NULL DEFAULT NOW(),
updated_at TIMESTAMP(3) WITH TIME ZONE NOT NULL DEFAULT NOW(),
CONSTRAINT project_pkey PRIMARY KEY (id),
CONSTRAINT unique_project_org_alias UNIQUE (org_id, alias),
-- allows composite FKs (org_id, project_id) that guarantee a project
-- reference always belongs to the referencing row's org
CONSTRAINT unique_project_org_id UNIQUE (org_id, id)
);
ALTER TABLE trustyca.project
OWNER TO trustyca;
CREATE INDEX IF NOT EXISTS idx_project_org_status
ON trustyca.project USING btree
(org_id, status);
-- the projects of every org listed by name, then by id (the support list
-- across orgs); the projects of one org are bounded by idx_project_org_status
CREATE INDEX IF NOT EXISTS idx_project_name_id
ON trustyca.project USING btree
(name, id);
--
-- Memberships
--
-- An explicit role assignment (org_id, project_id, user_id, role):
-- project_id IS NULL: explicit org-wide grant, inherited by every current and
-- future project of the org.
-- project_id IS NOT NULL: explicit grant restricted to the project. Grants are
-- additive: effective project permissions are the union of the inherited
-- org-wide grant and the project grant.
-- A user has at most one row per scope, hence NULLS NOT DISTINCT.
-- The composite FK guarantees the project belongs to the membership's org.
--
CREATE TABLE IF NOT EXISTS trustyca.membership
(
id BIGINT NOT NULL,
org_id BIGINT NOT NULL REFERENCES trustyca.org ON DELETE CASCADE,
project_id BIGINT NULL,
user_id BIGINT NOT NULL REFERENCES trustyca.user ON DELETE CASCADE,
role INTEGER NOT NULL,
created_at TIMESTAMP(3) WITH TIME ZONE NOT NULL DEFAULT NOW(),
CONSTRAINT membership_pkey PRIMARY KEY (id),
CONSTRAINT unique_membership_org_project_user UNIQUE NULLS NOT DISTINCT (org_id, project_id, user_id),
CONSTRAINT fk_membership_project FOREIGN KEY (org_id, project_id)
REFERENCES trustyca.project (org_id, id) ON DELETE CASCADE
);
ALTER TABLE trustyca.membership
OWNER TO trustyca;
CREATE INDEX IF NOT EXISTS idx_membership_org_project
ON trustyca.membership USING btree
(org_id, project_id);
CREATE INDEX IF NOT EXISTS idx_membership_user_id
ON trustyca.membership USING btree
(user_id);
--
-- Invites
--
-- project_id IS NULL: org-wide invite; otherwise the invite grants a project
-- membership when accepted. Expired invites grant nothing and are skipped on
-- acceptance.
--
CREATE TABLE IF NOT EXISTS trustyca.invite
(
id BIGINT NOT NULL,
org_id BIGINT NOT NULL REFERENCES trustyca.org ON DELETE CASCADE,
project_id BIGINT NULL,
inviter_id BIGINT NOT NULL REFERENCES trustyca.user ON DELETE CASCADE,
email VARCHAR(160) COLLATE pg_catalog."default" NOT NULL,
role INTEGER NOT NULL,
created_at TIMESTAMP(3) WITH TIME ZONE NOT NULL DEFAULT NOW(),
expires_at TIMESTAMP(3) WITH TIME ZONE NULL,
CONSTRAINT invite_pkey PRIMARY KEY (id),
CONSTRAINT unique_invite_org_project_email UNIQUE NULLS NOT DISTINCT (org_id, project_id, email),
CONSTRAINT fk_invite_project FOREIGN KEY (org_id, project_id)
REFERENCES trustyca.project (org_id, id) ON DELETE CASCADE
);
ALTER TABLE trustyca.invite
OWNER to trustyca;
CREATE INDEX IF NOT EXISTS idx_invite_org_project
ON trustyca.invite USING btree
(org_id, project_id);
CREATE INDEX IF NOT EXISTS idx_invite_email
ON trustyca.invite USING btree
(email);
CREATE VIEW trustyca.vw_membership_info AS
SELECT
m.id,
m.org_id,
m.project_id,
o.alias AS org_alias,
o.name AS org_name,
o.status AS org_status,
COALESCE(t.alias, '') AS project_alias,
COALESCE(t.name, '') AS project_name,
u.id AS user_id,
u.email,
u.name,
m.role,
m.created_at
FROM trustyca.membership m
LEFT JOIN trustyca.org o ON m.org_id = o.id
LEFT JOIN trustyca.project t ON m.project_id = t.id
LEFT JOIN trustyca.user u ON m.user_id = u.id;
--
-- API Keys
--
CREATE TABLE IF NOT EXISTS trustyca.apikey
(
id bigint NOT NULL,
org_id bigint NOT NULL REFERENCES trustyca.org ON DELETE CASCADE,
project_id BIGINT NULL,
key VARCHAR(128) COLLATE pg_catalog."default" NOT NULL,
secret VARCHAR(128) COLLATE pg_catalog."default" NOT NULL,
label VARCHAR(260) COLLATE pg_catalog."default" NOT NULL,
scopes VARCHAR(64)[] COLLATE pg_catalog."default" NOT NULL DEFAULT '{}'::character varying[],
metadata JSONB NULL DEFAULT '{}'::jsonb,
status INTEGER NOT NULL DEFAULT 0,
created_at TIMESTAMP(3) WITH TIME ZONE NOT NULL DEFAULT NOW(),
expires_at TIMESTAMP(3) WITH TIME ZONE NULL,
used_at TIMESTAMP(3) WITH TIME ZONE NULL,
used_count INTEGER NOT NULL DEFAULT 0,
CONSTRAINT apikey_pkey PRIMARY KEY (id),
CONSTRAINT apikey_key UNIQUE (key),
-- a project scoped key must belong to a project of the same org;
-- deleting the project deletes its keys
CONSTRAINT fk_apikey_project FOREIGN KEY (org_id, project_id)
REFERENCES trustyca.project (org_id, id) ON DELETE CASCADE
);
ALTER TABLE trustyca.apikey
OWNER to trustyca;
-- list keys of an org or a project
CREATE INDEX IF NOT EXISTS idx_apikey_org_project
ON trustyca.apikey USING btree
(org_id, project_id);
--
--
-- Events
--
CREATE TABLE IF NOT EXISTS trustyca.event
(
id BIGINT NOT NULL,
org_id BIGINT REFERENCES trustyca.org ON DELETE CASCADE,
project_id BIGINT NULL REFERENCES trustyca.project ON DELETE CASCADE,
type INTEGER NOT NULL,
title VARCHAR(256) COLLATE pg_catalog."default" NOT NULL,
description TEXT COLLATE pg_catalog."default" NULL,
metadata JSONB NOT NULL DEFAULT '{}',
ref_id BIGINT NULL,
email VARCHAR(160) COLLATE pg_catalog."default" NULL,
source VARCHAR(256) COLLATE pg_catalog."default" NULL,
created_at TIMESTAMP(3) WITH TIME ZONE NOT NULL DEFAULT NOW(),
CONSTRAINT event_pkey PRIMARY KEY (id)
);
ALTER TABLE trustyca.event
OWNER to trustyca;
CREATE INDEX IF NOT EXISTS idx_event_org_id
ON trustyca.event USING btree
(org_id);
CREATE INDEX IF NOT EXISTS idx_event_org_created_id_desc
ON trustyca.event USING btree (org_id, created_at DESC, id DESC);
CREATE INDEX IF NOT EXISTS idx_event_org_project_created_id_desc
ON trustyca.event USING btree (org_id, project_id, created_at DESC, id DESC);
CREATE INDEX IF NOT EXISTS idx_event_type
ON trustyca.event USING btree
(type);
CREATE INDEX IF NOT EXISTS idx_event_email
ON trustyca.event USING btree
(email);
CREATE INDEX IF NOT EXISTS idx_event_org_ref_id
ON trustyca.event USING btree
(org_id, ref_id);
--
-- CA DATA PLANE
--
-- Tenancy: every CA table carries (org_id, project_id).
-- org_id IS NULL -> platform scope (the service's own bootstrap issuers)
-- project_id IS NULL -> org scope row, governed by org-wide permissions
-- Unique keys that include tenancy columns use NULLS NOT DISTINCT so that a
-- label is unique within its scope.
-- CA tables intentionally do NOT have FK to org/project: certificates, revocations
-- and issuers are audit records and must survive tenant deletion (orgs are
-- soft-deleted via status). The service validates tenancy on write.
--
--
-- Root Certificates (trust anchors published by CIS)
--
CREATE TABLE IF NOT EXISTS trustyca.root_certificate
(
id BIGINT NOT NULL,
org_id BIGINT NULL,
skid VARCHAR(64) COLLATE pg_catalog."default" NOT NULL,
not_before TIMESTAMP(3) WITH TIME ZONE NOT NULL,
not_after TIMESTAMP(3) WITH TIME ZONE NOT NULL,
subject VARCHAR(260) COLLATE pg_catalog."default" NOT NULL,
sha256 VARCHAR(64) COLLATE pg_catalog."default" NOT NULL,
trust INTEGER NOT NULL,
pem TEXT COLLATE pg_catalog."default" NOT NULL,
created_at TIMESTAMP(3) WITH TIME ZONE NOT NULL DEFAULT NOW(),
CONSTRAINT root_certificate_pkey PRIMARY KEY (id),
CONSTRAINT unique_root_certificate_sha256 UNIQUE (sha256)
);
ALTER TABLE trustyca.root_certificate
OWNER TO trustyca;
CREATE INDEX IF NOT EXISTS idx_root_certificate_org
ON trustyca.root_certificate USING btree
(org_id);
CREATE INDEX IF NOT EXISTS idx_root_certificate_skid
ON trustyca.root_certificate USING btree
(skid COLLATE pg_catalog."default");
CREATE INDEX IF NOT EXISTS idx_root_certificate_notafter
ON trustyca.root_certificate USING btree
(not_after);
--
-- Issuers (Root, Intermediate and Issuing CAs owned by a tenant)
--
-- key_provider/key_id reference the private key in a crypto provider
-- (AWS KMS, GCP KMS, PKCS#11). key_protected holds dataprotection-wrapped
-- key material only for software keys (dev/test or imported keys).
-- config is JSON of the issuer runtime settings (AIA/CRL/OCSP URLs and timings,
-- allowed profiles), see pb.IssuerConfig.
-- crl_number is the monotonic CRL number, bumped with every published CRL.
--
CREATE TABLE IF NOT EXISTS trustyca.issuer
(
id BIGINT NOT NULL,
org_id BIGINT NULL,
project_id BIGINT NULL,
label VARCHAR(64) COLLATE pg_catalog."default" NOT NULL,
type INTEGER NOT NULL,
status INTEGER NOT NULL,
parent_id BIGINT NULL,
skid VARCHAR(64) COLLATE pg_catalog."default" NOT NULL,
ikid VARCHAR(64) COLLATE pg_catalog."default" NOT NULL DEFAULT '',
serial_number VARCHAR(64) COLLATE pg_catalog."default" NOT NULL DEFAULT '',
subject VARCHAR(260) COLLATE pg_catalog."default" NOT NULL,
issuer VARCHAR(260) COLLATE pg_catalog."default" NOT NULL DEFAULT '',
sha256 VARCHAR(64) COLLATE pg_catalog."default" NOT NULL DEFAULT '',
not_before TIMESTAMP(3) WITH TIME ZONE NULL,
not_after TIMESTAMP(3) WITH TIME ZONE NULL,
pem TEXT COLLATE pg_catalog."default" NOT NULL DEFAULT '',
chain_pem TEXT COLLATE pg_catalog."default" NOT NULL DEFAULT '',
root_pem TEXT COLLATE pg_catalog."default" NOT NULL DEFAULT '',
csr_pem TEXT COLLATE pg_catalog."default" NOT NULL DEFAULT '',
key_provider VARCHAR(64) COLLATE pg_catalog."default" NOT NULL,
key_id VARCHAR(260) COLLATE pg_catalog."default" NOT NULL,
key_protected TEXT COLLATE pg_catalog."default" NULL,
config JSONB NOT NULL DEFAULT '{}',
crl_number BIGINT NOT NULL DEFAULT 0,
created_at TIMESTAMP(3) WITH TIME ZONE NOT NULL DEFAULT NOW(),
updated_at TIMESTAMP(3) WITH TIME ZONE NOT NULL DEFAULT NOW(),
CONSTRAINT issuer_pkey PRIMARY KEY (id),
CONSTRAINT unique_issuer_org_project_label UNIQUE NULLS NOT DISTINCT (org_id, project_id, label),
CONSTRAINT unique_issuer_skid UNIQUE (skid)
);
ALTER TABLE trustyca.issuer
OWNER TO trustyca;
CREATE INDEX IF NOT EXISTS idx_issuer_org_project_status
ON trustyca.issuer USING btree
(org_id, project_id, status);
CREATE INDEX IF NOT EXISTS idx_issuer_parent
ON trustyca.issuer USING btree
(parent_id);
CREATE INDEX IF NOT EXISTS idx_issuer_notafter
ON trustyca.issuer USING btree
(not_after);
--
-- Certificate profiles
--
-- config is JSON of pb.CertProfile (same shape as the YAML profiles in
-- etc/dev/ca-config.*.yaml). issuer_label binds the profile to an issuer of
-- the same org; '*' allows any issuer that lists the profile in
-- config.allowed_profiles.
-- Resolution at sign time: (org, project, label) -> (org, 0, label).
--
CREATE TABLE IF NOT EXISTS trustyca.certificate_profile
(
id BIGINT NOT NULL,
org_id BIGINT NULL,
project_id BIGINT NULL,
label VARCHAR(64) COLLATE pg_catalog."default" NOT NULL,
issuer_label VARCHAR(64) COLLATE pg_catalog."default" NOT NULL,
status INTEGER NOT NULL,
config JSONB NOT NULL DEFAULT '{}',
created_at TIMESTAMP(3) WITH TIME ZONE NOT NULL DEFAULT NOW(),
updated_at TIMESTAMP(3) WITH TIME ZONE NOT NULL DEFAULT NOW(),
CONSTRAINT certificate_profile_pkey PRIMARY KEY (id),
CONSTRAINT unique_certificate_profile_org_project_label UNIQUE NULLS NOT DISTINCT (org_id, project_id, label)
);
ALTER TABLE trustyca.certificate_profile
OWNER TO trustyca;
CREATE INDEX IF NOT EXISTS idx_certificate_profile_org_issuer
ON trustyca.certificate_profile USING btree
(org_id, issuer_label COLLATE pg_catalog."default");
--
-- Certificates (issued end-entity and subordinate CA certificates)
--
-- Immutable except for label/metadata/locations/status. Rows are never
-- deleted by tenant lifecycle; retention of expired rows is a separate task.
-- High volume table: all tenant indexes lead with (org_id, project_id) so the
-- table can later be partitioned BY HASH (org_id) without changing queries.
--
CREATE TABLE IF NOT EXISTS trustyca.certificate
(
id BIGINT NOT NULL,
org_id BIGINT NULL,
project_id BIGINT NULL,
skid VARCHAR(64) COLLATE pg_catalog."default" NOT NULL,
ikid VARCHAR(64) COLLATE pg_catalog."default" NOT NULL,
serial_number VARCHAR(64) COLLATE pg_catalog."default" NOT NULL,
not_before TIMESTAMP(3) WITH TIME ZONE NOT NULL,
not_after TIMESTAMP(3) WITH TIME ZONE NOT NULL,
subject VARCHAR(260) COLLATE pg_catalog."default" NOT NULL,
issuer VARCHAR(260) COLLATE pg_catalog."default" NOT NULL,
sha256 VARCHAR(64) COLLATE pg_catalog."default" NOT NULL,
profile VARCHAR(64) COLLATE pg_catalog."default" NOT NULL,
label VARCHAR(260) COLLATE pg_catalog."default" NOT NULL DEFAULT '',
locations VARCHAR(260)[] COLLATE pg_catalog."default" NOT NULL DEFAULT '{}'::character varying[],
metadata JSONB NOT NULL DEFAULT '{}',
pem TEXT COLLATE pg_catalog."default" NOT NULL,
issuers_pem TEXT COLLATE pg_catalog."default" NOT NULL,
status INTEGER NOT NULL,
created_at TIMESTAMP(3) WITH TIME ZONE NOT NULL DEFAULT NOW(),
CONSTRAINT certificate_pkey PRIMARY KEY (id),
CONSTRAINT unique_certificate_sha256 UNIQUE (sha256),
CONSTRAINT unique_certificate_ikid_serial UNIQUE (ikid, serial_number)
);
ALTER TABLE trustyca.certificate
OWNER TO trustyca;
-- listing newest first with keyset pagination on id: the org-wide list of
-- a tenant, and the list of one project. Without the org index the planner
-- walks the primary key backwards for the rows of an org, which reads the
-- whole table for a small tenant.
CREATE INDEX IF NOT EXISTS idx_certificate_org_id_desc
ON trustyca.certificate USING btree
(org_id, id DESC);
CREATE INDEX IF NOT EXISTS idx_certificate_org_project_id_desc
ON trustyca.certificate USING btree
(org_id, project_id, id DESC);
-- expiring certificates per tenant
CREATE INDEX IF NOT EXISTS idx_certificate_org_project_notafter
ON trustyca.certificate USING btree
(org_id, project_id, not_after);
-- the counts of the tenant summary: the certificates of an org or of a
-- project by status, and the Active ones whose validity ends within a
-- period, as index-only scans; the org-wide counts read every entry of the
-- org, a project's the entries of the project
CREATE INDEX IF NOT EXISTS idx_certificate_org_project_status_notafter
ON trustyca.certificate USING btree
(org_id, project_id, status, not_after);
-- listing per issuer within the org: an issuer belongs to one org, so the
-- org leads and a foreign key identifier finds nothing without a scan; the
-- support list across orgs resolves the org of the issuer first
CREATE INDEX IF NOT EXISTS idx_certificate_org_ikid_id_desc
ON trustyca.certificate USING btree
(org_id, ikid COLLATE pg_catalog."default", id DESC);
CREATE INDEX IF NOT EXISTS idx_certificate_skid
ON trustyca.certificate USING btree
(skid COLLATE pg_catalog."default");
-- the certificate status task marks expired certificates and applies the
-- retention across every tenant: status = Active AND not_after < now(), and
-- not_after < cutoff
CREATE INDEX IF NOT EXISTS idx_certificate_notafter
ON trustyca.certificate USING btree
(not_after);
--
-- Revoked certificates
--
-- ikid, serial_number and not_after are denormalized from certificate so a
-- CRL is built with a single index range scan: ikid = ? AND not_after > now().
--
CREATE TABLE IF NOT EXISTS trustyca.revoked
(
id BIGINT NOT NULL,
org_id BIGINT NULL,
project_id BIGINT NULL,
certificate_id BIGINT NOT NULL REFERENCES trustyca.certificate ON DELETE CASCADE,
ikid VARCHAR(64) COLLATE pg_catalog."default" NOT NULL,
serial_number VARCHAR(64) COLLATE pg_catalog."default" NOT NULL,
not_after TIMESTAMP(3) WITH TIME ZONE NOT NULL,
revoked_at TIMESTAMP(3) WITH TIME ZONE NOT NULL,
reason INTEGER NOT NULL,
reason_text VARCHAR(260) COLLATE pg_catalog."default" NOT NULL DEFAULT '',
CONSTRAINT revoked_pkey PRIMARY KEY (id),
CONSTRAINT unique_revoked_certificate UNIQUE (certificate_id)
);
ALTER TABLE trustyca.revoked
OWNER TO trustyca;
CREATE INDEX IF NOT EXISTS idx_revoked_ikid_notafter
ON trustyca.revoked USING btree
(ikid COLLATE pg_catalog."default", not_after);
CREATE INDEX IF NOT EXISTS idx_revoked_org_project_id_desc
ON trustyca.revoked USING btree
(org_id, project_id, id DESC);
-- OCSP and the revocation lookups address a revoked certificate by the key
-- identifier of its issuer and its serial number, which are unique per
-- certificate (unique_certificate_ikid_serial) and copied to the revocation.
CREATE UNIQUE INDEX IF NOT EXISTS idx_revoked_ikid_serial
ON trustyca.revoked USING btree
(ikid COLLATE pg_catalog."default", serial_number COLLATE pg_catalog."default");
--
-- Revoked certificates joined with their certificate rows.
--
-- id is the revocation ID (the keyset pagination key), certificate_id the
-- certificate. ListRevokedCertificates reads this view so a page of revoked
-- certificates is one query; CRL generation reads trustyca.revoked directly.
--
CREATE VIEW trustyca.vw_revoked_certificate AS
SELECT
r.id,
r.org_id,
r.project_id,
r.certificate_id,
c.skid,
c.ikid,
c.serial_number,
c.not_before,
c.not_after,
c.subject,
c.issuer,
c.sha256,
c.profile,
c.label,
c.locations,
c.metadata,
c.pem,
c.issuers_pem,
c.status,
c.created_at,
r.revoked_at,
r.reason,
r.reason_text
FROM trustyca.revoked r
JOIN trustyca.certificate c ON c.id = r.certificate_id;
--
-- CRL (current CRL per issuer; replaced on every publish)
--
CREATE TABLE IF NOT EXISTS trustyca.crl
(
id BIGINT NOT NULL,
org_id BIGINT NULL,
project_id BIGINT NULL,
issuer_id BIGINT NOT NULL REFERENCES trustyca.issuer ON DELETE CASCADE,
ikid VARCHAR(64) COLLATE pg_catalog."default" NOT NULL,
crl_number BIGINT NOT NULL,
this_update TIMESTAMP(3) WITH TIME ZONE NOT NULL,
next_update TIMESTAMP(3) WITH TIME ZONE NOT NULL,
issuer VARCHAR(260) COLLATE pg_catalog."default" NOT NULL,
pem TEXT COLLATE pg_catalog."default" NOT NULL,
locations VARCHAR(260)[] COLLATE pg_catalog."default" NOT NULL DEFAULT '{}'::character varying[],
created_at TIMESTAMP(3) WITH TIME ZONE NOT NULL DEFAULT NOW(),
CONSTRAINT crl_pkey PRIMARY KEY (id),
CONSTRAINT unique_crl_ikid UNIQUE (ikid)
);
ALTER TABLE trustyca.crl
OWNER TO trustyca;
CREATE INDEX IF NOT EXISTS idx_crl_next_update
ON trustyca.crl USING btree
(next_update);
COMMIT;