Overview

Ingest Brabys subscriber data from Informix file dumps into a PostgreSQL staging database as raw tables — exact mirrors of the Informix schema. AI agents (Claire, General & Scrub) access data through a read layer (PostgreSQL views) that handles phone reconstruction and code resolution at query time. Verified changes are written back to Carl in the same file-dump format for re-import into Informix production.

Architecture

Source Data Format

Informix 11 running on AIX 5.3. Carl provides file dumps as a ZIP archive containing 13 pipe-delimited text files — one file per table, one row per record.

Source Tables

Each dump contains the following 13 tables:

Table	Purpose
cosdienr	Core subscriber record — address codes, flags, primary key
cosbusnm	Business name(s) per subscriber
cosphone	Phone numbers (multiple per subscriber)
cosinfod	Email addresses and website URLs
cospobox	PO Box address
cosaltad	Free-text alternate address (non-standard)
cosstrdc	Street code lookup
cossubrb	Suburb code lookup
costown	Town/city lookup (includes dialing code)
cosblddc	Building code lookup
cosregion	Region lookup
coscountry	Country lookup
cosplot	Plot type lookup

Sample Data Profile

Sample dump covering the Pretoria / 012 dialing area:

File Format

de_alpha_key|de_town_code|de_suburb_code|de_street_code|de_street_no|...
1234567|012|4501|88210|42|...

• Pipe-delimited (|) separator
• One row per record, no header row in data files
• Column order matches Informix schema definitions in .sql files
• NULLs represented as empty strings between delimiters

Key Field Reference — cosdienr

The cosdienr table is the core subscriber record. Every other table joins back to it via de_alpha_key. Understanding these fields is essential for import, filtering, and write-back.

Core Fields

Field	Type	Description
de_alpha_key	INTEGER	Primary key, immutable — links all tables
de_town_code	FK	Foreign key → costown (tw_town_code)
de_suburb_code	FK	Foreign key → cossubrb (sb_suburb_code)
de_street_code	FK	Foreign key → cosstrdc (st_street_code)
de_build_code	FK	Foreign key → cosblddc (building name)
de_street_no	VARCHAR	Street number
de_build_no	VARCHAR	Building / unit number
de_floor	VARCHAR	Floor (for buildings)
de_alt_bld_no	VARCHAR	Non-standard building number suffix
de_corner_of	VARCHAR	Corner description (free text)
de_amend_date	DATE	Last amended date

Filter & Status Flags

Field	Type	Description
de_print_code	CHAR	Print filter flag — NULL / " " = printable
de_z_entry	CHAR	Exclude flag — NULL / " " = include
de_specific	CHAR	Specific-type entry flag
de_unlisted	CHAR	Unlisted flag
de_sch_do_not_call	CHAR	Do Not Call flag — MUST be checked before any outbound call

Phone Number Reconstruction

Phone numbers are stored as INTEGER in cosphone (ph_phone), which means leading zeros are stripped on write. Reconstruction logic is derived from address.4gl.

Phone Type Indicators

Two fields determine the phone type: ph_phone_ind and ph_fax_ind.

Phone Types (ph_phone_ind)

Code	Type	Reconstruction
"Y"	Phone (landline)	Prepend tw_dialing_code from costown
"C"	Cell / Mobile	South Africa: prepend "0"; otherwise as-is
"V"	VOIP	South Africa: prepend "0"; otherwise as-is
"S"	Sharecall	South Africa: prepend "0"; otherwise as-is
"T"	Toll-free	South Africa: prepend "0"; otherwise as-is

Fax Types (ph_fax_ind)

Code	Type	Reconstruction
"Y"	Fax	Prepend tw_dialing_code
"C"	CellFax	South Africa: prepend "0"
"V"	VOIPFax	South Africa: prepend "0"
"X"	ShareCallFax	South Africa: prepend "0"

Dialing Code Join Path

cosphone.ph_alpha_key = cosdienr.de_alpha_key
  → cosdienr.de_town_code = costown.tw_town_code
  → use costown.tw_dialing_code

Examples

Address Resolution

Street addresses are assembled from code lookups as defined in address.4gl (get_street_address function). Alternate free-text addresses take priority when present.

Resolution Order

1. Check cosaltad for alt_address — if present, use free-text address directly
2. Otherwise resolve codes from cosdienr:
   • de_build_code → cosblddc (building name)
   • de_build_no + de_alt_bld_no → building / unit number
   • de_floor → floor
   • de_street_no → street number
   • de_street_code → cosstrdc (street name)
   • de_suburb_code → cossubrb (suburb name)
   • de_town_code → costown (town name)

PostgreSQL Staging Schema

The staging database keeps raw tables only — exact mirrors of the Informix schema. No flattening or reconstruction is stored in the database. All code resolution and phone reconstruction happens in the read layer (Section 7) at query time.

Raw Tables — Subscriber Data

Exact column-for-column mirrors of the Informix originals. One raw_ table per source file:

Table	Purpose	Join Key
raw_cosdienr	Core subscriber record — address codes, flags, PK	de_alpha_key (PK)
raw_cosbusnm	Business name(s) per subscriber	bn_alpha_key → de_alpha_key
raw_cosphone	Phone numbers (multiple per subscriber)	ph_alpha_key → de_alpha_key
raw_cosinfod	Email addresses and website URLs	inf_alpha_key → de_alpha_key
raw_cospobox	PO Box address	po_alpha_key → de_alpha_key
raw_cosaltad	Free-text alternate address (non-standard)	alt_alpha_key → de_alpha_key

Raw Tables — Lookups (Read-Only)

Reference tables used by the read layer to resolve codes into human-readable values. Never modified by agents:

Table	Purpose	Primary Key
raw_costown	Town / city lookup (includes dialing code)	tw_town_code
raw_cossubrb	Suburb code lookup	sb_suburb_code
raw_cosstrdc	Street code lookup	st_street_code
raw_cosblddc	Building code lookup	bd_build_code
raw_cosregion	Region lookup	rg_region_code
raw_coscountry	Country lookup	cy_country
raw_cosplot	Plot type lookup	pl_plot_code

Operational Tables

import_batches

Tracks each ZIP import for audit and rollback.

CREATE TABLE import_batches (
  id            SERIAL PRIMARY KEY,
  filename      TEXT,
  imported_at   TIMESTAMPTZ,
  record_count  INTEGER,
  notes         TEXT
);

verification_results

AI verification output per subscriber — stores what changed, agent confidence, and current record status.

CREATE TABLE verification_results (
  alpha_key              INTEGER PRIMARY KEY,
  verified_at            TIMESTAMPTZ,
  verified_business_name TEXT,
  verified_phone         TEXT,
  verified_email         TEXT,
  verified_website       TEXT,
  confidence_score       NUMERIC,
  changes_made           JSONB,          -- what changed
  call_transcript        TEXT,
  agent_notes            TEXT,
  record_status          TEXT DEFAULT 'pending'
                         -- pending | in_progress | verified | failed | dnc
);

tracking

Handshake table written back to Carl — tells Informix which records are ready to load.

CREATE TABLE tracking (
  alpha_key    INTEGER,
  verified_at  TIMESTAMPTZ
);

Read Layer — Agent Interface

Agents never query raw tables directly. All reads go through PostgreSQL views or a Node.js API layer. The read layer handles phone reconstruction, address code resolution, business name selection, email/web extraction, and DNC flagging — all computed at query time from raw data.

What the Read Layer Resolves

• Phone reconstruction — integer → dialable string using ph_phone_ind + tw_dialing_code
• Address code resolution — code FKs → readable street / suburb / town / building
• Business name — selects bn_line_no = 1 from raw_cosbusnm
• Email + website — extracted from raw_cosinfod
• DNC flag — derived from de_sch_do_not_call on raw_cosdienr
• Record status — joined from verification_results

subscriber_view

Primary PostgreSQL view — the single interface agents use to read subscriber data:

CREATE VIEW subscriber_view AS
SELECT
  d.de_alpha_key                    AS alpha_key,
  b.bn_bus_name                     AS business_name,

  -- Phone reconstruction
  CASE
    WHEN p.ph_phone_ind = 'Y'
      THEN tw.tw_dialing_code || p.ph_phone::text
    WHEN p.ph_phone_ind IN ('C','V','S','T')
     AND tw.tw_country = 'SOUTH AFRICA'
      THEN '0' || p.ph_phone::text
    ELSE p.ph_phone::text
  END                               AS phone,
  p.ph_phone_ind                    AS phone_type,
  p.ph_fax_ind                      AS fax_type,

  -- Address (code-resolved)
  COALESCE(a.alt_address,
    CONCAT_WS(', ',
      NULLIF(bd.bd_build_name,''),
      NULLIF(CONCAT_WS(' ', d.de_build_no, d.de_street_no),''),
      st.st_street_name,
      sb.sb_suburb_name,
      tw.tw_town_name
    )
  )                                 AS address,

  -- Email & website
  ie.inf_data                       AS email,
  iw.inf_data                       AS website,

  -- DNC flag
  CASE
    WHEN d.de_sch_do_not_call IS NOT NULL
     AND d.de_sch_do_not_call <> ' '
      THEN true ELSE false
  END                               AS do_not_call,

  -- Status
  COALESCE(v.record_status, 'pending') AS record_status,
  tw.tw_dialing_code                AS dialing_code

FROM raw_cosdienr d
LEFT JOIN raw_cosbusnm  b  ON b.bn_alpha_key   = d.de_alpha_key
                           AND b.bn_line_no     = 1
LEFT JOIN raw_cosphone  p  ON p.ph_alpha_key   = d.de_alpha_key
LEFT JOIN raw_costown   tw ON tw.tw_town_code  = d.de_town_code
LEFT JOIN raw_cossubrb  sb ON sb.sb_suburb_code= d.de_suburb_code
LEFT JOIN raw_cosstrdc  st ON st.st_street_code= d.de_street_code
LEFT JOIN raw_cosblddc  bd ON bd.bd_build_code = d.de_build_code
LEFT JOIN raw_cosinfod  ie ON ie.inf_alpha_key = d.de_alpha_key
                           AND ie.inf_type     = 'E'
LEFT JOIN raw_cosinfod  iw ON iw.inf_alpha_key = d.de_alpha_key
                           AND iw.inf_type     = 'W'
LEFT JOIN raw_cosaltad  a  ON a.alt_alpha_key  = d.de_alpha_key
LEFT JOIN verification_results v ON v.alpha_key = d.de_alpha_key;

Write Path

Agents write proposed changes back via the write layer only (Section 11). They never INSERT or UPDATE raw tables directly. All proposed changes are stored in human-readable form in verification_results, then encoded by the write layer at export time.

Import Process

A step-by-step pipeline that takes Carl's ZIP dump from raw files into the PostgreSQL staging database. No flattening or transformation — raw tables mirror Informix exactly.

Pipeline Steps

1. Receive ZIP from Carl → extract to staging folder
2. Run import script — loads all .txt files into corresponding raw_* tables (pipe-delimited, handle nulls)
3. Load all 13 tables:
   • 6 subscriber tables → raw_cosdienr, raw_cosbusnm, raw_cosphone, raw_cosinfod, raw_cospobox, raw_cosaltad
   • 7 lookup tables → raw_costown, raw_cossubrb, raw_cosstrdc, raw_cosblddc, raw_cosregion, raw_coscountry, raw_cosplot
4. Initialise verification_results — insert a row per de_alpha_key with record_status = 'pending'
5. Log import batch to import_batches

Exclusion Filter

Applied in the read layer (subscriber_view), not during import. This ensures raw data is preserved exactly as received. The filter mirrors Carl's sql.sql WHERE clause:

WHERE (de_print_code IS NULL OR de_print_code = ' ')
  AND (de_z_entry    IS NULL OR de_z_entry    = ' ')
  AND (de_specific   IS NULL OR de_specific   = ' ')
  AND  bn_bus_name NOT LIKE ',,%%'
  AND  bn_bus_name NOT LIKE '%%DELETE%%'
  AND  bn_line_no  = 1

Batch Ingestion Strategy

Carl will send updated file dumps periodically. Each dump contains a full export for the relevant dialing area — not a delta. The ingestion strategy must handle new records, updated records, unchanged records, and de-listed records without destroying verification history.

Strategy: Upsert + Change Detection

1. STEP 1 — Load to temp tables
   Load all incoming .txt files into temporary tables (temp_cosdienr, temp_cosbusnm, temp_cosphone, temp_cosinfod, etc.) in the same pipe-delimited format. Do not touch raw_ tables yet.
2. STEP 2 — Compare and classify (by alpha_key)
   Compare temp tables vs raw_ tables. Classify every record:
   • NEW: alpha_key exists in temp but not in raw_ → INSERT to raw_, set record_status = 'pending'
   • UPDATED: alpha_key exists in both, but one or more field values differ → UPDATE raw_, reset record_status = 'pending' (re-verification required)
   • UNCHANGED: alpha_key exists in both, all fields identical → no-op, preserve existing verification_results
   • INACTIVE: alpha_key exists in raw_ but NOT in temp → set record_status = 'inactive' (de-listed from Informix, skip verification)
3. STEP 3 — Pending write-back guard
   Before overwriting any UPDATED record, check the tracking table. If a write-back for this alpha_key exists in tracking but has not yet been acknowledged by Carl (i.e. no confirmation that Carl has loaded it), flag the conflict and do NOT overwrite. Log it in import_batches.notes for manual review.
4. STEP 4 — Log the batch
   Write a record to import_batches with counts: new_count, updated_count, unchanged_count, inactive_count, conflict_count. Drop temp tables.

Re-verification Queue

After each import batch, only records with record_status = 'pending' enter the verification queue. UNCHANGED records are never re-queued. This keeps agent workload proportional to what actually changed in Informix — not the full dataset.

import_batches Table (Updated Schema)

id              SERIAL PRIMARY KEY
filename        TEXT
imported_at     TIMESTAMPTZ
new_count       INTEGER
updated_count   INTEGER
unchanged_count INTEGER
inactive_count  INTEGER
conflict_count  INTEGER
notes           TEXT

tracking Table (Clarification)

AI Verification Flow

Two AI agents work in sequence: General enriches subscriber data online, then Claire places a voice call to verify. Agents read from subscriber_view (read layer) and write results to verification_results.

Verification Pipeline

1. General agent queries subscriber_view where record_status = 'pending' AND do_not_call = false
2. General enriches online — website check, address confirmation, business status
3. Claire (ElevenLabs voice, ZA female) calls reconstructed phone number from the view
4. Call outcome written to verification_results
5. record_status updated: verified / failed / dnc
6. On completion: alpha_key + timestamp written to tracking table

Call Eligibility Check

ALWAYS enforced before dialling — all conditions must be true:

Condition	Check
do_not_call	Must be false (read layer derives from de_sch_do_not_call)
record_status	Must be 'pending'
phone	Must be NOT NULL (reconstructed by read layer)

Scrub — Validation Layer

Scrub runs after Claire completes her call and captures updates. It is a deterministic rules engine — not an AI model — that validates every field Claire modified before it reaches the write layer. Nothing goes to the write layer without passing through Scrub.

Validation Rules

Field	Rule	Fail Action
Phone	Valid SA format: 10 digits, starts with 0 (mobile) or dialcode+number (landline)	RED — do not write back
Email	Valid format: contains @ and domain, no spaces	RED — do not write back
Website	Has valid TLD, reachable (optional check)	AMBER — write back with warning
Address	Street / suburb / town resolvable to Informix codes	If unresolvable → route to cosaltad instead
Business Name	No leading ,,, no DELETE, not blank	RED — do not write back

Outcome Logic

Outcome	Condition	Action
GREEN	All fields pass	Send to write layer
AMBER	Soft issues only	Send to write layer with flags noted in verification_results
RED	Any hard failures	Block write-back, set record_status = 'failed', log reason in verification_results.agent_notes

Write-Back Format

Agents propose changes in human-readable form. The write layer encodes those changes back into Informix-compatible raw format. Unchanged records are output directly from raw_* tables with no transformation. tracking.txt is always included.

Write-Layer Encoding

Data Type	Encoding Rule	Target Table
Phone	Strip leading zero or dialing code, store as INTEGER, set ph_phone_ind accordingly	raw_cosphone
Address	Lookup in raw_cosstrdc / raw_cossubrb / raw_costown; if no matching code found, write to raw_cosaltad as free text	raw_cosdienr or raw_cosaltad
Email / Website	Update raw_cosinfod directly	raw_cosinfod
Business Name	Update raw_cosbusnm directly	raw_cosbusnm

Output Files

File	Contents
tracking.txt	Always included — all alpha_keys verified with timestamps
cosdienr.txt	Output from raw_cosdienr; includes address code changes if any
cosphone.txt	Output from raw_cosphone; phone stored as integer with updated ph_phone_ind
cosinfod.txt	Output from raw_cosinfod; email / website updates
cosbusnm.txt	Output from raw_cosbusnm; business name updates
cosaltad.txt	Output from raw_cosaltad; non-standard addresses that couldn't be resolved to codes

Format Rules

• Same pipe-delimited layout as input files
• Same column order per .sql schema files
• Unchanged records: output from raw_* tables directly — no transformation
• Changed records: write layer applies encoding, then outputs in same format
• tracking.txt always included in every export

Open Items / TBD

Outstanding decisions and dependencies that affect the pipeline build. Items are tracked here until resolved with Brabys IT or confirmed internally.

Item	Status	Notes
B4 — Merge approval trigger	TBD	Manual vs scheduled — pending Brabys decision
Phone dialcode table completeness	TBD	Sample data is 012 (Pretoria) only — need full costown for all regions
Write-back file delivery method	Confirmed	Same folder-drop method as inbound
SIP credentials for Claire	Expected	Brabys IT to provide 3CX SIP account (this week)
Full Informix table list	Confirmed	13 tables confirmed with Carl

Informix Entity-Relationship Diagram

All 13 Informix tables with foreign-key relationships. cosdienr is the central hub — every subscriber table joins via alpha_key, and address/phone resolution flows through the lookup tables on the right.