← back to Wallco Ai

data/yolo-overnight/quarantine-20260525-1239.md

108 lines

# Quarantine Cross-Reference — generated_ghost_quarantine vs spoon_all_designs

Generated: 2026-05-25 05:39 PT
Source dir: `/root/public-projects/wallco-ai/data/generated_ghost_quarantine/`
Source table: `spoon_all_designs` (dw_unified)

## Headline Numbers

| Bucket | Count |
|---|---|
| **Files in quarantine** | **6,546** |
| Files matched to a PG row (by basename) | 37 |
|    ↳ matched + `is_published=FALSE` | **37** |
|    ↳ matched + `is_published=TRUE` | 0 |
| **Orphan files (no PG row at all)** | **6,509** |

So: **37 quarantine files are still attached to an unpublished PG row, and 6,509 are pure orphans** with no match anywhere in `spoon_all_designs`.

## Method

```sql
-- Loaded basenames of all 6,546 files into helper table _quarantine_files,
-- joined against regexp_replace(spoon_all_designs.local_path, '^.*/', '')
-- because local_path stores the FULL path (often the original Mac path),
-- while files in quarantine are bare filenames.
```

## Matched-Row Details (37 rows)

| Sub-bucket | Count |
|---|---|
| `is_published=FALSE`, `user_removed=FALSE` | 32 |
| `is_published=FALSE`, `user_removed=TRUE` | 5 |
| `is_published=TRUE` | 0 |

All 37 matched rows' `local_path` still points to the **original location** (`/Users/stevestudio2/Projects/wallco-ai/data/generated/...` — Mac dev paths). **None of the rows were updated when the files were moved to quarantine** — `local_path` is stale on every matched row. If anything reads `local_path` to load the file, it will 404.

Sample matched rows (all unpublished):

```
id=21  is_published=f user_removed=f /Users/stevestudio2/.../data/generated/1778541186410_1284647138.png  2026-05-11
id=25  is_published=f user_removed=f /Users/stevestudio2/.../data/generated/1778543171203_1496299154.png  2026-05-11
id=34  is_published=f user_removed=f /Users/stevestudio2/.../data/generated/1778543260334_151035740.png   2026-05-11
id=42  is_published=f user_removed=f /Users/stevestudio2/.../data/generated/1778546385937_218100061.png   2026-05-12
id=47  is_published=f user_removed=f /Users/stevestudio2/.../data/generated/1778550261052_125177861.png   2026-05-12
```

## Orphan Files (6,509)

These files exist in `data/generated_ghost_quarantine/` but no row in `spoon_all_designs` references them (by basename). They follow the same `{ms_timestamp}_{random}.png` naming pattern as the matched ones — most likely generated outputs that were never recorded in PG, or rows that have since been deleted.

Sample orphans:

```
1778561136580_1738674239.png
1778561136610_779545247.png
1778562018319_344222204.png
1778562678302_1951555011.png
1778562678310_9047248.png
```

## Sanity check — the other side

Of the 341 rows in `spoon_all_designs` with `local_path` set:

| | Count |
|---|---|
| Matched to a quarantine file | 37 |
| Pointing somewhere else (not in quarantine) | 304 |
|   ↳ published | 212 |
|   ↳ unpublished | 92 |
|   ↳ path starts `/root/public-projects/wallco-ai/data/generated/` | 187 |
|   ↳ path starts `/Users/` (Mac dev) | 58 |

So **92 unpublished rows point to files still in `data/generated/`** — those are next in line if the goal is to widen the quarantine sweep to "all unpublished".

## Implications / next moves (not executed)

1. **Most of the quarantine is dead weight** — 6,509 / 6,546 (99.4%) have no PG row. If quarantine retention has expired for these, they can be hard-deleted with no DB impact.
2. **37 matched rows have stale `local_path`** — if anything in the app reads `local_path` to load the bytes, it will fail. Either re-point them to the quarantine location or null out `local_path` on quarantine.
3. **92 unpublished rows still in `data/generated/`** — if the policy is "unpublished → quarantine", these are missed.

No destructive action taken. Soft-delete + JSONL is the default per YOLO rules.

## Reproduce

```bash
ls /root/public-projects/wallco-ai/data/generated_ghost_quarantine/ > /tmp/quarantine_files.txt
sudo -u postgres psql dw_unified <<'SQL'
DROP TABLE IF EXISTS _quarantine_files;
CREATE TABLE _quarantine_files (filename text);
\copy _quarantine_files FROM '/tmp/quarantine_files.txt'

WITH sad AS (
  SELECT id, local_path, regexp_replace(local_path, '^.*/', '') AS filename,
         is_published, user_removed
  FROM spoon_all_designs WHERE local_path IS NOT NULL
)
SELECT
  (SELECT COUNT(*) FROM _quarantine_files)                                                 AS quarantine_files,
  (SELECT COUNT(*) FROM _quarantine_files q JOIN sad USING(filename))                       AS matched,
  (SELECT COUNT(*) FROM _quarantine_files q JOIN sad USING(filename)
     WHERE sad.is_published=FALSE)                                                          AS matched_unpublished,
  (SELECT COUNT(*) FROM _quarantine_files q LEFT JOIN sad USING(filename)
     WHERE sad.id IS NULL)                                                                  AS orphans;
SQL
```