schema.sql

-- Certo — Postgres schema (DBA-applied)
--
-- The production profile runs Hibernate ddl-auto=validate and creates nothing: apply this script to a
-- fresh database before first start. It is GENERATED from the JPA entities (PostgreSQL dialect) and kept
-- in sync by PostgresSchemaTest, which regenerates it on every build and fails on drift — so this file is
-- authoritative for a fresh schema, and any diff the test reports is exactly what a DBA must translate
-- into an ALTER for live databases. Do not edit by hand; regenerate via the test.
create table certificate (version bigint not null, issuer varchar(65535), revisions varchar(65535), validator varchar(65535), area_of_application varchar(255), certificate_id varchar(255) not null, certificate_type varchar(255), certificate_type_version varchar(255), lifecycle_status varchar(255) check ((lifecycle_status in ('AVAILABLE','WITHDRAWN'))), participant_context_id varchar(255), registration_number varchar(255), primary key (certificate_id));
create table certificate_certified_location (address_id varchar(255), area_of_application varchar(255), certificate_id varchar(255) not null, legal_entity_id varchar(255), location_role varchar(255) check ((location_role in ('MAIN_LOCATION','ENCLOSED_LOCATION','REMOTE_SUPPORT_LOCATION','EXTENDED_MANUFACTURING_SITE'))), site_id varchar(255));
create table consumer_certificate_exchange (acceptance_reported boolean not null, revision integer, version bigint not null, acceptance_errors varchar(65535), fulfillment_errors varchar(65535), acceptance_status varchar(255) check ((acceptance_status in ('ACCEPTED','REJECTED'))), certificate_id varchar(255), exchange_id varchar(255) not null, fulfillment_status varchar(255) not null check ((fulfillment_status in ('RECEIVED','FULFILLED','DECLINED'))), participant_context_id varchar(255) not null, provider_id varchar(255) not null, primary key (exchange_id));
create table document (created_date date, document_id varchar(255) not null, language varchar(255), media_type varchar(255), participant_context_id varchar(255), content bytea, primary key (document_id));
create table exchange_binding (lock_version bigint not null, certificate_id varchar(255), counterparty_role varchar(255) not null check ((counterparty_role in ('CONSUMER','PROVIDER'))), exchange_id varchar(255) not null, peer_did varchar(255), protocol_id varchar(255), primary key (counterparty_role, exchange_id));
create table exchange_requested_location (address_id varchar(255), exchange_id varchar(255) not null, legal_entity_id varchar(255), site_id varchar(255));
create table known_certificate (revision integer, valid_from date, valid_until date, version bigint not null, certificate_id varchar(255) not null, certificate_type varchar(255), id varchar(255) not null, lifecycle_status varchar(255) check ((lifecycle_status in ('AVAILABLE','WITHDRAWN'))), participant_context_id varchar(255) not null, primary key (id), constraint uk_known_certificate_tenant_cert unique (participant_context_id, certificate_id));
create table known_certificate_certified_location (address_id varchar(255), area_of_application varchar(255), known_certificate_id varchar(255) not null, legal_entity_id varchar(255), location_role varchar(255) check ((location_role in ('MAIN_LOCATION','ENCLOSED_LOCATION','REMOTE_SUPPORT_LOCATION','EXTENDED_MANUFACTURING_SITE'))), site_id varchar(255));
create table participant_context (version bigint not null, dataspace_identifier varchar(255), did varchar(255) unique, participant_context_id varchar(255) not null, primary key (participant_context_id));
create table processed_event (first_seen timestamp(6) with time zone, event_key varchar(255) not null, primary key (event_key));
create table provider_certificate_exchange (revision integer not null, requested_at timestamp(6) with time zone, version bigint not null, acceptance_errors varchar(65535), fulfillment_errors varchar(65535), acceptance_status varchar(255) check ((acceptance_status in ('ACCEPTED','REJECTED'))), certificate_id varchar(255), counterparty_id varchar(255) not null, exchange_id varchar(255) not null, fulfillment_status varchar(255) not null check ((fulfillment_status in ('RECEIVED','FULFILLED','DECLINED'))), message_id varchar(255), participant_context_id varchar(255) not null, requested_type varchar(255), primary key (exchange_id), constraint uk_exchange_message unique (participant_context_id, counterparty_id, message_id));
alter table if exists certificate_certified_location add constraint FKto2f6uapaq3e1opcv5p82u15u foreign key (certificate_id) references certificate;
alter table if exists exchange_requested_location add constraint FK1yjcu6pqe035cqha2b3ibuqo2 foreign key (exchange_id) references provider_certificate_exchange;
alter table if exists known_certificate_certified_location add constraint FKmud3ttl12bm0m7f1wl01qfc4v foreign key (known_certificate_id) references known_certificate;