Окей, Джон. MySQL, нормальные формы, и чтобы потом это реально искалось, связывалось и не превращалось в помойку. Делаю схему вокруг сущности memory_item + справочники + связи many-to-many для тегов/ключевых слов/сущностей. Контент — отдельно (1:1), источники сообщений — отдельно (1:N). Дедуп — через хэш. -- MySQL 8.x -- charset/col: utf8mb4 CREATE TABLE users ( id BIGINT UNSIGNED PRIMARY KEY, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ) ENGINE=InnoDB; CREATE TABLE memory_types ( id TINYINT UNSIGNED PRIMARY KEY, code VARCHAR(32) NOT NULL UNIQUE -- note|fact|task|link|contact|quote|idea|bug|preference|other ) ENGINE=InnoDB; CREATE TABLE memory_statuses ( id TINYINT UNSIGNED PRIMARY KEY, code VARCHAR(16) NOT NULL UNIQUE -- active|archived|deleted ) ENGINE=InnoDB; CREATE TABLE content_formats ( id TINYINT UNSIGNED PRIMARY KEY, code VARCHAR(16) NOT NULL UNIQUE -- plain|markdown|html ) ENGINE=InnoDB; CREATE TABLE privacy_visibility ( id TINYINT UNSIGNED PRIMARY KEY, code VARCHAR(32) NOT NULL UNIQUE -- private|allowed_in_answers|never_quote ) ENGINE=InnoDB; CREATE TABLE sensitivity_levels ( id TINYINT UNSIGNED PRIMARY KEY, code VARCHAR(16) NOT NULL UNIQUE -- normal|sensitive ) ENGINE=InnoDB; CREATE TABLE entity_kinds ( id TINYINT UNSIGNED PRIMARY KEY, code VARCHAR(16) NOT NULL UNIQUE -- person|org|project|place|product|event ) ENGINE=InnoDB; CREATE TABLE source_channels ( id TINYINT UNSIGNED PRIMARY KEY, code VARCHAR(16) NOT NULL UNIQUE -- telegram|web|api|other ) ENGINE=InnoDB; -- Главная сущность CREATE TABLE memory_items ( id BINARY(16) PRIMARY KEY, -- UUID v4 в бинарном виде owner_user_id BIGINT UNSIGNED NOT NULL, type_id TINYINT UNSIGNED NOT NULL, status_id TINYINT UNSIGNED NOT NULL, title VARCHAR(255) NULL, topic VARCHAR(255) NULL, project VARCHAR(255) NULL, relevant_from DATETIME NULL, relevant_to DATETIME NULL, expires_at DATETIME NULL, visibility_id TINYINT UNSIGNED NOT NULL, sensitivity_id TINYINT UNSIGNED NOT NULL, content_hash BINARY(32) NOT NULL, -- SHA-256 canonical_id BINARY(16) NULL, -- если это дубль, ссылка на канон confidence DECIMAL(4,3) NOT NULL DEFAULT 0.000, user_confirmed BOOLEAN NOT NULL DEFAULT FALSE, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, CONSTRAINT fk_mem_owner FOREIGN KEY (owner_user_id) REFERENCES users(id), CONSTRAINT fk_mem_type FOREIGN KEY (type_id) REFERENCES memory_types(id), CONSTRAINT fk_mem_status FOREIGN KEY (status_id) REFERENCES memory_statuses(id), CONSTRAINT fk_mem_visibility FOREIGN KEY (visibility_id) REFERENCES privacy_visibility(id), CONSTRAINT fk_mem_sensitivity FOREIGN KEY (sensitivity_id) REFERENCES sensitivity_levels(id), CONSTRAINT fk_mem_canonical FOREIGN KEY (canonical_id) REFERENCES memory_items(id) ) ENGINE=InnoDB; CREATE INDEX idx_mem_owner_created ON memory_items(owner_user_id, created_at); CREATE INDEX idx_mem_owner_status ON memory_items(owner_user_id, status_id); CREATE INDEX idx_mem_expires ON memory_items(expires_at); CREATE INDEX idx_mem_hash_owner ON memory_items(owner_user_id, content_hash); -- Контент (1:1) CREATE TABLE memory_contents ( memory_item_id BINARY(16) PRIMARY KEY, text LONGTEXT NOT NULL, format_id TINYINT UNSIGNED NOT NULL, source_language VARCHAR(16) NULL, CONSTRAINT fk_cnt_item FOREIGN KEY (memory_item_id) REFERENCES memory_items(id) ON DELETE CASCADE, CONSTRAINT fk_cnt_format FOREIGN KEY (format_id) REFERENCES content_formats(id) ) ENGINE=InnoDB; -- Источник/привязка к диалогу (1:N) CREATE TABLE memory_sources ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, memory_item_id BINARY(16) NOT NULL, channel_id TINYINT UNSIGNED NOT NULL, chat_id VARCHAR(64) NULL, dialog_id VARCHAR(64) NULL, message_id VARCHAR(64) NULL, source_text VARCHAR(255) NULL, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, CONSTRAINT fk_src_item FOREIGN KEY (memory_item_id) REFERENCES memory_items(id) ON DELETE CASCADE, CONSTRAINT fk_src_channel FOREIGN KEY (channel_id) REFERENCES source_channels(id) ) ENGINE=InnoDB; CREATE INDEX idx_src_item ON memory_sources(memory_item_id); CREATE INDEX idx_src_channel_chat ON memory_sources(channel_id, chat_id); -- Вложения (1:N) CREATE TABLE attachment_kinds ( id TINYINT UNSIGNED PRIMARY KEY, code VARCHAR(16) NOT NULL UNIQUE -- url|file|image ) ENGINE=InnoDB; CREATE TABLE memory_attachments ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, memory_item_id BINARY(16) NOT NULL, kind_id TINYINT UNSIGNED NOT NULL, value TEXT NOT NULL, CONSTRAINT fk_att_item FOREIGN KEY (memory_item_id) REFERENCES memory_items(id) ON DELETE CASCADE, CONSTRAINT fk_att_kind FOREIGN KEY (kind_id) REFERENCES attachment_kinds(id) ) ENGINE=InnoDB; CREATE INDEX idx_att_item ON memory_attachments(memory_item_id); -- Теги (M:N) CREATE TABLE tags ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, name VARCHAR(64) NOT NULL, UNIQUE KEY uq_tag_name (name) ) ENGINE=InnoDB; CREATE TABLE memory_item_tags ( memory_item_id BINARY(16) NOT NULL, tag_id BIGINT UNSIGNED NOT NULL, PRIMARY KEY (memory_item_id, tag_id), CONSTRAINT fk_mit_item FOREIGN KEY (memory_item_id) REFERENCES memory_items(id) ON DELETE CASCADE, CONSTRAINT fk_mit_tag FOREIGN KEY (tag_id) REFERENCES tags(id) ON DELETE RESTRICT ) ENGINE=InnoDB; CREATE INDEX idx_mit_tag ON memory_item_tags(tag_id); -- Ключевые слова (M:N) — отдельно от тегов, чтобы не мешать «ручное» и «авто» CREATE TABLE keywords ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, keyword VARCHAR(64) NOT NULL, UNIQUE KEY uq_keyword (keyword) ) ENGINE=InnoDB; CREATE TABLE memory_item_keywords ( memory_item_id BINARY(16) NOT NULL, keyword_id BIGINT UNSIGNED NOT NULL, origin ENUM('user','auto') NOT NULL DEFAULT 'auto', PRIMARY KEY (memory_item_id, keyword_id), CONSTRAINT fk_mik_item FOREIGN KEY (memory_item_id) REFERENCES memory_items(id) ON DELETE CASCADE, CONSTRAINT fk_mik_kw FOREIGN KEY (keyword_id) REFERENCES keywords(id) ON DELETE RESTRICT ) ENGINE=InnoDB; CREATE INDEX idx_mik_kw ON memory_item_keywords(keyword_id); -- Сущности (person/org/...) (M:N) CREATE TABLE entities ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, kind_id TINYINT UNSIGNED NOT NULL, value VARCHAR(255) NOT NULL, UNIQUE KEY uq_entity (kind_id, value), CONSTRAINT fk_ent_kind FOREIGN KEY (kind_id) REFERENCES entity_kinds(id) ) ENGINE=InnoDB; CREATE TABLE memory_item_entities ( memory_item_id BINARY(16) NOT NULL, entity_id BIGINT UNSIGNED NOT NULL, PRIMARY KEY (memory_item_id, entity_id), CONSTRAINT fk_mie_item FOREIGN KEY (memory_item_id) REFERENCES memory_items(id) ON DELETE CASCADE, CONSTRAINT fk_mie_ent FOREIGN KEY (entity_id) REFERENCES entities(id) ON DELETE RESTRICT ) ENGINE=InnoDB; CREATE INDEX idx_mie_ent ON memory_item_entities(entity_id); -- Алиасы/фразы для поиска (1:N) CREATE TABLE memory_aliases ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, memory_item_id BINARY(16) NOT NULL, alias_text VARCHAR(255) NOT NULL, kind ENUM('alias','search_phrase') NOT NULL DEFAULT 'alias', CONSTRAINT fk_alias_item FOREIGN KEY (memory_item_id) REFERENCES memory_items(id) ON DELETE CASCADE ) ENGINE=InnoDB; CREATE INDEX idx_alias_item ON memory_aliases(memory_item_id); -- (Опционально) полнотекст по контенту -- В MySQL InnoDB FULLTEXT работает, но нюансы с морфологией RU. -- Если надо — добавим ngram parser. ALTER TABLE memory_contents ADD FULLTEXT KEY ftx_memory_text (text); Если хочешь, я подгоню это под твой реальный поиск. Скажи две вещи: ты будешь искать по-русски (нужна нормальная морфология/стемминг) или достаточно простого FULLTEXT/LIKE? И UUID ты хочешь хранить как BINARY(16) (быстро) или как строку CHAR(36) (удобно глазами)?