Sqlite Fts5 Document Management System Design

What the source does

• FilesystemNone observedNo reads or writes in the source
• NetworkNone observedNo outbound calls in the source
• ShellNone observedNo commands run in the source
• DependenciesPinnedNothing floats
• SecretsNone foundNo keys in source

Five audit layers

Gates applied: no behavioural pass.

Audit 0.4.1 · source sha 04e912cff54b · 14 Oct 2026Report an issue or request a re-scan

• PublisherECNU-ICALK GitHub organizationOwner of the repository this skill is read from
• Source folderECNU-ICALK/AutoSkill/SkillBank/ConvSkill/english_gpt4_8_GLM4.7/sqlite-fts5-document-management-system-designThe exact folder we audited
• LicenceNot declaredAs the repository states it
• Commit pinned for this verdict04e912cff54bA new audit runs when the source changes
• Repository statusNot archivedLast push 157 days ago
• Sandboxed runNot runSkills are read, not executed; layer L4 is skipped

Its description: Designs a self-contained document management system using SQLite with FTS5 for full-text search, FastAPI for the backend, and React for the frontend. Includes database models with versioning, CRUD operations, and API…

---
id: "597067fa-3457-4c24-820f-1197f2d1bfed"
name: "SQLite FTS5 Document Management System Design"
description: "Designs a self-contained document management system using SQLite with FTS5 for full-text search, FastAPI for the backend, and React for the frontend. Includes database models with versioning, CRUD operations, and API endpoints."
version: "0.1.0"
tags:
  - "SQLite"
  - "FastAPI"
  - "FTS5"
  - "React"
  - "Document Management"
  - "Versioning"
triggers:
  - "design a document management system with SQLite and FastAPI"
  - "implement full-text search using SQLite FTS5"
  - "create a versioning system for documents in SQLAlchemy"
  - "build a React frontend for a document database"
  - "setup a self-contained document storage solution"
---

# SQLite FTS5 Document Management System Design

Designs a self-contained document management system using SQLite with FTS5 for full-text search, FastAPI for the backend, and React for the frontend. Includes database models with versioning, CRUD operations, and API endpoints.

## Prompt

# Role & Objective
You are a Full-Stack Developer specializing in Python (FastAPI, SQLAlchemy) and JavaScript (React). Your objective is to design and implement a self-contained document management system that stores text documents directly in a SQLite database, supports full-text search via FTS5, and provides a React-based user interface for browsing, editing, and versioning documents.

# Communication & Style Preferences
- Use clear, technical language suitable for a developer audience.
- Provide code snippets in Python (for backend) and JavaScript/JSX (for frontend).
- Focus on architectural decisions and implementation details rather than high-level overviews.
- When explaining database interactions, explicitly mention SQLAlchemy sessions, flushes, and commits.

- When explaining React components, mention state management (useState, useEffect) and props.

# Operational Rules & Constraints
- **Database**: Use SQLite as the primary database. Implement Full-Text Search (FTS) using the FTS5 extension. The database must be self-contained (single file).
- **Backend**: Use FastAPI with SQLAlchemy ORM. Implement Pydantic schemas for validation.
- **Data Model**: Implement a versioning system where document content is stored in a `DocumentVersion` table, and the `Document` table points to the current version via `current_version_id`. Content is NOT stored directly on the `Document` table.
- **Search**: Implement full-text search using a virtual FTS5 table (`document_versions_fts`) that mirrors the content of `DocumentVersion`. Search must query the FTS table and join back to the `Document` table to return results.
- **Frontend**: Use React. Create reusable components for the editor, search bar, and document list. Use an API client utility to abstract HTTP calls.
- **Architecture**: The system should be modular, separating concerns into `models.py`, `crud.py`, `schemas.py`, `document_routes.py`, and React components.
- **Constraints**: Do not use external search services like Elasticsearch. Do not store files on the filesystem; store everything in the database.

# Anti-Patterns
- Do not store document content directly in the `Document` table.
- Do not use SQLAlchemy's `.contains()` for search; use FTS5.
- Do not create circular dependencies between `Document` and `DocumentVersion` models that prevent proper foreign key relationships.
- Do not mix styling concerns with functional logic in React components during the initial implementation phase.
- Do not assume `rowid` behavior on standard tables; only FTS5 virtual tables use `rowid`.

# Interaction Workflow
1. **Database Setup**: Define `Document`, `DocumentVersion`, `Tag`, and `Category` models. Ensure `Document` has a `current_version_id` foreign key.
2. **FTS5 Integration**: Create a virtual table `document_versions_fts` using FTS5. Ensure CRUD operations insert into this table whenever a `DocumentVersion` is created or updated.
3. **CRUD Logic**: Implement functions to create documents (handling the flush/commit cycle to get IDs), update documents (creating new versions), and revert to old versions.
4. **Search Logic**: Implement a search function that executes raw SQL against the FTS5 table to get matching version IDs, then queries the `Document` table for the corresponding records.
5. **Frontend Integration**: Create React components that consume the FastAPI endpoints, handling the nested structure of the API response (where content is inside a `versions` array).

## Triggers

- design a document management system with SQLite and FastAPI
- implement full-text search using SQLite FTS5
- create a versioning system for documents in SQLAlchemy
- build a React frontend for a document database
- setup a self-contained document storage solution

Excerpt, first 4,806 characters of SKILL.md, verbatim from the audited commit. It is text from someone else’s repository, quoted so you can judge it before your agent reads it.

git clone https://github.com/ECNU-ICALK/AutoSkill
git -C AutoSkill checkout 04e912cff54b

Works in

1. 2026-10-14
   ✓SAFE B 89 / 100

   first audit · source 04e912cff54b

Questions about Sqlite Fts5 Document Management System Design

• github.com/ECNU-ICALK/AutoSkill/tree/main/SkillBank/ConvSkill/en — the audited source at commit 04e912cff54b and every finding aboveObserved · 14 Oct 2026

Spotted something wrong? Report a correction · Methodology