query-orchestration/internal/database/migrations_archive/00000000000005_documents.up.sql

CREATE TABLE documents (
    id uuid primary key DEFAULT uuid_generate_v7(),
    clientId varchar(255) not null,
    hash text not null,
    foreign key (clientId) references clients(clientId),
    unique(clientId, hash)
);

CREATE TABLE documentUploads (
    id uuid primary key DEFAULT uuid_generate_v7(),
    bucket text not null,
    key text not null,
    clientId varchar(255) not null,
    part unsignedsmallint not null,
    createdAt timestamp not null,
    foreign key (clientId) references clients(clientId)
);

CREATE TABLE documentEntries (
    id uuid primary key DEFAULT uuid_generate_v7(),
    documentId uuid not null,
    bucket text not null,
    key text not null,
    foreign key (documentId) references documents(id)
);

CREATE TYPE cleanFailType AS ENUM (
    'invalid_mimetype',
    'invalid_read',
    'invalid_read_pages',
    'zero_page_count',
    'large_file',
    'small_dimensions',
    'large_dimensions',
    'small_dpi',
    'large_dpi'
);
CREATE TYPE cleanMimeType AS ENUM ('application/pdf');

CREATE TABLE documentCleans (
    id uuid primary key DEFAULT uuid_generate_v7(),
    documentId uuid not null,
    bucket text,
    key text,
    hash text,
    mimetype cleanMimeType,
    fail cleanFailType,
    foreign key (documentId) references documents(id),
    CONSTRAINT bucket_and_key_together CHECK ((bucket IS NULL) = (key IS NULL) and (bucket is null) = (mimetype is null) and (bucket is null) = (hash is null)),
    CONSTRAINT location_xor_fail CHECK (
    (bucket IS NOT NULL) != (fail IS NOT NULL)
  )
);

CREATE TABLE documentCleanEntries (
    id uuid primary key DEFAULT uuid_generate_v7(),
    cleanId uuid not null,
    version bigint not null,
    foreign key (cleanId) references documentCleans(id)
);

CREATE TABLE documentTextExtractions (
    id uuid primary key DEFAULT uuid_generate_v7(),
    cleanId uuid not null,
    bucket text not null,
    key text not null,
    hash text not null,
    createdAt timestamp not null,
    part unsignedsmallint not null,
    foreign key (cleanId) references documentCleans(id)
);

CREATE TABLE documentTextExtractionEntries (
    id uuid primary key DEFAULT uuid_generate_v7(),
    textId uuid not null,
    version bigint not null,
    foreign key (textId) references documentTextExtractions(id)
);