# ssot-db-initializer

> Initialize SSOT SQLite database from init/配下 and sequential step files. This skill should be used
when the user requests database initialization, reinitialization, or reset. The skill
automates backup, table creation, sequential data loading (step01_01.sql, step01_10.sql, ...), and integrity validation.

- Author: Hiroshi Kataoka
- Repository: pri-Kataoka-Hiroshi/kfc_generator2
- Version: 20260126232234
- Stars: 0
- Forks: 0
- Last Updated: 2026-02-07
- Source: https://github.com/pri-Kataoka-Hiroshi/kfc_generator2
- Web: https://mule.run/skillshub/@@pri-Kataoka-Hiroshi/kfc_generator2~ssot-db-initializer:20260126232234

---

---
name: ssot-db-initializer
description: |
  Initialize SSOT SQLite database from init/配下 and sequential step files. This skill should be used
  when the user requests database initialization, reinitialization, or reset. The skill
  automates backup, table creation, sequential data loading (step01_01.sql, step01_10.sql, ...), and integrity validation.
---

# SSOT Database Initializer Skill

## Purpose

Initialize the SSOT (Single Source of Truth) SQLite database by:
1. Creating a fresh ssot.db file
2. Executing `SSOT_db/sql/init/配下` for table creation (70 tables)
3. Executing step files (`step01_01.sql`, `step01_10.sql`, ...) in numerical order for data loading

## When to Use This Skill

Use this skill when:
- The user requests database initialization or reinitialization
- The database needs to be reset to a clean state
- Schema changes require rebuilding the database
- Initial setup of the SSOT database is needed
- New step SQL files have been added and need to be applied

## How to Use This Skill

### Execute the Initialization Script

Run the Python script located at `scripts/init_db.py`:

```bash
python3 .claude/skills/ssot-db-initializer/scripts/init_db.py
```

This outputs a single line on success:
```
✓ SSOT DB initialized successfully. (tables: 70, size: 156 KB)
```

On failure, error details are shown:
```
✗ step03.sql 実行失敗: FOREIGN KEY constraint failed
✗ SSOT DB initialization failed.
```

### Script Options

The initialization script supports several command-line options:

- `-v, --verbose` - Show detailed progress logs (for debugging)
- `--dry-run` - Preview actions without executing
- `--no-backup` - Skip backup creation (use with caution)
- `--db-path <path>` - Specify custom database path (default: `SSOT_db/ssot.db`)
- `--sql-dir <path>` - Specify custom SQL directory (default: `SSOT_db/sql/`)

### Usage Examples

```bash
# Standard execution (minimal output)
python3 .claude/skills/ssot-db-initializer/scripts/init_db.py

# Detailed output for debugging
python3 .claude/skills/ssot-db-initializer/scripts/init_db.py --verbose

# Preview mode (no actual changes)
python3 .claude/skills/ssot-db-initializer/scripts/init_db.py --dry-run
```

## Processing Flow

The script executes the following steps in order:

1. Validate SQL files existence (`init/`配下 and any `step*.sql` files)
2. Create backup directory (`SSOT_db/backup/`) if it doesn't exist
3. Create backup of existing database to `SSOT_db/backup/ssot.backup.YYYYMMDD_HHMMSS.db`
4. Remove existing database file
5. Create new database with `PRAGMA foreign_keys = ON`
6. Execute `init/`配下 (70 tables DDL + system enumeration masters)
7. Execute step files in numerical order: `step01_01.sql`, `step01_10.sql`, `step03.sql`, ...
8. Run integrity checks (foreign key constraints, database integrity)
9. Display completion status

## SQL Files

### init/配下 (Required)

**Location**: `SSOT_db/sql/init/配下`

Contains CREATE TABLE statements for all 70 tables and INSERT statements for system enumeration masters:
- System data types (STRING, INT, BOOLEAN, etc.)
- IO types (IN, OUT, INOUT)
- Step types (VALIDATION, LOGIC, BRANCH, etc.)
- Logic types, fetch methods, condition types
- Permission types and roles
- Requirement types, function types, etc.

### Step Files (Optional)

**Location**: `SSOT_db/sql/step*.sql`

Step files are executed in numerical order (2-digit zero-padded):
- `step01_01.sql` - Project & Stakeholder (project_master, stakeholder_master)
- `step01_10.sql` - Business Goals (business_goal)
- `step03.sql` - Requirements (requirement_definition)
- `step03_07.sql` - Non-functional Requirements (project_non_func_definition)
- `step06.sql` - Function definitions & Skeletons (function_master, mapping_requirement_function, screen_master, api_master, table_master, mst_complex_type)
- And so on...

Only existing step files will be executed. Missing step numbers are skipped.

## Error Handling

The script provides clear error messages for common issues:

- **init/配下 not found**: Fatal error, cannot proceed
- **Step file execution error**: Shows the failing query and file name
- **Foreign key constraint failure**: Suggests checking insert order in step files
- **Backup failure**: Halts execution to prevent data loss

When errors occur:
1. The current step file is identified in the error message
2. Previous step files may have been applied successfully
3. Fix the failing step file and re-run the initialization

## Important Notes

- **Data loss warning**: All existing database contents will be deleted. The script creates a timestamped backup automatically.
- **Foreign key constraints**: Data in step files must respect dependency order (parent tables before child tables).
- **Transaction safety**: Each SQL file executes within a transaction, ensuring atomicity per file.
- **Step file order**: Files are sorted by the numeric portion (step01_01, step01_10, step01_11, step03_01, etc.).
- **Path assumptions**: The script expects to run from the project root directory.

## Troubleshooting

If the script fails:

1. Check that `SSOT_db/sql/init/配下` exists
2. Verify SQL syntax in the failing step file
3. Review insert order in step files (respect foreign key dependencies)
4. Use `--dry-run --verbose` to see which files would be executed
5. Check for invisible characters (non-breaking spaces, etc.) in SQL files

## Task Completion

After running the initialization script:
1. Report the outcome (success or failure) to the user
2. If errors occurred, explain the issue and suggest remediation steps