Skip to content

old-database-schema.md

Latest commit

 

History

History
106 lines (81 loc) · 3.33 KB

old-database-schema.md

File metadata and controls

106 lines (81 loc) · 3.33 KB

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.