begin transaction;

create domain NixPath as bytea
    check (length(value) = 20);
create domain SHA256Hash as bytea
    check (length(value) = 16);
create domain Ed25519Public as bytea
    check (length(value) = 32);
create domain SignatureBytes as bytea
    check (length(value) = 64);

create table PathName (
    hash NixPath not null primary key,
    -- Split off into a separate PathName table since a reference only requires
    -- a PathName exists but not necessarily a full NarInfo
    name text not null
);

-- Yoinked from https://git.lix.systems/lix-project/lix/src/e34833c0253340f47dc0add8609eb86cf9cba19b/src/libstore/binary-cache-store.cc#L151-L158
create type Compression as enum (
    'none',
    'bzip2',
    'zstd',
    'xz',
    'lzip',
    'lz4',
    'br'
);

-- There are no non sha256 CA hashes in the wild and we can honestly just tell anyone sending us one to go away
create type CAType as enum (
    'textsha256',
    'fixedrecsha256',
    'fixedflatsha256'
);

create table NarInfo (
    -- URL not needed (derivable from file_hash and compression)
    file_size bigint not null,
    nar_size bigint not null,

    hash NixPath primary key not null references PathName(hash),
    file_hash SHA256Hash not null,
    nar_hash SHA256Hash not null,
    ca_hash SHA256Hash,

    compression Compression not null,
    ca_type CAType
);

create table Reference (
    target NixPath not null references PathName(hash),
    by_narinfo NixPath not null references NarInfo(hash),
    primary key (target, by_narinfo)
);

create index reference_by_target on Reference using hash (target);
create index reference_by_referencer on Reference using hash (by_narinfo);

create table KeyName (
    id serial primary key,
    name text not null
);

comment on table KeyName is 'The Nix name for a key, which is often, but not strictly, related to the domain name';

-- FIXME: partitioning the system for security? e.g. can we make it so that the app *cannot* mess with public key validity periods?
create table BuilderPublicKey (
    id integer primary key references KeyName(id),
    public_key Ed25519Public not null,
    comment text,
    valid_from timestamptz,
    valid_until timestamptz
);

comment on table BuilderPublicKey is 'Public keys which iron-seal is willing to re-sign paths signed with';
comment on column BuilderPublicKey.valid_from is 'Signature time when the public key is first trusted for new signatures. If null, from a time in the indefinite past.';
comment on column BuilderPublicKey.valid_until is 'Signature time when the public key is last trusted for new signatures. If null, to a time in the indefinite future.';

create table OwnPublicKey (
    id integer primary key references KeyName(id),
    public_key Ed25519Public not null,
    comment text,
    valid_from timestamptz,
    valid_until timestamptz
);

comment on table OwnPublicKey is 'Public keys which iron-seal will sign paths with during their validity period';
comment on column OwnPublicKey.valid_from is 'Signature time when the public key will first be used for new signatures. If null, from a time in the indefinite past.';
comment on column OwnPublicKey.valid_until is 'Signature time when the public key will last be used for new signatures. If null, to a time in the indefinite future.';

create table Signature (
    path NixPath references NarInfo(hash),

    id serial primary key,
    signer integer not null references KeyName(id),
    sig_bytes SignatureBytes not null,
    signed_at timestamptz not null
);

comment on column Signature.signed_at is 'When the path was signed. For s3-sourced paths we have a reliable idea of it if we take the narinfo date as the signature date (and can assume there is no backdating by trusting s3)';

create unique index signature_path_only_signed_once on Signature (signer, path);

-- Definitely want to be able to look up signatures by store path
create index signature_by_path on Signature using hash (path);
-- Definitely want to be able to look up paths signed by a key
create index signature_by_signer on Signature (signer);

rollback;