Skip to content

MIGRATION_POSTGRESQL.md

Latest commit

 

History

History
89 lines (73 loc) · 5.47 KB

MIGRATION_POSTGRESQL.md

File metadata and controls

89 lines (73 loc) · 5.47 KB
Raw

PostgreSQL Migration

Status: proposal, not started. Audience: IT engineering. Estimated total effort: 4–5 working days incl. validation and the auth piece (§4 step 4).

1. Current database status

  • Store: plain Excel file data/master_database.xlsx — no DB server, no connection string, no network.
  • Access pattern: the Flask webapp opens the file directly on the local filesystem via openpyxl. All reads/writes funnel through one module, IST_CD/db.py (load_contacts, save_contact, save_contacts, overwrite_all, get_stats, …) with a cross-process file lock and auto-backups to data/backups/.
  • Schema: one worksheet; columns = _DB_COLUMNS in IST_CD/db.py (name, company, title, email, phone, address, country, category, tags, assistant fields, notes, verification status, …) plus a hidden immutable _Record ID (UUID) column and a sequential customer_id.
  • Sidecar state (file-based, also to migrate):
    • data/metadata.json — per-contact diagnostic/enrichment metadata (keyed by record identity)
    • data/dedup_state.json — Step 2 runs, pair assessments, merge plans, human decisions
    • data/agent_feedback_log.jsonl — human review feedback events
  • Implication: single-host, single-user; concurrency and backups are hand-rolled. This is why a real DB server is the goal.

2. Target architecture (to-be)

Browser (LAN)
   │  HTTP :8000
   ▼
Flask webapp (IST_CD)  ── unchanged app code ──
   │
   ▼
IST_CD/db.py  ← single seam; implementation swapped only here
   │  SQLAlchemy + psycopg, DATABASE_URL env var
   ▼
PostgreSQL 15+ (LAN host, :5432, TLS if available)
   ├── contacts            (core table, see §3)
   ├── contact_metadata    (JSONB diagnostic/enrichment data)
   ├── dedup_runs / dedup_plans / dedup_decisions
   └── feedback_events

Excel remains: import/export format only (legacy import path already exists, unchanged).

Key point: the app never touches Excel directly today — it calls db.py functions. Migration reimplements those ~15 functions; no webapp/UI changes.

3. Schema (need to be confirmed by IST)

CREATE TABLE contacts (
    record_id      UUID PRIMARY KEY,          -- from hidden _Record ID column
    customer_id    INTEGER UNIQUE NOT NULL,   -- sequential, preserved
    name           TEXT NOT NULL,
    company        TEXT NOT NULL,
    title          TEXT,
    email          TEXT,
    phone          TEXT,
    address        TEXT,
    country        TEXT,
    category       TEXT,
    industry       TEXT,
    tags           TEXT,
    assistant_name TEXT,
    assistant_email TEXT,
    assistant_phone TEXT,
    notes          TEXT,
    verification_status TEXT DEFAULT 'unverified',
    extra          JSONB DEFAULT '{}',        -- corrected_fields, evidence, flags
    created_at     TIMESTAMPTZ DEFAULT now(),
    updated_at     TIMESTAMPTZ DEFAULT now()
);
-- Sidecar tables mirror the JSON files 1:1 (metadata, dedup state, feedback events).

4. Migration steps

  • Step 1 — Provision PostgreSQL 15+ (0.5 d, IT). LAN host; create database + least-privilege app user. Output: running instance.
  • Step 2 — Create schema (0.5 d). contacts + sidecar tables per §3. Output: DDL applied.
  • Step 3 — Backend swap (1–1.5 d, dev). Reimplement IST_CD/db.py against SQLAlchemy with identical function signatures; DATABASE_URL env var; delete hand-rolled file-lock/backup logic (Postgres handles concurrency and backups). Output: app running on Postgres with zero webapp/UI changes.
  • Step 4 — Minimal auth + reviewer identity (0.5–1 d, dev). Today there is no login and every review is recorded as "local-reviewer". Add: (a) simple login — a small user table (username + password hash) with Flask session, LAN-only; no SSO needed for pilot scale; (b) reviewer identity stamped on every approve/reject/merge decision (feeds feedback_events and the dedup decision audit trail); (c) two roles at most — operator (import, run agents, review) and viewer (read-only). Output: multi-user access with accountability.
  • Step 5 — One-time ETL (0.5 d). Read Excel via existing load_contacts() → insert; import metadata.json, dedup_state.json, feedback log. Output: migrated data.
  • Step 6 — Validation (0.5 d). Row counts, per-field diff on the 50-sample test set, full pytest suite, smoke test 6/6. Output: sign-off.
  • Step 7 — Cutover (0.5 d). Set DATABASE_URL, restart app; keep the Excel file read-only as a fallback snapshot. Output: go-live.

Decision needed before step 3: Step 2's review workflow currently writes color marks into the Excel workbook. Either (a) keep color-marking in an exported review workbook, or (b) store review state as Postgres columns and render colors in the UI. Recommend (b); adds ~0.5 d if chosen.

5. Rollback

Set DATABASE_URL back to the Excel backend (keep old db.py as db_excel.py behind a backend switch), restart. Data loss risk ≈ 0: ETL is copy-only, Excel file untouched until cutover is accepted.

6. Notes

  • Do this after pilot sign-off on the Excel store, not mid-pilot.
  • Multi-user readiness = PostgreSQL (concurrent writes) + the auth piece (step 4). Neither alone is sufficient for several staff working with accountability.
  • The existing 50-sample test set (IST_test_sample_50.xlsx) is the validation fixture.
  • Bulk-import and dedup logic is backend-agnostic (operates on Contact objects), so no Sprint 1–3 pipeline changes are expected beyond db.py.