Database: coral_dev

Documentation of the database coral_dev:
CORAL Dev - Database.xlsx

Table scripts for PostgreSQL

-- Table: dbo.DocumentType

-- DROP TABLE IF EXISTS dbo."DocumentType";

CREATE TABLE IF NOT EXISTS dbo."DocumentType"
(
    "Id" bigint NOT NULL GENERATED ALWAYS AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 9223372036854775807 CACHE 1 ),
    "Code" character varying(50) COLLATE pg_catalog."default" NOT NULL,
    "Description" character varying(255) COLLATE pg_catalog."default",
    "Active" boolean DEFAULT true,
    "HideFromPublic" boolean,
    "Confidential" boolean,
    "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,
    CONSTRAINT "DocumentType_pkey" PRIMARY KEY ("Id")
)

TABLESPACE pg_default;

ALTER TABLE IF EXISTS dbo."DocumentType"
    OWNER to orms_db_user;

-- Table: dbo.Fee

-- DROP TABLE IF EXISTS dbo."Fee";

CREATE TABLE IF NOT EXISTS dbo."Fee"
(
    "Id" bigint NOT NULL GENERATED ALWAYS AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 9223372036854775807 CACHE 1 ),
    "Name" character varying(50) COLLATE pg_catalog."default" NOT NULL,
    "Description" character varying(150) COLLATE pg_catalog."default",
    "Rate" numeric(19,4),
    "Active" boolean DEFAULT true,
    "IsTax" boolean DEFAULT false,
    "CreateByUserId" bigint NOT NULL,
    "CreateDate" timestamp without time zone NOT NULL,
    "ModifyByUserId" bigint,
    "ModifyDate" timestamp without time zone,
    "DeleteByUserId" bigint,
    "DeleteDate" timestamp without time zone,
    "Deleted" boolean NOT NULL DEFAULT false,
    CONSTRAINT "Fee_pkey" PRIMARY KEY ("Id")
)

TABLESPACE pg_default;

ALTER TABLE IF EXISTS dbo."Fee"
    OWNER to orms_db_user;

-- Table: dbo.DocumentTypeFee

-- DROP TABLE IF EXISTS dbo."DocumentTypeFee";

CREATE TABLE IF NOT EXISTS dbo."DocumentTypeFee"
(
    "Id" bigint NOT NULL GENERATED ALWAYS AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 9223372036854775807 CACHE 1 ),
    "DocumentTypeId" bigint NOT NULL,
    "FeeId" bigint NOT NULL,
    "CreateByUserId" bigint NOT NULL,
    "CreateDate" timestamp without time zone NOT NULL,
    "ModifyByUserId" bigint,
    "ModifyDate" timestamp without time zone,
    "DeleteByUserId" bigint,
    "DeleteDate" timestamp without time zone,
    "Deleted" boolean NOT NULL DEFAULT false,
    CONSTRAINT "DocumentTypeFee_pkey" PRIMARY KEY ("Id"),
    CONSTRAINT "DocumentType_DocumentTypeId_fkey" FOREIGN KEY ("DocumentTypeId")
        REFERENCES dbo."DocumentType" ("Id") MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION,
    CONSTRAINT "Fee_FeeId_fkey" FOREIGN KEY ("FeeId")
        REFERENCES dbo."Fee" ("Id") MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION
)

TABLESPACE pg_default;

ALTER TABLE IF EXISTS dbo."DocumentTypeFee"
    OWNER to orms_db_user;

-- Table: dbo.Audit

-- DROP TABLE IF EXISTS dbo."Audit";

CREATE TABLE IF NOT EXISTS dbo."Audit"
(
    "Id" bigint NOT NULL GENERATED ALWAYS AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 9223372036854775807 CACHE 1 ),
    "TableName" character varying(100) COLLATE pg_catalog."default",
    "TableId" bigint,
	"Action" character varying(50) COLLATE pg_catalog."default",
	"OldValue" text COLLATE pg_catalog."default",
	"NewValue" text COLLATE pg_catalog."default",
    "CreateByUserId" bigint NOT NULL,
    "CreateDate" timestamp without time zone NOT NULL,
    "ModifyByUserId" bigint,
    "ModifyDate" timestamp without time zone,
    CONSTRAINT "Audit_pkey" PRIMARY KEY ("Id")
)

TABLESPACE pg_default;

ALTER TABLE IF EXISTS dbo."Audit"
    OWNER to orms_db_user;

-- Table: dbo.Agent

-- DROP TABLE IF EXISTS dbo."Agent";

CREATE TABLE IF NOT EXISTS dbo."Agent"
(
    "Id" bigint NOT NULL GENERATED ALWAYS AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 9223372036854775807 CACHE 1 ),
    "Name" character varying(255) COLLATE pg_catalog."default" NOT NULL,
	"Address1" character varying(255) COLLATE pg_catalog."default",
	"Address2" character varying(255) COLLATE pg_catalog."default",
	"City" character varying(100) COLLATE pg_catalog."default",
	"State" character varying(100) COLLATE pg_catalog."default",
	"Zip" character varying(50) COLLATE pg_catalog."default",
	"Phone" character varying(50) COLLATE pg_catalog."default",
	"Email" character varying(50) COLLATE pg_catalog."default",
	"Active" boolean DEFAULT true,
    "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,
    CONSTRAINT "Agent_pkey" PRIMARY KEY ("Id")
)

TABLESPACE pg_default;

ALTER TABLE IF EXISTS dbo."Agent"
    OWNER to orms_db_user;

-- Table: dbo.RoleType

-- DROP TABLE IF EXISTS dbo."RoleType";

CREATE TABLE IF NOT EXISTS dbo."RoleType"
(
    "Id" bigint NOT NULL GENERATED ALWAYS AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 9223372036854775807 CACHE 1 ),
    "Name" character varying(50) COLLATE pg_catalog."default" NOT NULL,
    "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,
    CONSTRAINT "RoleType_pkey" PRIMARY KEY ("Id")
)

TABLESPACE pg_default;

ALTER TABLE IF EXISTS dbo."RoleType"
    OWNER to orms_db_user;

-- Table: dbo.Role

-- DROP TABLE IF EXISTS dbo."Role";

CREATE TABLE IF NOT EXISTS dbo."Role"
(
    "Id" bigint NOT NULL GENERATED ALWAYS AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 9223372036854775807 CACHE 1 ),
    "RoleTypeId" bigint NOT NULL,
    "Name" character varying(50) COLLATE pg_catalog."default" NOT NULL,
    "Description" character varying(255) COLLATE pg_catalog."default",
    "Active" boolean DEFAULT true,
    "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,
    CONSTRAINT "Role_pkey" PRIMARY KEY ("Id"),
    CONSTRAINT "RoleType_RoleTypeId_fkey" FOREIGN KEY ("RoleTypeId")
        REFERENCES dbo."RoleType" ("Id") MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION
        NOT VALID
)

TABLESPACE pg_default;

ALTER TABLE IF EXISTS dbo."Role"
    OWNER to orms_db_user;

-- Table: dbo.Right

-- DROP TABLE IF EXISTS dbo."Right";

CREATE TABLE IF NOT EXISTS dbo."Right"
(
    "Id" bigint NOT NULL GENERATED ALWAYS AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 9223372036854775807 CACHE 1 ),
    "Name" character varying(50) COLLATE pg_catalog."default" NOT NULL,
    "Description" character varying(255) 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,
    CONSTRAINT "Right_pkey" PRIMARY KEY ("Id")
)

TABLESPACE pg_default;

ALTER TABLE IF EXISTS dbo."Right"
    OWNER to orms_db_user;

-- Table: dbo.RoleRight

-- DROP TABLE IF EXISTS dbo."RoleRight";

CREATE TABLE IF NOT EXISTS dbo."RoleRight"
(
    "Id" bigint NOT NULL GENERATED ALWAYS AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 9223372036854775807 CACHE 1 ),
    "RoleId" bigint NOT NULL,
    "RightId" bigint NOT NULL,
    "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,
    CONSTRAINT "RoleRight_pkey" PRIMARY KEY ("Id"),
    CONSTRAINT "Right_RightId_fkey" FOREIGN KEY ("RightId")
        REFERENCES dbo."Right" ("Id") MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION,
    CONSTRAINT "Role_RoleId_fkey" FOREIGN KEY ("RoleId")
        REFERENCES dbo."Role" ("Id") MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION
)

TABLESPACE pg_default;

ALTER TABLE IF EXISTS dbo."RoleRight"
    OWNER to orms_db_user;

Transactions DB Diagram

Location

-- Table: dbo.Location

-- DROP TABLE IF EXISTS dbo."Location";

CREATE TABLE IF NOT EXISTS dbo."Location"
(
    "Id" bigint NOT NULL GENERATED ALWAYS AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 9223372036854775807 CACHE 1 ),
    "Name" character varying(50) COLLATE pg_catalog."default" NOT NULL,
	"Active" boolean DEFAULT true,
    "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,
    CONSTRAINT "Location_pkey" PRIMARY KEY ("Id")
)

TABLESPACE pg_default;

ALTER TABLE IF EXISTS dbo."Location"
    OWNER to orms_db_user;
	
INSERT INTO dbo."Location"(
	"Name", "Active", "CreateByUserId")
	VALUES ('Test Location', true, 123);

Source

-- Table: dbo.Source

-- DROP TABLE IF EXISTS dbo."Source";

CREATE TABLE IF NOT EXISTS dbo."Source"
(
    "Id" bigint NOT NULL GENERATED ALWAYS AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 9223372036854775807 CACHE 1 ),
    "Name" character varying(50) COLLATE pg_catalog."default" NOT NULL,
	"Active" boolean DEFAULT true,
    "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,
    CONSTRAINT "Source_pkey" PRIMARY KEY ("Id")
)

TABLESPACE pg_default;

ALTER TABLE IF EXISTS dbo."Source"
    OWNER to orms_db_user;
	
INSERT INTO dbo."Source"("Name", "CreateByUserId")
	VALUES ('eRecording', 123);
INSERT INTO dbo."Source"("Name", "CreateByUserId")
	VALUES ('Over the Counter', 123);
INSERT INTO dbo."Source"("Name", "CreateByUserId")
	VALUES ('United States Postal Service', 123);
INSERT INTO dbo."Source"("Name", "CreateByUserId")
	VALUES ('UPS', 123);
INSERT INTO dbo."Source"("Name", "CreateByUserId")
	VALUES ('Fedex', 123);
INSERT INTO dbo."Source"("Name", "CreateByUserId")
	VALUES ('Third Party Mail Delivery Service', 123);

Transaction

-- Table: dbo.Transaction

-- DROP TABLE IF EXISTS dbo."Transaction";

CREATE TABLE IF NOT EXISTS dbo."Transaction"
(
    "Id" bigint NOT NULL GENERATED ALWAYS AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 9223372036854775807 CACHE 1 ),
    "LocationId" bigint NOT NULL,
	"SourceId" bigint NOT NULL,
    "Active" boolean DEFAULT true,
	"Status" character varying(50) 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,
    CONSTRAINT "Transaction_pkey" PRIMARY KEY ("Id"),
    CONSTRAINT "Location_LocationId_fkey" FOREIGN KEY ("LocationId")
        REFERENCES dbo."Location" ("Id") MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION
        NOT VALID,
	CONSTRAINT "Source_SourceId_fkey" FOREIGN KEY ("SourceId")
        REFERENCES dbo."Source" ("Id") MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION
        NOT VALID
)

TABLESPACE pg_default;

ALTER TABLE IF EXISTS dbo."TransactionStatus"
    OWNER to orms_db_user;

Catalis Official Records

Database: coral_dev

Related content