ROUTINE-Data-Model

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 (for example 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.

Source of truth

mw_tags
mw_tag_pages

Supporting wiki data

mw_page
mw_revision

Derived runtime table

mw_approved_revs

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

---

ROUTINE Data Relationships (Overview)

Table	Role	Key Fields	Notes
mw_tags	Defines each ROUTINE release label	tag_id publish locked deleted comment	Example: ROUTINE_4.110
mw_tag_pages	Release snapshot mapping pages to revisions	tag_id tag_page tag_rev	Defines exactly which revision belongs to a ROUTINE release
mw_revision	Stores revision history	rev_id rev_page rev_timestamp	Each revision belongs to a page
mw_page	Page identity and metadata	page_id page_namespace page_title page_latest	Used for display and navigation
mw_approved_revs	Runtime projection used by ApprovedRevs	page_id rev_id approver_id approval_date	Derived table rebuilt during ROUTINE activation

---

Data Flow

mw_tags
   ↓
mw_tag_pages
   ↓
mw_revision
   ↓
mw_page

Activation rebuilds:

mw_tag_pages → mw_revision → mw_approved_revs

---

Important Principle

The authoritative ROUTINE snapshot lives in:

mw_tags + mw_tag_pages

Everything else can be regenerated.

---

Administrator Rule

Do not edit mw_approved_revs manually.

If something looks wrong in ApprovedRevs:

1. Activate the correct ROUTINE tag.
2. Allow the system to rebuild mw_approved_revs from the ROUTINE snapshot.

This guarantees consistency between the release label and approved revisions.

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.

---

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.

---

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.

---

One Sentence Summary

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

Everything else can be rebuilt.

---

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. Authoritative ROUTINE Data

The authoritative release mapping lives in two tables.

mw_tags

Defines each ROUTINE label.

Field	Purpose
tag_id	ROUTINE identifier (for example 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

---

mw_tag_pages

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

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

---

2. Supporting MediaWiki Core Data

The ROUTINE workflow relies on normal MediaWiki tables.

mw_page

Identifies pages.

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

---

mw_revision

Stores revision history.

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

---

3. Derived Runtime Table

mw_approved_revs

This table is not part of the migrated ROUTINE data.

It is a runtime projection used by the ApprovedRevs extension.

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.

---

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

---

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. ```

---

This version will render cleanly in MediaWiki because it uses:

• `== headings ==`
• `{| class="wikitable" |}` tables
• `

  ` blocks instead of ``` fences

* `` inline formatting ---