# NEG DMS — Junior Dev Handoff
**Date:** 2026-05-27  
**Contact:** huy@neg.ag

---

## 1. What this project is (30 seconds)

NEG AG is a German real estate holding company with 43 companies. They accumulate documents via email (contracts, invoices, floor plans, photos, construction photos). We are building a system that:

1. Extracts text from documents (Mistral OCR / Doc AI)
2. Classifies them with AI (Gemini Vertex)
3. Files them to the correct OneDrive folder automatically
4. Makes them searchable via chat/WhatsApp (RAG pipeline)

The new database schema is `dms.*` in PostgreSQL (`neg_rag` database). Old schemas `v5.*` and `v6.*` are frozen — do not touch them.

---

## 2. Your access (n8n only — no server)

**You have no direct server or SSH access.** All SQL runs through the n8n Postgres node.

| Tool | URL | Notes |
|---|---|---|
| n8n | `https://n8n.negag.cloud` | All SQL execution happens here |
| DB credential | `NEG RAG` (ID: `j9xrEmElU55qnKWk`) | Use in every Postgres node |
| Schema | `dms` | All new tables live here |
| Database | `neg_rag` | Do not touch `v5`, `v6`, or `public.companies` |

**All files you need are in:**
```
/Users/b1live/Workspace/neg-dms/
├── docs/
│   ├── database.md              ← full schema reference (read this first)
│   ├── db_schema/dms-schema.sql ← exact column names
│   └── workflows/               ← pipeline docs
├── migration/                   ← all data files, job results
└── scripts/                     ← Python scripts (run locally on Mac)
```

---

## 3. Current DB state

Run this via n8n PG node to check:

```sql
SELECT COUNT(*) AS total_docs     FROM dms.documents;
SELECT COUNT(*) AS table_rows     FROM dms.document_table_rows;
SELECT COUNT(*) AS folder_maps    FROM dms.folder_mappings;
SELECT COUNT(*) AS reviewed_docs  FROM dms.documents WHERE company_reviewed = TRUE;
```

Expected today (2026-05-27):
- `dms.documents` — migrated rows from B69 + partial other companies
- `dms.folder_mappings` — may be empty; pipeline can't route files until this is populated
- `company_reviewed` — B69 done, all others pending

---

## 4. Company review checklist

Work through companies in order. B69 is done — start at ACI.

| # | Company | Code | Docs | Status |
|---|---|---|---|---|
| 1 | ARTCAS Projekt B69 GmbH | B69 | 958 | ✅ Reviewed |
| 2 | ARTCAS Invest GmbH | ACI | 505 | ⬜ Pending |
| 3 | NEG AG | NEG_AG | 480 | ⬜ Pending |
| 4 | Alter Flughafen Leipzig | AFL | 472 | ⬜ Pending |
| 5 | ARTCAS Projekt Z1 | Z1 | 406 | ⬜ Pending |
| 6 | ARTCAS Projekt R100 | R100 | 342 | ⬜ Pending |
| 7 | ARTCAS Projekt HS | HS | 334 | ⬜ Pending |
| 8 | Messeblick | MBL | 301 | ⬜ Pending |
| 9 | BSG GmbH | BSG | 273 | ⬜ Pending |
| 10 | ARTCAS Projekt HR | HR | 263 | ⬜ Pending |
| 11–43 | Remaining 33 companies | — | ~3,111 | ⬜ Pending |

---

## 5. Per-company workflow (5 steps)

All SQL runs via n8n PG node. Replace `[CODE]` with the company code (e.g. `ACI`).

### Step 1 — Check what's there

```sql
SELECT document_type, COUNT(*), AVG(confidence)::numeric(4,2) AS avg_conf
FROM dms.documents
WHERE company_code = '[CODE]'
GROUP BY 1
ORDER BY 2 DESC;
```

Look for:
- Low `avg_conf` on a type (< 0.5) → those rows may be misclassified, spot-check them
- Unexpected types (e.g. "Sonstiges" in bulk) → classifier struggled

### Step 2 — Spot-check 5 docs

```sql
SELECT internal_number, document_type, filed_filename, confidence, document_status
FROM dms.documents
WHERE company_code = '[CODE]'
ORDER BY confidence DESC
LIMIT 5;
```

Also check the bottom:
```sql
SELECT internal_number, document_type, filed_filename, confidence, document_status
FROM dms.documents
WHERE company_code = '[CODE]'
ORDER BY confidence ASC
LIMIT 5;
```

If you find obvious misclassifications, fix them (see section 7 — SQL fixes).

### Step 3 — Write enrichment results (see section 7)

If Job A/B/C results exist for this company, write them back before marking reviewed.

### Step 4 — Mark reviewed

```sql
UPDATE dms.documents
SET company_reviewed = TRUE
WHERE company_code = '[CODE]';
```

### Step 5 — Confirm

```sql
SELECT
    company_code,
    COUNT(*)                                              AS docs,
    COUNT(*) FILTER (WHERE company_reviewed)              AS reviewed,
    COUNT(*) FILTER (WHERE needs_review)                  AS needs_human_review,
    ROUND(AVG(confidence)::numeric, 3)                    AS avg_confidence
FROM dms.documents
WHERE company_code = '[CODE]'
GROUP BY 1;
```

All `docs` should equal `reviewed` after Step 4.

---

## 6. How to run SQL (n8n PG node only)

1. Go to `https://n8n.negag.cloud`
2. Create a new workflow (or open an existing scratch workflow)
3. Add a **Postgres** node
4. Set credential: **NEG RAG** (ID: `j9xrEmElU55qnKWk`)
5. Paste your SQL in the query field
6. Click **Execute node**

For multi-statement SQL (UPDATE + SELECT), split them into separate Postgres nodes connected in sequence — n8n's PG node runs one statement per node by default.

**Useful monitoring queries to run anytime:**
```sql
-- Overall company review progress
SELECT
    company_code, company_name,
    COUNT(*) AS total,
    COUNT(*) FILTER (WHERE company_reviewed) AS reviewed
FROM dms.documents
GROUP BY company_code, company_name
ORDER BY total DESC;

-- Pipeline health
SELECT * FROM dms.v_company_doc_counts ORDER BY total_docs DESC;
SELECT * FROM dms.v_ingest_monitor WHERE day >= CURRENT_DATE - 7;
```

---

## 7. Job A/B/C results — how to write them back

### Job A — table extraction

Result format (from `migration/results-job-a-full.jsonl`):
```json
{
  "_internal_number": "DOC-2024-1234",
  "has_tables": true,
  "tables": [
    {
      "title": "Tilgungsplan",
      "table_type": "payment_schedule",
      "headers": ["Datum", "Betrag"],
      "rows": [["01.01.2024", "1.500,00"]]
    }
  ]
}
```

SQL to write back (per doc):
```sql
UPDATE dms.documents
SET table_blocks            = '<json_from_tables_field>'::jsonb,
    needs_table_rows_populate = TRUE
WHERE internal_number = 'DOC-2024-1234';
```

After writing all rows for a company batch:
```sql
SELECT dms.batch_populate_table_rows();
-- Returns count of docs processed. Idempotent — safe to run multiple times.
```

### Job B — floor plans

Same structure as Job A. Rooms/dimensions become rows in `table_blocks`. Write back the same way.

### Job C — photo analysis

Result format (from `migration/results-job-c-full.jsonl`):
```json
{
  "_internal_number": "DOC-2024-5678",
  "category": "Bauphase",
  "suggested_name": "2023-07-15 - B69 - Rohbau-EG-Nordseite",
  "description": "Rohbauzustand Erdgeschoss, Blick von Nordseite...",
  "construction_stage": "Rohbau",
  "floor_level": "EG",
  "visible_date": "2023-07-15",
  "tags": ["Rohbau", "Decke", "Bewehrung"]
}
```

Write back to the `photo_meta` column (added 2026-05-27):
```sql
UPDATE dms.documents
SET photo_meta = '{
  "category": "Bauphase",
  "suggested_name": "2023-07-15 - B69 - Rohbau-EG-Nordseite",
  "description": "Rohbauzustand Erdgeschoss...",
  "construction_stage": "Rohbau",
  "floor_level": "EG",
  "visible_date": "2023-07-15",
  "tags": ["Rohbau", "Decke", "Bewehrung"]
}'::jsonb
WHERE internal_number = 'DOC-2024-5678';
```

### Manual classification fix (no re-extraction)

When you spot a misclassified doc during review:
```sql
UPDATE dms.documents SET
    document_type       = 'Contract',
    document_type_label = 'Vertrag',
    company_code        = 'ACI',
    area                = '1_FIRMA',
    subfolder           = 'Vertraege_Bescheide'
WHERE internal_number = 'DOC-2026-XXXX';
```

---

## 8. Column gotchas (will break your SQL if wrong)

| Table | Wrong | Correct |
|---|---|---|
| `dms.documents` | `content` | `extracted_text` |
| `dms.documents` | `type` | `document_type` |
| `dms.documents` | `doc_date` | `document_date` |
| `dms.documents` | `status` | `document_status` |
| `dms.documents` | `filename` | `filed_filename` |
| `dms.documents` | `sub_folder` | `subfolder` |
| `dms.document_types` | `label_german` | `label_de` |
| `dms.document_types` | `active` | `is_active` |
| `dms.folder_mappings` | `folder_id` | `onedrive_folder_id` |

Full reference: `docs/database.md` → "Key column names" section.

---

## 9. Hard rules

| Rule | Why |
|---|---|
| **No SSH or server commands** | You have no server access — use n8n PG node only |
| **Never write to `v5.*` or `v6.*`** | Live production RAG reads these |
| **Never modify `NEG_v6` or `NEG_v4` in Qdrant** | RAG search depends on them |
| **`dms.*` is the only target** | All new code writes here only |
| **`public.companies` is read-only** | 43 companies, do not add/change/delete |
| **Check column names before running SQL** | Wrong names silently update 0 rows |
| **Don't mark `company_reviewed = TRUE` until you've actually spot-checked** | This flag is used to track real human sign-off |

---

## When stuck

1. `docs/database.md` — full schema reference with example queries
2. `docs/db_schema/dms-schema.sql` — exact DDL including all column definitions
3. `CLAUDE.md` — full project context (pipeline architecture, known issues, credentials)
4. Ask huy: `huy@neg.ag`