# PhilGEPS eBidding ERD extraction

Use the [JSON catalog](../data/eBidding_ERD.schema.json) for programmatic lookup and source traceability. Use the [field CSV](../data/eBidding_ERD.fields.csv) for spreadsheet or SQL analysis. Each CSV record represents one field as drawn.

## Use the PhilGEPS Data Explorer

Run this from the sprint directory and open http://localhost:8000/ in your browser.

```bash
rtk proxy python3 -m http.server 8000 --bind 127.0.0.1
```

In **eBidding ERD**, search by table or field name, compare fields in Overview, and open individual diagram boxes in Single table. **Open Data** lists public export layouts and aliases. **OCDS Mapping** shows the workbook comparison and the Open Data ETL field and codelist mappings. **Sources** groups the downloadable references for each dataset.

### Publish with GitHub Pages

The workflow assumes the workspace root is the Git repository and the sprint stays at its current path. Push the sprint files and the root `.github/workflows/philgeps-explorer-pages.yml` file to the default branch. On GitHub, set **Settings → Pages → Build and deployment → Source** to **GitHub Actions**, then run **Publish PhilGEPS Schema Explorer** from the Actions tab on the default branch. Rerun that workflow when you want to publish updates. [GitHub Pages publishing settings](https://docs.github.com/en/pages/getting-started-with-github-pages/configuring-a-publishing-source-for-your-github-pages-site)

The workflow publishes the explorer and the files listed on Sources at the Pages site root. If the destination repository already uses Pages, use a dedicated repository or integrate the explorer into its existing Pages workflow to keep that site available.

## Reproduce the exports

From the sprint directory, run the [extractor](../scripts/extract_erd.py) with Python 3. It uses only the standard library and reads the [source diagram](../data/eBidding_ERD.drawio).

```bash
rtk proxy python3 scripts/extract_erd.py
```

The command writes both exports under `data/`. Pass a source path to extract a different uncompressed draw.io file with the same layout. Pass `--output-dir` to direct the outputs elsewhere.

## Extraction rules

1. Read each page's uncompressed draw.io XML. A cell with `shape=table` defines a table box. Its `tableRow` children define fields. The script orders rows by vertical position in the box and reads the two row cells from left to right as the optional key marker and field label.
2. Strip HTML tags, decode HTML entities, and trim surrounding whitespace in display labels. Preserve capitalization and spelling. Keep the original draw.io label value in `raw_label`. Keep page, box, field row, and field cell IDs so an exported field can be traced back to the source.
3. Index the boxes by candidate table name. Remove continuation suffixes such as `(continued...)`, `..`, and `...2` for indexing. The explicit alias `sup_platimun_registration..1` groups that box with the neighboring `sup_platinum_registration` box and its `..2` continuation. The original box label remains available.
4. Retain every box and field occurrence. The `table_index` maps each candidate name to page and box IDs in source order. Its two counts show how many base boxes exist and how many distinct ordered field lists those base boxes contain, comparing field names and key markers. In the CSV, use `candidate_name` to filter and `page_id`, `block_id`, and `ordinal` to identify and order rows within a box.
5. Record the diagram's connectors with their endpoint cell IDs. Both connectors in this file link a cell to itself. The left column holds only blank cells and explicit `PK` markers. Foreign-key references, types, nullability, and defaults would require another source such as database DDL. Field names beginning with `fk_` are copied as written.

## Results and verification

| Item | Count |
| --- | ---: |
| Candidate table names | 291 |
| Table boxes | 341 |
| Continuation boxes | 20 |
| Field rows | 4,267 |
| Explicit `PK` markers | 319 |
| Diagram connectors | 2 |
| Candidate names with multiple base boxes | 28 |
| Candidate names with differing base definitions | 13 |

Review groups with multiple base boxes before consolidating fields into a single schema. For example, the two base boxes for `template_nexsheets_column_analytics` show different field spellings. Candidate names are lookup keys for the diagram.

Verification matched the JSON row counts and field IDs to the source XML, compared all CSV rows with the JSON, checked that both connector endpoints resolve to their original cells, and reproduced byte-identical exports in a temporary directory.
