create_statements.postgre.sql

CREATE TABLE item_text (
    item_uuid UUID NOT NULL PRIMARY KEY REFERENCES item,
	-- ^ Generated in referenced table via gen_random_uuid, originally from pgcrypto extension
    tenant_id INTEGER NOT NULL REFERENCES tenant,
    item_subject TEXT NOT NULL,
    item_body TEXT NOT NULL,
    item_body_start TEXT NOT NULL generated always AS (left(item_body, 400)) stored,
    sentence_offsets_array INTEGER[][2]
);
-- CREATE INDEX item_text_tenant_index ON conversations.item_text(tenant_id);
-- CREATE INDEX item_text_item_subject_unaccent_trigram_gin_index ON conversations.item_text
--         USING GIN(_st_immutable_unaccent(item_subject) sturdy_common.gin_trgm_ops);
-- CREATE INDEX item_text_item_body_unaccent_trigram_gin_index ON conversations.item_text
--         USING GIN(_st_immutable_unaccent(item_body) sturdy_common.gin_trgm_ops);


-- There are historical reasons within our project that the primary key is not just item_uuid
-- and that a table constraint rather than a column constraint is used on item_uuid.
CREATE TABLE conversation_item (
    conversation_uuid UUID NOT NULL REFERENCES conversation,
    item_uuid UUID NOT NULL REFERENCES item,
    CONSTRAINT conversation_item_item_uuid_key UNIQUE(item_uuid),
	-- ^ We get a free btree index on item_uuid from this so we don't create
	-- another below
    PRIMARY KEY(conversation_uuid, item_uuid),
    tenant_id INTEGER NOT NULL REFERENCES tenant
);
CREATE INDEX conversation_item_item_hash_index ON conversation_item USING hash(item_uuid);

-- CREATE INDEX conversation_item_conversation_hash_index ON conversation_item USING hash(conversation_uuid);
-- CREATE INDEX conversation_item_conv_only_index ON conversation_item(conversation_uuid); -- btree