ROUTINE-Data-Model

Good idea. A TL;DR at the top saves future-you (and Simon) from rereading the whole document.

Here’s a clean one you can drop **at the very top of the notes**.

---

1. TL;DR – ROUTINE Release Model

ROUTINE releases are defined entirely by **two tables**:

``` mw_tags mw_tag_pages ```

• `mw_tags.tag_id` stores the ROUTINE label (e.g. `ROUTINE_4.110`)
• `mw_tag_pages` maps each ROUTINE to the **exact page revision** that belongs to that release

``` tag_id → release label tag_page → page_id tag_rev → approved revision for that release ```

Everything else is derived.

1. 1. 1. Source of truth

``` mw_tags mw_tag_pages ```

1. 1. 1. Supporting wiki data

``` mw_page mw_revision ```

1. 1. 1. Derived runtime table

``` mw_approved_revs ```

`mw_approved_revs` is **not migration data** and can always be regenerated from the ROUTINE snapshot.

---

1. Operational Behavior

• • Activate ROUTINE**

``` ROUTINE tag selected

     ↓

mw_tag_pages snapshot identified

     ↓

mw_approved_revs rebuilt ```

• • Rollback**

``` Activate older ROUTINE tag ```

Approved revisions immediately revert to that snapshot.

---

1. Key Insight (Breakthrough)

The ROUTINE system does **not store approval history in mw_approved_revs**.

Instead it stores the **release snapshot** in:

``` mw_tag_pages ```

That snapshot defines exactly which revision of each page belongs to a release.

`mw_approved_revs` is only a **runtime projection** used by the ApprovedRevs extension.

---

1. Safe Test Dataset

To build an offsite development stub you only need:

``` mw_tags mw_tag_pages mw_page mw_revision ```

`mw_approved_revs` can be empty and regenerated during activation.

---

1. One Sentence Summary

• • ROUTINE releases are simply named snapshots of page revisions stored in `mw_tag_pages`.**

Everything else can be rebuilt.

---

If you'd like, I can also give you a **very small ASCII diagram of the data relationships** that fits on one screen. It's surprisingly helpful when someone new inherits the system (Simon in particular).

---

1. ROUTINE Data Model and Test Stub Notes

These notes describe the data structures used by the ROUTINE workflow in MediaWiki and how to construct a reusable development stub dataset for offsite work.

The goal is to preserve the important relationships between ROUTINE labels, pages, and revisions without requiring production data.

---

1. 1. Authoritative ROUTINE Data

The authoritative release mapping lives in two tables.

1. 1. `mw_tags`

Defines each ROUTINE label.

Example fields:

| Field | Purpose | | --------- | ------------------------------------------------- | | `tag_id` | ROUTINE identifier (e.g. `ROUTINE_4.110`) | | `publish` | Indicates whether the tag is considered published | | `locked` | Prevents edits to the tag | | `deleted` | Soft delete flag | | `comment` | Optional deployment comment |

Important note:

In this system the ROUTINE identifier is stored in **`tag_id`**, not `tag_name`.

All extension logic joins on:

``` mw_tags.tag_id ```

Example values:

``` ROUTINE_4.110 ROUTINE_4.111 ROUTINE_4.112 ```

---

1. 1. `mw_tag_pages`

Defines which revision of each page belongs to a ROUTINE release.

Fields:

| Field | Purpose | | ---------- | -------------------- | | `tag_id` | ROUTINE label | | `tag_page` | Page ID | | `tag_rev` | Approved revision ID |

This table represents the **release snapshot**.

Example:

| tag_id | tag_page | tag_rev | | ------------- | -------- | ------- | | ROUTINE_4.110 | 101 | 5001 | | ROUTINE_4.110 | 102 | 5002 | | ROUTINE_4.111 | 101 | 5001 | | ROUTINE_4.111 | 102 | 5009 |

This means:

• page 101 did not change between releases
• page 102 received a new revision in 4.111

---

1. 2. Supporting MediaWiki Core Data

The ROUTINE workflow relies on normal MediaWiki tables.

1. 1. `mw_page`

Identifies pages.

Important fields:

| Field | Purpose | | ---------------- | ----------------------- | | `page_id` | Primary page identifier | | `page_namespace` | Namespace | | `page_title` | Page title | | `page_latest` | Current revision ID |

Relationship:

``` mw_revision.rev_page = mw_page.page_id ```

---

1. 1. `mw_revision`

Stores revision history.

Important fields:

| Field | Purpose | | --------------- | ------------------ | | `rev_id` | Revision ID | | `rev_page` | Page ID | | `rev_timestamp` | Revision timestamp |

Relationship used by ROUTINE logic:

``` mw_tag_pages.tag_rev = mw_revision.rev_id ```

---

1. 3. Derived Runtime Table

1. 1. `mw_approved_revs`

This table is **not part of the migrated ROUTINE data**.

It is a runtime projection used by the **ApprovedRevs extension**.

Schema:

| Field | Purpose | | --------------- | ------------------ | | `page_id` | Page identifier | | `rev_id` | Approved revision | | `approver_id` | User who approved | | `approval_date` | Approval timestamp |

Important constraint:

``` page_id UNIQUE ```

Only one approved revision per page can exist at a time.

---

1. Important Operational Insight

`mw_approved_revs` was **not included in the ROUTINE migration**.

On the sh0re environment the table still existed only because the environment was not dropped during migration.

Therefore:

• • `mw_approved_revs` should be considered derived state.**

It can always be rebuilt from:

``` mw_tags mw_tag_pages mw_revision ```

---

1. 4. Activation Workflow

When a ROUTINE tag is activated:

1. The selected ROUTINE label is verified in `mw_tags`. 2. All pages belonging to that tag are identified via `mw_tag_pages`. 3. `mw_approved_revs` is refreshed to match the snapshot.

Conceptually:

``` mw_tag_pages → mw_revision → mw_approved_revs ```

Rollback is therefore simple:

``` activate ROUTINE_4.110 ```

and the approved revisions revert to that snapshot.

---

1. 5. Reusable Test Dataset

A reusable development stub should include:

1. 1. 1. Required tables

``` mw_tags mw_tag_pages mw_page mw_revision ```

1. 1. 1. Optional

``` mw_approved_revs (empty) ```

---

1. Recommended Stub Structure

A useful small dataset contains:

| Item | Suggested Count | | ------------ | --------------- | | ROUTINE tags | 3 | | pages | 5–8 | | revisions | 6–10 | | tag mappings | 8–15 |

---

1. Example Release Story

1. 1. 1. ROUTINE_4.110

Pages:

``` Startup_Procedure Shutdown_Procedure Network_Check Incident_Escalation ```

1. 1. 1. ROUTINE_4.111

Pages:

``` Startup_Procedure (unchanged) Shutdown_Procedure (new revision) Backup_Verification (new page) Incident_Escalation (unchanged) ```

This creates realistic test scenarios:

| Scenario | Purpose | | -------------- | ---------------------- | | unchanged page | verify revision reuse | | updated page | verify revision update | | removed page | verify exclusion | | new page | verify clone/import |

---

1. 6. Minimal Fields That Must Be Accurate

The following identifiers must remain correct in any stub dataset:

``` mw_tags.tag_id mw_tag_pages.tag_id mw_tag_pages.tag_page mw_tag_pages.tag_rev mw_page.page_id mw_page.page_title mw_revision.rev_id mw_revision.rev_page mw_revision.rev_timestamp ```

Everything else may be dummy data if necessary.

---

1. 7. Future Importer Design

If a JSON/API importer is later introduced, its logical structure should mirror the release mapping.

Example conceptual payload:

```json {

 "tag_id": "ROUTINE_4.112",
 "pages": [
   {
     "page_id": 101,
     "rev_id": 5001
   },
   {
     "page_id": 102,
     "rev_id": 5009
   }
 ]

} ```

This maps directly to `mw_tag_pages`.

---

1. Final Operational Principle

The **source of truth** for ROUTINE releases is:

``` mw_tags + mw_tag_pages ```

Everything else can be regenerated.

This makes rollback, cloning, and importer workflows safe and predictable.

---