party_relation

Column	Type	PK	Required	Default
party_relation_id	VARCHAR	Yes	Yes
party_id	VARCHAR	No	Yes
related_party_id	VARCHAR	No	Yes
relation_type_id	ULID	No	Yes
elaboration	TEXT	No	No
created_at	TIMESTAMPTZ	No	No	CURRENT_TIMESTAMP
created_by	TEXT	No	No	'UNKNOWN'
updated_at	TIMESTAMPTZ	No	No
updated_by	TEXT	No	No
deleted_at	TIMESTAMPTZ	No	No
deleted_by	TEXT	No	No
activity_log	TEXT	No	No

Foreign Keys

Column Name	Foreign Key
relation_type_id	relation_type_id references party_relation_type.party_relation_type_id
related_party_id	related_party_id references party.party_id
party_id	party_id references party.party_id

Indexes

Column Name	Index Name
party_id	idx_party_relation__party_id__related_party_id__relation_type_id
related_party_id	idx_party_relation__party_id__related_party_id__relation_type_id
relation_type_id	idx_party_relation__party_id__related_party_id__relation_type_id
party_id	sqlite_autoindex_party_relation_2
related_party_id	sqlite_autoindex_party_relation_2
relation_type_id	sqlite_autoindex_party_relation_2
party_relation_id	sqlite_autoindex_party_relation_1

SQL DDL

CREATE TABLE "party_relation" (
    "party_relation_id" VARCHAR PRIMARY KEY NOT NULL,
    "party_id" VARCHAR NOT NULL,
    "related_party_id" VARCHAR NOT NULL,
    "relation_type_id" ULID NOT NULL,
    "elaboration" TEXT CHECK(json_valid(elaboration) OR elaboration IS NULL),
    "created_at" TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    "created_by" TEXT DEFAULT 'UNKNOWN',
    "updated_at" TIMESTAMPTZ,
    "updated_by" TEXT,
    "deleted_at" TIMESTAMPTZ,
    "deleted_by" TEXT,
    "activity_log" TEXT,
    FOREIGN KEY("party_id") REFERENCES "party"("party_id"),
    FOREIGN KEY("related_party_id") REFERENCES "party"("party_id"),
    FOREIGN KEY("relation_type_id") REFERENCES "party_relation_type"("party_relation_type_id"),
    UNIQUE("party_id", "related_party_id", "relation_type_id")
)