# 02 — Data model

## Database abstraction layers

The codebase mixes **three** DB access generations:

### 1. SDBA (modern, ~2014+)
- File: `httpdocs/auth/classes/sdba/sdba.php`
- Version: `1.0.6`
- Pattern: singleton + fluent builder, mysqli-based
- Typical use: `Sdba::table('iweb_accounts')->where('email', $e)->get()`
- Features: JOINs, GROUP BY, fulltext, prepared statements (internally)
- Credentials resolved via `Sdba::params($key)` against the arrays in `dbconnect.php`

### 2. dsX / iSQL (legacy, ~2001–2010)
- Files: `httpdocs/search/dsX/dsX.inc` + `httpdocs/search/dsX/iSQL/`
- Pattern: Record-object wrappers; e.g., `Listing.inc`, `Account.inc`, `CategoryNavigator.inc`
- Uses mysqli under the hood but with lots of raw query concatenation in call sites

### 3. Raw `mysql_*` (deprecated, pre-2010)
- Files: `httpdocs/inc/order.php`, `httpdocs/inc/sitemap.php`, `httpdocs/classes_new/minibots.class.php`
- Uses `mysql_pconnect`, `mysql_query`, `mysql_fetch_object`, `mysql_real_escape_string`
- **Fatal under PHP 7.0+** — see [07-code-health.md](07-code-health.md)

## Database credentials location

`httpdocs/auth/classes/sdba/dbconnect.php` stores **all** DB credentials in plain text as PHP associative arrays, one per database. See [11-database-credentials.md](11-database-credentials.md) for the structure (passwords redacted).

## Table catalog

Table names extracted from `httpdocs/search/dsX/tabledefs.inc` (lines 46–119) and query grep across `httpdocs/`.

### Search / directory core
| Table | Purpose |
|-------|---------|
| `iweb_hyperseek_links` | Indexed listings / directory entries |
| `iweb_hs_categories` | Category taxonomy (hierarchical) |
| `iweb_hyperseek_stats` | Per-listing usage statistics |
| `iweb_hyperseek_keys` | Keyword-to-listing mapping |
| `iweb_hyperseek_cats` | Category-to-listing mapping |
| `iweb_hs_featured` | Promoted / featured listings |
| `iweb_sponsor_boxes` | Sponsor ad slots |
| `wwbn_distributions` | Distribution channels (cross-site syndication) |
| `wwbn_tubes` | Video sources |

### Accounts / billing
| Table | Purpose |
|-------|---------|
| `iweb_accounts` | User accounts: advertisers, publishers, admins |
| `iweb_acct_summary` | Running balance snapshot per account |
| `iweb_acct_transactions` | Append-only ledger |
| `iweb_acct_deposits` | Deposit events (writes from `receive-payment.php`) |
| `iweb_acct_withdrawls` | Withdrawal/payout events (note: spelling follows source) |

### Support
| Table | Purpose |
|-------|---------|
| `iweb_support_tickets` | Support cases |
| `iweb_support_ticket_replies` | Thread replies |

### Affiliate / publisher network
| Table | Purpose |
|-------|---------|
| `iweb_affiliate_info` | Publisher affiliate profiles |
| `iweb_affiliate_payments` | Payout history |
| `iweb_affiliate_searches` | Tracked clicks/searches from affiliate widgets |

### Click tracking
| Table | Purpose |
|-------|---------|
| `iweb_jh_click_log` | Raw click events (written by `r.php`) |
| `iweb_jh_click_summary` | Periodic aggregation |
| `iweb_jh_affiliate_summary` | Per-affiliate aggregates |

## Multi-database topology

`dbconnect.php` exposes parameter sets for several databases on the same host:

- `searchmercials` — primary
- `youportal_aroundme_new` — sibling YouPortal directory schema
- `beta_db` — development/staging
- `domain_site` — domain-level registrations / tenant mappings

The app routes to the correct DB per-feature by calling `Sdba::table($tbl, $dbParamsKey)`. **Same username/password across all four databases** — a shared account (`ostv`) with broad privileges.

## Schema evolution signals

- **No migration framework** (no Laravel/Doctrine/Phinx). Schema changes appear to have happened manually in production.
- **Historical migration archive at `data/sql/schemas/`** is the only in-repo record of schema changes:
  - `renametables.patch.done` — bulk `RENAME TABLE accounts TO iweb_accounts; ...` is the origin of the `iweb_` table prefix used throughout the code.
  - `autobilling.patch.done` — creates `iweb_jh_autobill` and cleans matching rows from `iweb_table_def`.
  - `data/sql/schemas/done/` — older `.data`/`.mysql` dumps (`bannermaster`, `categories`, `generic`, `hyperseek`, `jackhammer`).
  - These are **already-applied** one-shot patches; keep read-only.
- `httpdocs/search/dsX/Addons/backfill.inc.bak` — backup of data-import logic; the live `backfill.inc` is what's active.
- `httpdocs/search/data_old/`, `httpsdocs/search_old/` — archived directory snapshots.
- `httpdocs/pages/advertisers_old.php`, `publishers_old.php` — legacy template versions retained for fallback.
- Git history's `7ea37ec` commit note ("removed uncommited changes in prod (backup june 03, 2024)") suggests ad-hoc hot-patches in production that had to be rolled back.

## Sibling schema: iDevAffiliate

`affiliates/affiliate/` is a **separate application** with its own `idevaff_*` tables (e.g. `idevaff_tlog`, `idevaff_colors`, ...) and its own credentials in `affiliates/affiliate/config/database.php`. It shares the MySQL server but not the `iweb_*` schema. Treat as a sibling system for data-model purposes.

## Cross-platform data flow

`/var/www/html/Searchmercials/ypbackfill_feed_data` + the companion `.txt`/`.curl` files indicate a data-sync job:

- **Direction**: Searchmercials → YouPortal (inferred from naming: "yp backfill feed data")
- **Schedule**: No cron entry visible in this project; may be wired from the sibling YouPortal deployment
- **Cross-DB reads**: SDBA's `youportal_aroundme_new` params suggest direct cross-DB access, not an HTTP API boundary

## Indexing / performance

No evidence of query profiling, slow-query logging, or documented index coverage. The app predates observability tooling; ops diagnostics rely on reading MySQL's slow-query log externally (if enabled).