# ssot-db-exporter

> Export all tables from the SSOT database (ssot.db) to pipe-delimited CSV files sorted by primary key. Use this skill when backing up database contents for version tracking, comparing database state across different points in time, or generating human-readable snapshots of all table data for documentation or diff analysis.

- 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-exporter:20260126232234

---

---
name: ssot-db-exporter
description: Export all tables from the SSOT database (ssot.db) to pipe-delimited CSV files sorted by primary key. Use this skill when backing up database contents for version tracking, comparing database state across different points in time, or generating human-readable snapshots of all table data for documentation or diff analysis.
---

# SSOT Database Exporter

## Overview

Export all tables from the SSOT (Single Source of Truth) database to pipe-delimited CSV files, with each table sorted by its primary key in ascending order. The export creates a timestamped directory containing one CSV file per table, enabling easy version comparison and diff analysis between database states.

## When to Use This Skill

Use this skill when:
- Backing up SSOT database contents in human-readable format
- Comparing database state before and after changes
- Generating diff reports between two database versions
- Creating snapshots for version control or documentation
- Auditing data changes across database migrations
- Exporting data for analysis or review

## Quick Start

To export the entire SSOT database:

```bash
python3 scripts/export_ssot_tables.py
```

This command will:
1. Automatically locate `SSOT_db/ssot.db` from the project root
2. Create a timestamped directory: `output/YYYYMMDDHHMMSS/`
3. Export all tables as pipe-delimited CSV files: `table_name.csv`
4. Sort each table by its primary key(s) in ascending order
5. Display progress with table names and row counts

## Usage Patterns

### Basic Export

```bash
# Export with default settings (auto-detects database location)
python3 scripts/export_ssot_tables.py
```

Output structure:
```
output/
└── 20250122143055/
    ├── api_master.csv
    ├── api_fields.csv
    ├── table_master.csv
    ├── screen_master.csv
    └── ... (all 59 tables)
```

### Custom Database Path

```bash
# Specify a custom database file location
python3 scripts/export_ssot_tables.py --db-path /path/to/custom/ssot.db
```

### Custom Output Directory

```bash
# Change the base output directory
python3 scripts/export_ssot_tables.py --output-dir /path/to/backups
```

### Verbose Mode

```bash
# Show detailed progress including file paths
python3 scripts/export_ssot_tables.py --verbose
```

### Combined Options

```bash
# Full customization
python3 scripts/export_ssot_tables.py \
  --db-path /custom/path/ssot.db \
  --output-dir /backups \
  --verbose
```

## Comparing Database Versions

To compare two database states:

1. **Export initial state:**
   ```bash
   python3 scripts/export_ssot_tables.py
   # Creates: output/20250122100000/
   ```

2. **Make database changes** (via init_db.py or manual edits)

3. **Export new state:**
   ```bash
   python3 scripts/export_ssot_tables.py
   # Creates: output/20250122150000/
   ```

4. **Compare using diff:**
   ```bash
   # Compare all tables
   diff -r output/20250122100000 output/20250122150000

   # Compare specific table
   diff output/20250122100000/api_master.csv output/20250122150000/api_master.csv

   # Visual diff with color (if available)
   diff -u output/20250122100000/api_master.csv output/20250122150000/api_master.csv | colordiff

   # Use git diff for better formatting
   git diff --no-index output/20250122100000/ output/20250122150000/
   ```

## Output Format

### File Format Specifications

- **Delimiter**: Pipe character (`|`)
- **Header**: First row contains column names
- **Sorting**: Rows sorted by primary key(s) in ascending order
- **NULL values**: Represented as empty strings
- **Encoding**: UTF-8

### Example Output (api_master.csv)

```
full_api_cd|api_cd|api_name|endpoint|http_method|status|created_at|updated_at
API_USER_REG|USER_REG|User Registration|/api/users|POST|Active|2025-01-22 10:00:00|2025-01-22 10:00:00
API_USER_LOGIN|USER_LOGIN|User Login|/api/auth/login|POST|Active|2025-01-22 10:05:00|2025-01-22 10:05:00
```

## Workflow Integration

### With db-initializer Skill

```bash
# 1. Export current state
python3 .claude/skills/ssot-db-exporter/scripts/export_ssot_tables.py

# 2. Reinitialize database
python3 .claude/skills/db-initializer/scripts/init_db.py

# 3. Export new state
python3 .claude/skills/ssot-db-exporter/scripts/export_ssot_tables.py

# 4. Compare changes
diff -r output/20250122100000 output/20250122150000
```

### Version Control Integration

To track database content changes in git:

```bash
# Export to a consistent directory name
python3 scripts/export_ssot_tables.py --output-dir snapshots/current

# Review changes
git diff snapshots/current/

# Commit if desired
git add snapshots/current/
git commit -m "Database snapshot after schema update"
```

## Troubleshooting

### Database Not Found

If the script cannot locate `ssot.db`:
- Ensure running from project root or SSOT_db exists in parent directories
- Use `--db-path` to specify exact database location
- Verify database file exists: `ls -la SSOT_db/ssot.db`

### Permission Errors

If output directory creation fails:
- Check write permissions on the output directory
- Try using `--output-dir` to specify a different location
- Ensure sufficient disk space

### Empty Tables

Tables with no data will show:
```
⚪ [12/59] empty_table_name                     (    0 rows)
```

This is normal for freshly initialized databases.

## Script Details

### Location
`scripts/export_ssot_tables.py`

### Features
- Automatic project root detection
- Primary key identification and sorting
- Timestamped output directories
- Progress reporting with row counts
- Error handling per table
- UTF-8 encoding support
- NULL value handling

### Dependencies
- Python 3.6+
- sqlite3 (standard library)
- csv (standard library)
- pathlib (standard library)

No external packages required.