Database Schema
Megha Chandrashekharappa
-- Table: dbo.Redact
-- DROP TABLE IF EXISTS dbo."Redact";
CREATE TABLE IF NOT EXISTS dbo."Redact"
(
"Id" bigint NOT NULL GENERATED ALWAYS AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 9223372036854775807 CACHE 1 ),
"DocumentId" bigint,
"DateSent" timestamp without time zone,
"SourceImagePath" character varying(500) COLLATE pg_catalog."default",
"RedactStatus" integer,
"HasRedactions" boolean DEFAULT false,
"Errors" text COLLATE pg_catalog."default",
"CreateByUserId" bigint NOT NULL,
"CreateDate" timestamp without time zone NOT NULL DEFAULT now(),
"ModifyByUserId" bigint,
"ModifyDate" timestamp without time zone,
"DeleteByUserId" bigint,
"DeleteDate" timestamp without time zone,
"Deleted" boolean NOT NULL DEFAULT false,
"RedactResult" json,
"RedactKey" uuid,
CONSTRAINT "Redact_pkey" PRIMARY KEY ("Id")
)
TABLESPACE pg_default;
ALTER TABLE IF EXISTS dbo."Redact"
OWNER to orms_db_user;
-- dbo."RedactionKeyword" definition
-- Drop table
-- DROP TABLE dbo."RedactionKeyword";
CREATE TABLE dbo."RedactionKeyword" (
"Id" int8 GENERATED BY DEFAULT AS IDENTITY( INCREMENT BY 1 MINVALUE 1 MAXVALUE 9223372036854775807 START 1 CACHE 1 NO CYCLE) NOT NULL,
"Keyword" varchar(255) NOT NULL,
"IsDefault" bool DEFAULT false NOT NULL,
"CreateByUserId" int8 NOT NULL,
"CreateDate" timestamptz NOT NULL,
"ModifyByUserId" int8 NULL,
"ModifyDate" timestamptz NULL,
"DeleteByUserId" int8 NULL,
"DeleteDate" timestamptz NULL,
CONSTRAINT "RedactionKeyword_pkey" PRIMARY KEY ("Id")
);
-- dbo."DocumentTypeRedactionKeyword" definition
-- Drop table
-- DROP TABLE dbo."DocumentTypeRedactionKeyword";
CREATE TABLE dbo."DocumentTypeRedactionKeyword" (
"DocumentTypeId" int8 NOT NULL,
"RedactionKeywordId" int8 NOT NULL,
"CreateByUserId" int8 NOT NULL,
"CreateDate" timestamptz NOT NULL,
"ModifyByUserId" int8 NULL,
"ModifyDate" timestamptz NULL,
"DeleteByUserId" int8 NULL,
"DeleteDate" timestamptz NULL,
CONSTRAINT "DocumentTypeRedactionKeyword_pkey"
PRIMARY KEY ("DocumentTypeId", "RedactionKeywordId")
);
CREATE INDEX "IX_DocumentTypeRedactionKeyword_RedactionKeywordId"
ON dbo."DocumentTypeRedactionKeyword" USING btree ("RedactionKeywordId");
-- dbo."DocumentTypeRedactionKeyword" foreign keys
ALTER TABLE dbo."DocumentTypeRedactionKeyword"
ADD CONSTRAINT "DocumentTypeRedactionKeyword_DocumentTypeId_fkey"
FOREIGN KEY ("DocumentTypeId")
REFERENCES dbo."DocumentType"("Id") ON DELETE SET NULL;
ALTER TABLE dbo."DocumentTypeRedactionKeyword"
ADD CONSTRAINT "DocumentTypeRedactionKeyword_RedactionKeywordId_fkey"
FOREIGN KEY ("RedactionKeywordId")
REFERENCES dbo."RedactionKeyword"("Id") ON DELETE SET NULL;
DynamoDB - CoralRedactionIntegrationLog
DynamoDB - GB-Confidact-Redaction-DocTypes
DynamoDB - GB-Confidact-Redaction-Indexing-Fields
-- dbo."Tenant" definition
-- Drop table
-- DROP TABLE dbo."Tenant";
CREATE TABLE dbo."Tenant" (
"TenantID" int8 GENERATED ALWAYS AS IDENTITY( INCREMENT BY 1 MINVALUE 1 MAXVALUE 9223372036854775807 START 1 CACHE 1 NO CYCLE) NOT NULL,
"CountyID" int8 NULL,
"StateID" int8 NULL,
"DBServer" varchar(255) NULL,
"DBName" varchar(255) NULL,
"DBLogin" varchar(100) NULL,
"DBPassword" varchar(100) NULL,
"CreateByID" int8 NULL,
"CreateTime" timestamp NULL,
"UpdateByID" int8 NULL,
"UpdateTime" timestamp NULL,
"Description" varchar(50) NULL,
"CommandTimeout" varchar(50) NULL,
"RODBServer" varchar(255) NULL,
"RODBName" varchar(255) NULL,
"RODBLogin" varchar(100) NULL,
"RODBPassword" varchar(100) NULL,
"Indexing" text DEFAULT 'N'::text NOT NULL,
"AutoIndex" text DEFAULT 'N'::text NOT NULL,
"AutoRedact" text DEFAULT 'N'::text NOT NULL,
CONSTRAINT "Tenant_pkey" PRIMARY KEY ("TenantID")
);
-- dbo."Tenant" foreign keys
ALTER TABLE dbo."Tenant" ADD CONSTRAINT "FK_Coral_County_CountyID" FOREIGN KEY ("CountyID") REFERENCES dbo."County"("CountyID");
-- dbo."DocumentType" definition
-- Drop table
-- DROP TABLE dbo."DocumentType";
CREATE TABLE dbo."DocumentType" (
"Id" int8 GENERATED ALWAYS AS IDENTITY( INCREMENT BY 1 MINVALUE 1 MAXVALUE 9223372036854775807 START 1 CACHE 1 NO CYCLE) NOT NULL,
"Code" varchar(50) NOT NULL,
"Description" varchar(255) NULL,
"Active" bool DEFAULT true NULL,
"HideFromPublic" bool NULL,
"Confidential" bool NULL,
"CreateByUserId" int8 NOT NULL,
"CreateDate" timestamp DEFAULT now() NOT NULL,
"ModifyByUserId" int8 NULL,
"ModifyDate" timestamp NULL,
"DeleteByUserId" int8 NULL,
"DeleteDate" timestamp NULL,
"Deleted" bool DEFAULT false NOT NULL,
"BookId" int8 NULL,
"MarriageAssignCfnAtPost" bool DEFAULT false NULL,
"BookNumberSequenceId" int8 NULL,
"PageSequenceId" int8 NULL,
"CFNSequenceId" int8 NULL,
"TransferFormSequenceId" int8 NULL,
"ConfidentialVerbiage" varchar(500) NULL,
"ChildDocumentTypeId" int8 NULL,
"EndorsementFirstPageId" int8 NULL,
"EndorsementMiddlePageId" int8 NULL,
"EndorsementLastPageId" int8 NULL,
"EndorsementLabelId" int8 NULL,
"EndorsementCoverPageId" int8 NULL,
"EndorsementTrailingPageId" int8 NULL,
"EndorsementCopyLabelId" int8 NULL,
"Recordable" bool NULL,
"State" varchar(100) NULL,
"GLNumberId" int8 NULL,
"DefaultSaveFormat" int4 DEFAULT 0 NOT NULL,
"AutoRedaction" bool NULL,
"AutoIndex" bool NULL,
CONSTRAINT "DocumentType_pkey" PRIMARY KEY ("Id")
);
CREATE INDEX idx_documenttype_description ON dbo."DocumentType" USING btree ("Description");
CREATE INDEX idx_pk_documenttype ON dbo."DocumentType" USING btree ("Id");
-- dbo."DocumentType" foreign keys
ALTER TABLE dbo."DocumentType" ADD CONSTRAINT "Book_DocumentType_BookId_fkey" FOREIGN KEY ("BookId") REFERENCES dbo."Book"("Id");
ALTER TABLE dbo."DocumentType" ADD CONSTRAINT "DocumentType_DocumentType_ChildDocumentTypeId_fkey" FOREIGN KEY ("ChildDocumentTypeId") REFERENCES dbo."DocumentType"("Id");
ALTER TABLE dbo."DocumentType" ADD CONSTRAINT "Endorsement_DocumentType_EndorsementCopyLabelId_fkey" FOREIGN KEY ("EndorsementCopyLabelId") REFERENCES dbo."Endorsement"("Id");
ALTER TABLE dbo."DocumentType" ADD CONSTRAINT "Endorsement_DocumentType_EndorsementCoverPageId_fkey" FOREIGN KEY ("EndorsementCoverPageId") REFERENCES dbo."Endorsement"("Id");
ALTER TABLE dbo."DocumentType" ADD CONSTRAINT "Endorsement_DocumentType_EndorsementFirstPageId_fkey" FOREIGN KEY ("EndorsementFirstPageId") REFERENCES dbo."Endorsement"("Id");
ALTER TABLE dbo."DocumentType" ADD CONSTRAINT "Endorsement_DocumentType_EndorsementLabelId_fkey" FOREIGN KEY ("EndorsementLabelId") REFERENCES dbo."Endorsement"("Id");
ALTER TABLE dbo."DocumentType" ADD CONSTRAINT "Endorsement_DocumentType_EndorsementLastPageId_fkey" FOREIGN KEY ("EndorsementLastPageId") REFERENCES dbo."Endorsement"("Id");
ALTER TABLE dbo."DocumentType" ADD CONSTRAINT "Endorsement_DocumentType_EndorsementMiddlePageId_fkey" FOREIGN KEY ("EndorsementMiddlePageId") REFERENCES dbo."Endorsement"("Id");
ALTER TABLE dbo."DocumentType" ADD CONSTRAINT "Endorsement_DocumentType_EndorsementTrailingPageId_fkey" FOREIGN KEY ("EndorsementTrailingPageId") REFERENCES dbo."Endorsement"("Id");
ALTER TABLE dbo."DocumentType" ADD CONSTRAINT "GLNumber_DocumentType_GLNumberId_FK" FOREIGN KEY ("GLNumberId") REFERENCES dbo."GLNumber"("Id");
ALTER TABLE dbo."DocumentType" ADD CONSTRAINT "SequenceNumber_DocumentType_BookNumberSequenceId_fkey" FOREIGN KEY ("BookNumberSequenceId") REFERENCES dbo."SequenceNumber"("Id");
ALTER TABLE dbo."DocumentType" ADD CONSTRAINT "SequenceNumber_DocumentType_CFNSequenceId_fkey" FOREIGN KEY ("CFNSequenceId") REFERENCES dbo."SequenceNumber"("Id");
ALTER TABLE dbo."DocumentType" ADD CONSTRAINT "SequenceNumber_DocumentType_PageSequenceId_fkey" FOREIGN KEY ("PageSequenceId") REFERENCES dbo."SequenceNumber"("Id");
ALTER TABLE dbo."DocumentType" ADD CONSTRAINT "SequenceNumber_DocumentType_TransferFormSequenceId_fkey" FOREIGN KEY ("TransferFormSequenceId") REFERENCES dbo."SequenceNumber"("Id");