隨記

SpecM Database & API Specification

Overview

SpecM uses SQLite as its local database, stored in the OS application data directory. The Rust backend (Tauri) exposes 7 IPC commands that the Angular frontend consumes via @tauri-apps/api/core.

Database Location

OS Path
Windows %LOCALAPPDATA%\com.specm.app\specm.db
macOS ~/Library/Application Support/com.specm.app/specm.db
Linux ~/.local/share/com.specm.app/specm.db

Schema

categories

Column Type Constraints Description
id INTEGER PRIMARY KEY AUTOINCREMENT Internal ID
name TEXT NOT NULL Display name (e.g. "Architecture")
slug TEXT NOT NULL UNIQUE URL-safe key (e.g. "architecture")
description TEXT Human-readable description

Seed data: 8 categories (architecture, bug-detection, code-quality, code-style, documentation, migration, security, testing)

tools

Column Type Constraints Description
id INTEGER PRIMARY KEY AUTOINCREMENT Internal ID
name TEXT NOT NULL Display name (e.g. "ArchUnit")
slug TEXT NOT NULL UNIQUE URL-safe key (e.g. "archunit")
description TEXT Human-readable description
config_path TEXT Path to tool config file

Seed data: 5 tools (archunit, checkstyle, spotbugs, sonarqube, openrewrite)

specs

Column Type Constraints Description
id TEXT PRIMARY KEY Spec ID (e.g. "ARCH-001")
version TEXT NOT NULL DEFAULT '1.0' Semver version
title TEXT NOT NULL Human-readable title
category TEXT NOT NULL, FK → categories(slug) Category slug
severity TEXT NOT NULL DEFAULT 'major' One of: critical, major, minor, info
applies_to TEXT Target scope: all, controller, service, repository
config_ref TEXT Path + line pointer to enforcement config
body TEXT NOT NULL Markdown content
created_at TEXT DEFAULT datetime('now') ISO-8601 creation timestamp
updated_at TEXT DEFAULT datetime('now') ISO-8601 last update timestamp

Seed data: 29 specs across 8 categories

spec_tools

Many-to-many relationship between specs and tools.

Column Type Constraints Description
spec_id TEXT NOT NULL, FK → specs(id) ON DELETE CASCADE Spec ID
tool_slug TEXT NOT NULL, FK → tools(slug) Tool slug

Primary key: (spec_id, tool_slug)

Seed data: 37 relationships

spec_tags

Many-to-many relationship between specs and tags.

Column Type Constraints Description
spec_id TEXT NOT NULL, FK → specs(id) ON DELETE CASCADE Spec ID
tag TEXT NOT NULL Tag string

Primary key: (spec_id, tag)

Seed data: 83 tags

spec_dependencies

Many-to-many relationship between specs (dependency tracking).

Column Type Constraints Description
spec_id TEXT NOT NULL, FK → specs(id) ON DELETE CASCADE Spec ID
depends_on TEXT NOT NULL Target spec ID

Primary key: (spec_id, depends_on)

specs_fts

FTS5 virtual table for full-text search.

Column Source
id specs.id
title specs.title
body specs.body
category specs.category
tags (aggregated from spec_tags)

Usage: SELECT * FROM specs_fts WHERE specs_fts MATCH 'keyword'

Entity Relationship Diagram

┌──────────────┐       ┌──────────────────┐       ┌──────────────┐
│  categories  │       │      specs       │       │    tools     │
├──────────────┤       ├──────────────────┤       ├──────────────┤
│ id (PK)      │◄──┐   │ id (PK)          │   ┌──►│ id (PK)      │
│ name         │   │   │ version          │   │   │ name         │
│ slug (UQ)    │───┼───│ category (FK)    │   │   │ slug (UQ)    │
│ description  │   │   │ title            │   │   │ description  │
└──────────────┘   │   │ severity         │   │   │ config_path  │
                   │   │ applies_to       │   │   └──────────────┘
                   │   │ config_ref       │   │
                   │   │ body             │   │   ┌──────────────┐
                   │   │ created_at       │   │   │ spec_tools   │
                   │   │ updated_at       │   │   ├──────────────┤
                   │   └────────┬────┬────┘   │   │ spec_id (FK) │
                   │            │    │        │   │ tool_slug(FK)│
                   │            │    │        │   └──────────────┘
                   │            │    │        │
                   │   ┌────────┘    │        │
                   │   │             │        │
                   │   │  ┌──────────┘   ┌────┘
                   │   │  │              │
                   │   ▼  ▼              ▼
                   │  ┌──────────────┐  ┌──────────────────┐
                   │  │ spec_tags    │  │ spec_dependencies │
                   │  ├──────────────┤  ├──────────────────┤
                   │  │ spec_id (FK) │  │ spec_id (FK)      │
                   │  │ tag          │  │ depends_on        │
                   │  └──────────────┘  └──────────────────┘
                   │
                   └── categories(slug) referenced by specs(category)

Tauri IPC Commands

All commands are invoked via invoke() from @tauri-apps/api/core.

list_specs

list_specs(category?: string): Result<Spec[], string>

Returns all specs, optionally filtered by category slug. Results ordered by spec ID.

Parameters: - category (optional): Filter by category slug. null or omitted returns all.

Response: Array of Spec objects with tools, tags, and depends_on populated.

get_spec

get_spec(spec_id: string): Result<Spec | null, string>

Returns a single spec by ID, or null if not found.

Response: Spec object with all relationships populated.

create_spec

create_spec(spec: CreateSpec): Result<Spec, string>

Creates a new spec with its tools, tags, and dependencies.

Request body: 

{
  id: string;           // e.g. "ARCH-006"
  version: string;      // e.g. "1.0"
  title: string;
  category: string;     // category slug
  severity: string;     // "critical" | "major" | "minor" | "info"
  applies_to: string | null;
  config_ref: string | null;
  body: string;         // Markdown content
  tools: string[];      // tool slugs
  tags: string[];       // tag strings
  depends_on: string[]; // spec IDs
}

Response: The newly created Spec object.

delete_spec

delete_spec(spec_id: string): Result<boolean, string>

Deletes a spec and its cascading relationships (spec_tools, spec_tags, spec_dependencies).

Response: true if deleted, false if not found.

list_categories

list_categories(): Result<Category[], string>

Returns all categories ordered by name.

Response: Array of Category objects.

list_tools

list_tools(): Result<Tool[], string>

Returns all tools ordered by name.

Response: Array of Tool objects.

validate_specs

validate_specs(): Result<ValidationResult, string>

Validates all specs against: - Severity must be one of: critical, major, minor, info - Tool slugs must be one of: archunit, checkstyle, spotbugs, sonarqube, openrewrite

Response: 

{
  passed: string[];     // spec IDs that passed
  failed: Array<{
    spec_id: string;
    errors: string[];   // e.g. ["invalid severity: warning", "unknown tool: pmd"]
  }>;
}

TypeScript Types

interface Spec {
  id: string;
  version: string;
  title: string;
  category: string;
  tools: string[];
  severity: string;
  applies_to: string | null;
  tags: string[];
  depends_on: string[];
  config_ref: string | null;
  body: string;
  created_at: string;
  updated_at: string;
}

interface CreateSpec {
  id: string;
  version: string;
  title: string;
  category: string;
  tools: string[];
  severity: string;
  applies_to: string | null;
  tags: string[];
  depends_on: string[];
  config_ref: string | null;
  body: string;
}

interface Category {
  id: number;
  name: string;
  slug: string;
  description: string | null;
}

interface Tool {
  id: number;
  name: string;
  slug: string;
  description: string | null;
  config_path: string | null;
}

interface ValidationError {
  spec_id: string;
  errors: string[];
}

interface ValidationResult {
  passed: string[];
  failed: ValidationError[];
}

Current Data Summary

Table Count
categories 8
tools 5
specs 29
spec_tools 37
spec_tags 83