Окей, Джон. Держи финальное ТЗ под MySQL 8.x: справочники — таблицами, поиск — FULLTEXT по title + text, дедуп — strict, идентификатор — BIGINT AUTO_INCREMENT. Без «опционально» там, где это ломает реализацию. ТЕХНИЧЕСКОЕ ЗАДАНИЕ Проект: модуль хранения ‘Memory’ СУБД: MySQL 8.x (InnoDB, utf8mb4) 1. Цель Реализовать подсистему хранения пользовательских элементов памяти (заметки/факты/ссылки/идеи/задачи и т.п.) с возможностью: — создавать/обновлять/архивировать/удалять (soft delete); — искать по тексту (FULLTEXT) и по якорям (теги/ключевые слова/сущности/алиасы); — фильтровать по типам/статусам/приватности/времени; — предотвращать дубли (strict dedup по хэшу контента). 2. Термины и сущности MemoryItem — основная запись памяти. Content — текст и формат (1:1 к MemoryItem). Anchors — якоря поиска: tags, keywords, entities, aliases/search_phrases. Source — источник сохранения (канал/чат/сообщение/диалог) (1:N). Attachment — вложение (url/file/image) (1:N). 3. Область работ (Scope) В рамках работ: — проектирование и создание схемы БД в нормальных формах; — реализация CRUD для MemoryItem и связанных сущностей; — реализация поиска: FULLTEXT + фильтры + поиск по anchors; — реализация strict-дедупликации; — обеспечение целостности данных (FK, каскады, уникальности); — подготовка миграций/DDL. Вне рамок: — векторный поиск/embeddings; — внешние поисковые движки/морфология (оставляем MySQL FULLTEXT); — UI. 4. Функциональные требования 4.1 Создание MemoryItem Система должна создавать MemoryItem со следующими полями: — owner_user_id (обязательно); — type (из справочника memory_types) (обязательно); — status (из справочника memory_statuses) (обязательно, по умолчанию active); — title (nullable); — content.text (обязательно), content.format (обязательно), content.source_language (nullable); — topic/project (nullable); — time: relevant_from/relevant_to/expires_at (nullable); — privacy: visibility, sensitivity (обязательно); — anchors: tags (0..N), keywords (0..N, с origin user/auto), entities (0..N), aliases/search_phrases (0..N); — attachments (0..N); — sources (0..N). 4.2 Дедупликация (strict) При создании/обновлении контента система обязана: — вычислять content_hash = SHA-256(normalized_text); — проверять уникальность по (owner_user_id, content_hash); Если дубль найден: — новая запись не создаётся (при create); — при update операция отклоняется (ошибка конфликта) либо возвращается id существующей записи (конкретное поведение фиксируется в API-контракте проекта; по умолчанию: ошибка 409). Нормализация текста для хэша Минимальные правила: — trim; — приведение CRLF/LF к LF; — схлопывание повторных пробелов до одного. 4.3 Обновление MemoryItem Должна быть возможность: — менять status (active/archived/deleted); — редактировать title/topic/project/time/privacy; — редактировать content (с пересчётом content_hash и проверкой на дубль); — управлять anchors/attachments/sources (добавление/удаление). 4.4 Удаление Только soft delete: status=deleted. Физическое удаление не реализуется в рамках данного ТЗ. 4.5 Поиск Система должна поддерживать: — FULLTEXT поиск по memory_contents.search_text (включает title + text); — фильтры: owner_user_id (обязателен), type, status, project, topic, visibility, sensitivity; — фильтры по времени: created_at range, expires_at (актуальные/просроченные); — фильтрацию/поиск по tags/keywords/entities. Формат результата поиска Минимально: id, type, status, title, created_at, updated_at, expires_at (и при необходимости score от FULLTEXT). 5. Требования к данным и БД 5.1 СУБД и кодировка MySQL 8.x, InnoDB, utf8mb4 (utf8mb4_0900_ai_ci). 5.2 Нормальные формы — справочники: отдельные таблицы; — контент: отдельная таблица 1:1; — tags/keywords/entities: отдельные таблицы + M:N связки; — sources/attachments/aliases: 1:N. 5.3 Индексы (обязательные) — memory_items(owner_user_id, created_at) — memory_items(owner_user_id, status_id) — memory_items(expires_at) — UNIQUE(memory_items.owner_user_id, memory_items.content_hash) — FULLTEXT(memory_contents.search_text) — индексы на M:N таблицах по второму ключу (tag_id/keyword_id/entity_id) 6. Логический API-контракт (без привязки к транспорту) — CreateMemoryItem(payload) - {id, duplicate:boolean} — GetMemoryItem(id) — UpdateMemoryItem(id, patch) — DeleteMemoryItem(id) (soft) — ArchiveMemoryItem(id) / RestoreMemoryItem(id) — SearchMemoryItems(query, filters, pagination) — ListMemoryItems(filters, pagination) 7. Нефункциональные требования — Безопасность: доступ строго по owner_user_id. — Производительность: поиск и листинг должны использовать индексы; обязательна пагинация. — Надёжность: все связи через FK; каскады на зависимых таблицах. — Логирование: sensitive-контент не логировать в открытом виде. 8. Приёмка (Definition of Done) Готово, если: — DDL разворачивается без ручных правок; — CRUD работает; — strict-дедуп работает (уникальность по owner_user_id+content_hash); — FULLTEXT поиск по title+text работает; — фильтры и поиск по anchors работают; — soft delete работает; — целостность данных обеспечена FK и каскадами. ПРИЛОЖЕНИЕ A. DDL (MySQL 8.x) -- charset: utf8mb4, collation: utf8mb4_0900_ai_ci 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 ) ENGINE=InnoDB; CREATE TABLE memory_statuses ( id TINYINT UNSIGNED PRIMARY KEY, code VARCHAR(16) NOT NULL UNIQUE ) ENGINE=InnoDB; CREATE TABLE content_formats ( id TINYINT UNSIGNED PRIMARY KEY, code VARCHAR(16) NOT NULL UNIQUE ) ENGINE=InnoDB; CREATE TABLE privacy_visibility ( id TINYINT UNSIGNED PRIMARY KEY, code VARCHAR(32) NOT NULL UNIQUE ) ENGINE=InnoDB; CREATE TABLE sensitivity_levels ( id TINYINT UNSIGNED PRIMARY KEY, code VARCHAR(16) NOT NULL UNIQUE ) ENGINE=InnoDB; CREATE TABLE entity_kinds ( id TINYINT UNSIGNED PRIMARY KEY, code VARCHAR(16) NOT NULL UNIQUE ) ENGINE=InnoDB; CREATE TABLE source_channels ( id TINYINT UNSIGNED PRIMARY KEY, code VARCHAR(16) NOT NULL UNIQUE ) ENGINE=InnoDB; CREATE TABLE attachment_kinds ( id TINYINT UNSIGNED PRIMARY KEY, code VARCHAR(16) NOT NULL UNIQUE ) ENGINE=InnoDB; CREATE TABLE memory_items ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, 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, 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), UNIQUE KEY uq_owner_hash (owner_user_id, content_hash) ) 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 TABLE memory_contents ( memory_item_id BIGINT UNSIGNED NOT NULL PRIMARY KEY, text LONGTEXT NOT NULL, format_id TINYINT UNSIGNED NOT NULL, source_language VARCHAR(16) NULL, -- агрегированное поле для FULLTEXT: title + '\n' + text search_text LONGTEXT NOT 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; ALTER TABLE memory_contents ADD FULLTEXT KEY ftx_memory_search (search_text); CREATE TABLE memory_sources ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, memory_item_id BIGINT UNSIGNED 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); CREATE TABLE memory_attachments ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, memory_item_id BIGINT UNSIGNED 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); 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 BIGINT UNSIGNED 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); 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 BIGINT UNSIGNED 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); 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 BIGINT UNSIGNED 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); CREATE TABLE memory_aliases ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, memory_item_id BIGINT UNSIGNED 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); ПРИЛОЖЕНИЕ B. Примеры запросов поиска -- 1) FULLTEXT + фильтры SELECT mi.id, mi.title, mt.code AS type, ms.code AS status, mi.created_at, MATCH(mc.search_text) AGAINST (? IN NATURAL LANGUAGE MODE) AS score FROM memory_items mi JOIN memory_contents mc ON mc.memory_item_id = mi.id JOIN memory_types mt ON mt.id = mi.type_id JOIN memory_statuses ms ON ms.id = mi.status_id WHERE mi.owner_user_id = ? AND mi.status_id (SELECT id FROM memory_statuses WHERE code='deleted') AND MATCH(mc.search_text) AGAINST (? IN NATURAL LANGUAGE MODE) ORDER BY score DESC, mi.created_at DESC LIMIT ? OFFSET ?; -- 2) Фильтр по тегу SELECT mi.id, mi.title FROM memory_items mi JOIN memory_item_tags mit ON mit.memory_item_id = mi.id JOIN tags t ON t.id = mit.tag_id WHERE mi.owner_user_id = ? AND t.name = ? ORDER BY mi.created_at DESC LIMIT ? OFFSET ?; -- 3) FULLTEXT + тег (комбинация) SELECT mi.id, mi.title, MATCH(mc.search_text) AGAINST (? IN NATURAL LANGUAGE MODE) AS score FROM memory_items mi JOIN memory_contents mc ON mc.memory_item_id = mi.id JOIN memory_item_tags mit ON mit.memory_item_id = mi.id JOIN tags t ON t.id = mit.tag_id WHERE mi.owner_user_id = ? AND t.name = ? AND MATCH(mc.search_text) AGAINST (? IN NATURAL LANGUAGE MODE) ORDER BY score DESC; ПРИЛОЖЕНИЕ C. Правило заполнения search_text При insert/update контента приложение обязано записывать: search_text = CONCAT(COALESCE(memory_items.title,''), '\n', memory_contents.text) (Можно делать на уровне приложения или триггером; в рамках ТЗ ответственность на приложении, чтобы не усложнять миграции.) Если хочешь, я добавлю в ТЗ ещё один кусок: минимальные миграции для наполнения справочников (типы/статусы/форматы/видимость/чувствительность/каналы/виды сущностей/вложений) — скажи, ты хочешь фиксированные id (1..N) или тебе всё равно, лишь бы code был уникальным?