fusion/docs/old-database-schema.md

Legacy Database Schema (Preserved)

This document preserves the legacy schema snapshot for migration development and validation. SQL DDL is the single source of truth for table/column definitions.

Full DDL (Authoritative)

CREATE TABLE `groups` (
  `id` integer PRIMARY KEY AUTOINCREMENT,
  `created_at` datetime,
  `updated_at` datetime,
  `deleted_at` integer,
  `name` text NOT NULL
);

CREATE TABLE sqlite_sequence(name,seq);

CREATE UNIQUE INDEX `idx_name` ON `groups`(`deleted_at`,`name`);

CREATE TABLE `feeds` (
  `id` integer PRIMARY KEY AUTOINCREMENT,
  `created_at` datetime,
  `updated_at` datetime,
  `deleted_at` integer,
  `name` text NOT NULL,
  `link` text NOT NULL,
  `last_build` datetime,
  `failure` text DEFAULT "",
  `group_id` integer,
  `suspended` numeric DEFAULT false,
  `req_proxy` text,
  `consecutive_failures` integer DEFAULT 0,
  CONSTRAINT `fk_feeds_group` FOREIGN KEY (`group_id`) REFERENCES `groups`(`id`)
);

CREATE TABLE `items` (
  `id` integer PRIMARY KEY AUTOINCREMENT,
  `created_at` datetime,
  `updated_at` datetime,
  `deleted_at` integer,
  `title` text NOT NULL,
  `guid` text,
  `link` text,
  `content` text,
  `pub_date` datetime,
  `unread` numeric DEFAULT true,
  `feed_id` integer,
  `bookmark` numeric DEFAULT false,
  CONSTRAINT `fk_items_feed` FOREIGN KEY (`feed_id`) REFERENCES `feeds`(`id`)
);

CREATE INDEX `idx_items_feed_id` ON `items`(`feed_id`);
CREATE INDEX `idx_items_title` ON `items`(`title`);
CREATE INDEX `idx_items_unread` ON `items`(`unread`);
CREATE INDEX `idx_items_bookmark` ON `items`(`bookmark`);

CREATE UNIQUE INDEX `idx_guid` ON `items`(`deleted_at`,`guid`,`feed_id`);
CREATE UNIQUE INDEX `idx_link` ON `feeds`(`deleted_at`,`link`);

Business Logic Notes

1) Entity relationships

• groups -> feeds -> items is a two-level one-to-many chain.
• feeds.group_id links each feed to a group.
• items.feed_id links each item to a feed.
• Legacy schema declares foreign keys (fk_feeds_group, fk_items_feed).

2) Soft-delete model

• All business tables use deleted_at (Unix timestamp) for logical delete.
• Deletes update deleted_at; rows are not physically removed.
• Unique indexes include deleted_at, so deleted historical rows can coexist with new active rows.

3) Deduplication and uniqueness semantics

• Group name uniqueness: idx_name(deleted_at, name).
• Feed link uniqueness: idx_link(deleted_at, link).
• Item deduplication key: idx_guid(deleted_at, guid, feed_id).
• SQLite caveat: NULL != NULL in unique indexes, so multiple rows with guid=NULL are allowed (must be preserved in migrations).

4) Feed pull status fields (feeds)

• last_build: last content update time.
• failure + consecutive_failures: latest error and failure streak.
• suspended: pull disabled flag.
• req_proxy: per-feed proxy setting.

5) Reading state fields (items)

• unread: unread flag (default true).
• bookmark: bookmark flag (default false).
• Both are indexed for high-frequency filtering.

6) Application-level semantics

• App bootstrap ensures default group exists: id=1, name="Default".
• unread_count is a virtual field in app models and is not persisted.

Legacy Migration History

After v0.8.7

• Added unique constraint for feed links via idx_link.
• Migration removed duplicate feeds and kept the row with the smallest id.