# DB schema similarity — RESULTS

Date: 2026-09-14. Inventory taken live from 50.21.183.111 (sys.tables/sys.columns/sys.partitions).

## Overall numbers
- DBs in analysis: **50** (all ONLINE, database_id > 4)
- Unique table names total: **5097**
- Median tables per DB: **3385**
- Maximum: 2610BERRYOILUAP01 — 4554 tables, 97 281 columns
- Minimum: U22930128\SQL2022 — 6 tables (service), i21Hangfire — 11 (Hangfire jobs)

## Clusters (Jaccard on table sets, threshold 0.40)

### CLUSTER_1 — 43 DBs: main iRely i21 ERP (UAP products)
Composition: all customer UAP/AGNOSTIC/ECOM/cfg-like product DBs
(2210PIGOTTOIL, 2430DDENERGY, 2610JOHNSONPETROLEUM, MCP, BerryOil, Dallmyr,
NewtonOil, StrohFarm(no—see below), WAMAB3, LewisOil, GeneralPetro, ECOM*, Agnostic*, etc.)

**Cluster core: 2808 tables present in 100% of the 43 DBs** (>=95%: 2979).
This is the unified i21 schema; differences are pluggable modules and junk/backup tables.

### CLUSTER_2 — 5 DBs: cfg DBs (2210DAVISOILUAPcfg, 2210JOHNSONPETROUAPcfg, GENERALPETROUAPcfg, JMREYNOLDSUAPcfg, NEWTONUAPcfg)
- 4 of 5 are identical: Jaccard = 1.000, 14 tables each (`ss*mst` — i21 system configuration tables: sscapmst, sscommst, ssctlmst, sseulmst, sslacmst, ssmgpmst, ssmnumst, sspagmst, sspatmst, sspgmmst, ssprtmst, sspwdmst, sssesmst, sstrcmst)
- **NEWTONUAPcfg: 15 tables** — additionally `coctlmst` (J with the others = 0.933)
- These are i21 configuration DBs (not customer data)

### CLUSTER_3 — U22930128\SQL2022 (6 tables) — instance service DB
### CLUSTER_4 — i21Hangfire (11 HangFire_* tables) — application background-task queue

## Similarity within CLUSTER_1
- Average Jaccard across all 1225 pairs: 0.619 (lowered by cfg/hangfire/service DBs)
- Inside the cluster, pairs are nearly identical: 0.99–1.00
  - GENERALPETROUAP101 == GENERALPETROUAP201 (3592 common, J=1.000)
  - LEWISOILUAP101 == LEWISOILUAP201 (J=1.000)
  - PalmdaleAgnosticF01 == PalmdaleAgnosticV01 (J=0.999)
  - FEHRENBACHER == GENERALPETRO (J=0.999)
  - ECOMMAIN(2630) == ECOMPORTAL (J=0.998–0.999)

## Unique DB tables (deviations from the core) — 15 DBs out of 43
TOP by number of unique tables:
| DB | Unique tables | Character |
|---|---|---|
| 2610RTROGERSUAP01 | 48 | custom reports/staging (RTCust, PerformanceIndex), bak tables |
| WAMAB3UAP01 | 33 | custom CT-balance logs (tblCTContractBalanceLog*BU) |
| 2610BERRYOILUAP01 | 20 | bak* tables (in-DB table backups, 2016–2018) |
| 2410WoodfordUAP101 | 18 | _cnv_* conversion tables (data migration) |
| 2430BLUPETROLEUMUAP01 | 9 | baktbl* from 11/2024 |
| 2430DDENERGYUAP101 | 7 | bak/dup tables (GLGJDup, baktblGLDetail) |
| 2210CHILDERSOILUAP01 | 6 | tblCT* custom (CostCenter, GeneralLedger...) |
| JMREYNOLDSUAP01 | 5 | tmp-import tables, ptjddmst_13OCT |
| 2610NEWTONOILUAP01 | 3 | Bak tables 05/2025, TRLoad |
| BANNERFURNANCEFUELUAP01 | 3 | _cnv_pp_* (migration) |

Character of deviations: (1) in-DB backup tables bak*/baktbl* with dates,
(2) _cnv_* conversion tables from migrations, (3) customer custom (reports, balance logs),
(4) tmp import tables. No new secret modules were identified.

## Modular composition of the i21 core (tblXX* prefixes)
| Prefix | Tables | Module (per i21 source) |
|---|---|---|
| tblIC* | 374 | Inventory Control |
| tblRK* | 372 | Rack/pricing |
| tblSM* | 333 | System Manager |
| tblCT* | 287 | Contracts |
| tblMF* | 278 | Manufacturing |
| tblIP* | 233 | iProcess |
| tblAR* | 222 | Accounts Receivable |
| tblAP* | 179 | Accounts Payable |
| tblCF* | 168 | Card Fueling |
| tblST* | 160 | Store |
| tblLG* | 157 | Logistics |
| tblSC* | 147 | Scale |
| tblHD* | 139 | Help Desk |
| tblTM* | 125 | Tank/Transport Mgmt |
| tblGL* | 124 | General Ledger |

## Conclusions for the project
1. **All 43 product DBs are the same i21 schema** (core 2808 tables = 83% of median composition). Data analysis can be done against a single representative schema; per-DB review is needed only for unique tables.
2. **Unique tables are mostly junk** (bak/_cnv/tmp). Real custom content is only in RTROGERS (reports) and WAMAB3 (CT logs).
3. **cfg DBs are identical** (14 tables) — product configuration, not data.
4. **i21Hangfire** — application background tasks: in HangFire_Job/State there may be job parameters (incl. integrations) — a candidate for targeted review.
5. For the backup plan: the structure confirms that a .bak of any DB is self-sufficient — the schema is unified, the i21 encryption keys are the same everywhere (verified earlier on 2210PIGOTTOILUAP01).

## Artifacts (all in schema_inventory/)
- columns.tsv — 2 669 928 columns (db|tbl|col|typ|max_len), 170 MB
- tables.tsv — 152 182 pairs (db|tbl)
- rowcounts.tsv — 152 182 tables with row counts (db|tbl|rows)
- core_tables.csv — each table → in how many DBs it appears
- cluster1_core.txt — 2808 core tables of CLUSTER_1
- similarity_matrix.txt — 1225 pairs (jaccard, common_tables, cosine by column signatures)
- cluster_groups/cluster_1..4.tsv — cluster composition
- analyze_schema_similarity.py — script (re-runnable)