Exhibit_B.docx

Record Promotion Workflow Single-Database Status Lifecycle Model

1. Overview This document defines the functional and technical requirements for the Regulation Promotion Workflow – a single-database content lifecycle management system for the Fishing Regulations Data Management platform. The workflow governs how regulatory changes progress from authoring through internal review to public-facing publication, without requiring multiple database instances.

1.1 Purpose

The Regulation Promotion Workflow provides administrative users with a controlled, auditable mechanism to:

· Author and revise regulation and rule records without affecting published content

· Stage approved changes for pre-publication review

· Publish finalized content to the public-facing API

· Safely discard or revert changes at any stage

1.2 Scope

This document covers:

· The status lifecycle model applied to Regulations and FishingRules tables

· The Draft-on-Edit cloning mechanism

· The Promotion Portal administrative UI and its functional requirements

· Discard and revert behaviors

· Schema changes required to support the workflow

1.3 Background

The existing transactional data management site allows approved users to create and edit regulations and rules, as well as manage lookup tables. As the platform expands to include a public-facing website, a controlled release mechanism is required to prevent unfinished or unapproved changes from being exposed publicly. The Regulation Promotion Workflow addresses this need through a status-flag-based approach operating entirely within a single database instance, eliminating the complexity of multi-database synchronization.

1.4 Design Philosophy

Single database. All data – Draft, Staged, and Published – lives in one database instance.

No sync operations. Promotion is a status flag change, not a data copy.

Non-destructive. Published records are never deleted; they are Archived with a soft-delete flag.

Auditable. Every state transition is traceable to a user and timestamp.

2. Status Lifecycle Model The workflow introduces a status field on both the Regulations and FishingRules tables. Each record exists in exactly one of four states at any given time.

2.1 Status Definitions

* OriginRegulationId and OriginRuleId shall be used to filter out which existing Regulation and Rules to exclude

2.2 Status Transition Diagram

Records progress through the following states:

Note: A Staged record that is discarded reverts to Draft. A Published record that is superseded transitions to Archived.

2.3 Discard Behavior by Status

Staged records - The Discard action is the only route back to an editable state

3. Draft-on-Edit Mechanism To prevent editors from accidentally modifying live Published records, the system implements a Draft-on-Edit pattern. When an authorized user initiates an edit on a Published regulation, the system automatically creates a Draft clone rather than opening the live record for direct modification.

3.1 Clone Trigger

The clone process is triggered when:

· An editor clicks Edit on a Published regulation record

· No existing Draft for that regulation (identified via [OriginRegulationId] / [OriginRuleId]) already exists If a Draft already exists for the regulation, the user is routed to the existing Draft instead of creating a second conflicting copy. The system must enforce a one-Draft-per-regulation constraint.

3.2 Clone Regulations Process

When the clone is initiated, the system performs the following steps atomically:

1. Creates a new Regulations row, copying all field values from the Published record.

2. Sets status = 'Draft' on the new row.

3. Sets [OriginRegulationId] on the new row to point to the original Published record's [RegulationId].

4. Clones all FishingRules associated with the Published record with the Clone Rule Process, linking them to the new Draft regulation.

5. Absorbs any outstanding Unaffiliated Draft or Staged FishingRules associated with the original Published regulation into the new Draft regulation (see Section 3.8).

6. Redirects the user to the Draft for editing. The Published record remains untouched.

3.3 Clone Rule Process

When the clone is initiated, the system performs the following steps atomically:

1. Creates a new Rule row, copying all field values from the Published record.

2. Sets [Status] = 'Draft' on the new row.

3. Sets [OriginRuleId] on the new row to point to the original Published record's [FishingRuleId].

4. Redirects the user to the Draft for editing. The Published record remains untouched.

3.4 Editing Draft and Staged Records

Once the Draft clone is created, the editor interacts with it as a standard CRUD form. They may:

· Modify any field on the Draft regulation record

· Modify any field on any affiliated Draft FishingRule record

· Add new FishingRule records directly to the Draft regulation

· Delete individual affiliated Draft FishingRule records (see deletion behaviour below) Staged records are locked for editing. Once a regulation and its affiliated rules have been promoted to Staged status, no field modifications, or rule additions, or rule deletions are permitted until the record is either published or discarded back to Draft. If corrections are required after staging, the administrator must use the Discard action in the Promotion Portal to revert the regulation and its affiliated rules to Draft status before editing can resume.

At no point during draft editing is the Published record or any of its affiliated Published FishingRule records modified. The Published version remains live and accessible to the public-facing API throughout the editing process.

3.4.1 Deleting an individual affiliated rule during Draft editing When an editor deletes an individual FishingRule affiliated with a Draft regulation, the system must determine whether the rule is a clone of a Published record or a net-new addition:

· If the rule has an [OriginRuleId] value (i.e., it was cloned from a Published record), the Draft rule record is hard deleted immediately. At publish time, the atomic publish transaction (§3.7.1) will detect that no Staged counterpart exists for the original Published rule and will Archive it accordingly.

· If the rule has no [OriginRuleId] (i.e., it is a net-new rule added during drafting), the record is hard deleted immediately with no downstream effect on Published content.

In both cases, deletion of an individual affiliated rule does not affect the parent Draft regulation record, which continues through its normal promotion lifecycle.

3.5 Deleting Draft and Staged Records

3.5.1 Draft Records

Deleting a Draft record shall perform a hard delete removing the record entirely from the database. When a Draft Regulation is deleted, all associated Draft Rules shall also be hard deleted Comment by Van Diggelen, Amanda@Wildlife: I don't think this hard delete is great Comment by Davis, Ian@Wildlife: Only drafts are hard deleted. This is primarily don’t to make sure the database doesn’t explode with deleted drafts that were never realized.

Also this means that any IsDeleted record was once in Production.

3.5.1 Staged Records

Deleting a Staged record shall reverts Staged Regulation and all associated Rules to status = 'Draft'.

3.6 Required Schema Additions

3.6.1 Regulations Table

The following columns are required on the Regulations table to support the Draft-on-Edit mechanism:

3.6.2 FishingRules Table

The following columns are required on the Regulations table to support the Draft-on-Edit mechanism:

3.7 Publish Transition – Atomicity Requirement

3.7.1 Publishing a Staged Regulation

When a Staged regulation is published, the following operations must execute as a single atomic transaction to prevent data inconsistency:

· The Staged regulation record is updated: Status = 'Published'

· All FishingRules associated with the Staged regulation are updated: Status = 'Published'

· The prior Published Regulation record (identified via [OriginRegulationId]) is updated as follows: [Status] = 'Archived', [DeletedTime] set to current Datetime, and [DeletedBy] set to the user who initiated publication

· The prior Published FishingRule records (identified via [OriginRuleId]) are updated as follows: [Status] = 'Archived', [DeletedTime] set to current Datetime, and [DeletedBy] set to the user who initiated publication

· If [OriginRegulationId] is NULL (i.e., the regulation is a new record, not a clone of an existing Published record), the archival steps are skipped and only the status updates to 'Published' are performed

· if a Published rule's [OriginRuleId] is not matched by any Staged rule at publish time, that Published rule is still Archived.

3.7.2 Publishing an Unaffiliated Staged Rule

When an Unaffiliated Staged rule is published, the following operations must execute as a single atomic transaction:

· The Staged rule record is updated: Status = 'Published'

· The prior Published FishingRule record (identified via [OriginRuleId]) is updated as follows: [Status] = 'Archived', [DeletedTime] set to current Datetime, and [DeletedBy] set to the user who initiated publication

· If [OriginRuleId] is NULL (i.e., the rule is a new record, not a clone of an existing Published record), the archival step is skipped and only the status update to 'Published' is performed

If any step in either publish transaction fails, the entire operation must be rolled back.

The public API must always filter WHERE Status = 'Published' AND [DeletedTime] IS NULL to ensure only current, active records are returned..

3.8 Regulation Parenthetical Dropdown — Filtering Rules

The Regulation Parenthetical dropdown field on the Rule form lists Regulation Codes that a rule may be associated with. This dropdown must be filtered to prevent users from selecting regulations that are inactive, superseded, or mid-revision.

3.8.1 Inclusion Criteria

The dropdown shall display Regulation Codes from records meeting any of the following conditions:

· Status = 'Published' AND [DeletedTime] IS NULL AND [RegulationId] is NOT referenced as the [OriginRegulationId] of any Draft or Staged record

· Status = 'Draft' or Status = 'Staged' (so editors can assign rules to regulations that are currently being revised but have not yet been published)

3.8.2 Exclusion Criteria

The dropdown shall exclude Regulation Codes from records meeting any of the following conditions:

· Status = 'Archived'

· Status = 'Published' AND [RegulationId] IS referenced as the [OriginRegulationId] of an active Draft or Staged record (i.e., a revision is in progress and the Draft/Staged clone is the correct target for new rule assignments)

· [DeletedTime] IS NOT NULL

3.8.3 Rationale

When a Published regulation is being revised, two records exist simultaneously: the original Published record and its Draft/Staged clone. If both appeared in the dropdown, editors could inadvertently assign new rules to the old Published version — which will eventually be archived — rather than to the in-progress revision. By suppressing the Published original and surfacing its Draft/Staged replacement, the dropdown guides editors toward the correct target.

3.8.4 Edge Case: Discarded Drafts

If a Draft or Staged regulation is discarded (deleted or reverted), the suppression filter on the original Published record must be automatically lifted. Because the filter is evaluated dynamically (checking whether an active Draft/Staged record references the Published record's [RegulationId] via [OriginRegulationId]), discarding the draft removes the referencing record, and the original Published regulation will naturally reappear in the dropdown with no additional action required.

3.8.5 Scope of Application

This filtering logic applies to the Regulation Parenthetical dropdown in all contexts where the Rule form is rendered. This includes new rule creation, draft editing, and staged editing, not only during the Clone Rule Process.

3.9 Unaffiliated Rule Absorption During Clone Regulations Process

3.9.1 Trigger Condition

When the Clone Regulations Process (Section 3.2) is initiated for a Published regulation, the system must check whether any Unaffiliated FishingRule records in Draft or Staged status are currently associated with that Published regulation (i.e., the rule's [RegulationId] points to the Published regulation being cloned).

3.9.2 Required Behavior

If one or more Unaffiliated Draft or Staged rules are found, the system must perform the following additional steps as part of the same atomic clone transaction:

Re-link each Unaffiliated Rule by updating its [RegulationId] to point to the newly created Draft regulation rather than the original Published regulation If the Unaffiliated Rule's status is Staged, revert it to Draft, since it is now part of a regulation-level draft that has not yet been staged Preserve all other field values on the Unaffiliated Rule, including [OriginRuleId], edit history, and audit columns Clear [StagedBy] and [StagedDate] on any rule reverted from Staged to Draft

3.9.3 Rationale

Without this step, an Unaffiliated Rule would remain linked to the original Published regulation, which will be archived when the new Draft regulation is eventually published. This would leave the Unaffiliated Rule pointing to an Archived parent — effectively orphaning it. Re-linking at clone time ensures all in-progress work against a regulation is consolidated into a single draft revision, preventing orphaned records and conflicting parallel edits.

3.9.4 Notification

After the clone transaction completes, the system must notify the user who initiated the Clone Regulations Process that existing in-progress rules were absorbed into the new draft. The notification must include:

· The number of Unaffiliated Rules that were re-linked

· The FishingRuleId and Rule Type of each affected rule

· An indication of any rules that were reverted from Staged to Draft

The system should also notify the original editor(s) of the absorbed rules (identified via [UpdatedBy]) that their in-progress rule has been incorporated into a regulation-level draft and is no longer independently promotable.

3.9.5 Impact on Promotion Portal

Once absorbed, the re-linked rules are no longer considered Unaffiliated. They will appear under the parent Draft regulation's row expansion (Section 5.2) in the Drafts tab rather than in the Unaffiliated Rule Drafts section. They follow the regulation-level promotion path from that point forward — they can only be promoted to Staged and Published as part of their parent regulation, not independently.

4. Promotion Portal The Promotion Portal is a dedicated administrative screen within the existing Content Management Site. It provides authorized administrative users with a single location to review, promote, and discard Draft and Staged records. The portal is organized into two primary tabs: Drafts and Staged.

4.1 Access Control

The Promotion Portal is accessible only to users with the Administrative role. Standard editor users do not have access to promotion or publish actions.

4.2 Review and Promotion Controls

The header shall display tabs to navigate between Draft and Staged records. Each tab will also include a section tabs to review either Regulations or Rules:

· Drafts (Default)

· Regulations (default) - Each Regulation row supports expansion of affiliated Rules (see 5.2 Row Expansion – Inline Rule Preview)

· Unaffiliated Rules

· Staged

· Regulations (default) - Each Regulation row supports expansion of affiliated Rules (see 5.2 Row Expansion – Inline Rule Preview)

· Unaffiliated Rules

4.2.1 Drafts Tab

4.2.1.1 Section: Regulation Drafts

Displays a selectable list of all regulation records where status = 'Draft' and [DeletedTime] = NULL.

Columns Displayed

· Source

· Code

· Title

· OriginRegulationId

· Last Modified Date

· Last Modified By

· Actions (use same symbology as Regulations Screen)

· View Record

· Edit Record

· Delete Record Table Controls

· Same sorting and filtering controls as the Rules screen

· Sorted by default on Code, Species Group, and Species in ascending order Bulk Actions Applied to user-selected rows:

· Check box to select and unselect rows

· Select All – Select all rows

· Promote to Staged – Updates selected Regulation records and all associated FishingRule records from Draft to Staged

· Discard – Permanently deletes selected Draft Regulation records and all associated cloned FishingRule records

4.2.1.2 Section: Unaffiliated Rule Drafts

Displays a selectable list of all rule records where status = 'Draft' and [DeletedTime] = NULL and the rule's parent regulation is already Published (i.e. the rule change is not part of a regulation-level draft).

Columns Displayed

· Regulation

· Rule Type

· Species Group

· Species

· OriginRegulationId

· Last Modified Date

· Last Modified By

· Actions (use same symbology as Regulations Screen)

· View Record

· Edit Record

· Delete Record Table Controls

· Same sorting and filtering controls as the Regulations screen

· Sorted by default on Regulation Code in ascending order Bulk Actions Applied to user-selected rows:

· Check box to select and unselect rows

· Select All – Select all rows

· Promote to Staged – Updates Unaffiliated selected Rule records from Draft to Staged.

· Affiliated Rules can only be promoted to Staged through the Regulation Drafts section

· Discard – Permanently deletes selected Draft Rule records

4.2.2 Staged Tab

4.2.2.1 Section: Staged Regulations

Displays a selectable list of all regulation records where status = 'Staged' and [DeletedTime] = NULL.

Columns Displayed Same as Section: Regulations Drafts Table Controls Nearly the same as Section: Regulations Drafts, however Staged records are locked for editing so the ‘Edit’ Action shall net be available Bulk Actions Applied to user-selected rows:

· Check box to select and unselect rows

· Select All – Select all rows

· Publish – Executes the atomic publish transaction (see 3.7.1 Publishing a Staged Regulation) for each selected regulation

· Discard – Reverts selected Staged Regulation records and all associated FishingRule records to status = 'Draft'

4.2.2.2 Section: Unaffiliated Staged Rules

Displays a selectable list of all FishingRules records where status = 'Staged' and [DeletedTime] = NULL.

Columns Displayed Same as Section: Unaffiliated Rule Drafts Table Controls Nearly the same as Section: Rule Drafts, however Staged records are locked for editing so the ‘Edit’ Action shall net be available Bulk Actions Bulk actions (applied to user-selected rows):

· Check box to select and unselect rows

· Select All – Select all rows

· Publish – Executes an atomic publish transaction for each selected Unaffiliated Rule record: the Staged rule is updated to Status = 'Published'; the prior Published rule (identified via [OriginRuleId]) is updated to Status = 'Archived', [DeletedTime] set to current Datetime, and [DeletedBy] set to the user who initiated publication. If no prior Published rule exists (i.e., [OriginRuleId] is NULL), only the status update to 'Published' is performed. If any step in the transaction fails, the entire operation must be rolled back.

· Affiliated Rules can only be Published through the Regulation Staged section

· Discard – Reverts selected Staged Rule records to [Status] = 'Draft'

4.2.3 Archived Tab

The Promotion Portal header tabs shall be updated to include a third primary tab:

· Drafts (Default)

· Staged

· Archived (new) The Archived tab provides administrators with a view of all previously Published records that have been superseded, and serves as the interface for initiating the Rollback Mechanism defined in Section 7.

4.2.3.1 Section: Archived Regulations

Displays a selectable list of all regulation records where [Status] = ‘Archived’ and the record is eligible for rollback (see Section 7.3.1).

Columns Displayed

· Source

· Code

· Title

· OriginRegulationId

· Archived Date ([DeletedTime])

· Archived By ([DeletedBy])

· Actions

· View Record

Row Expansion — Inline Rule Preview Each Archived Regulation row must support inline expansion to reveal affiliated Archived FishingRules, consistent with the pattern defined in Section 5.2. The expanded view must display:

· FishingRuleId

· Rule Type

· Species Group

· Species

· OriginRuleId

· Archived Date ([DeletedTime])

· Actions: View Record Table Controls

· Same sorting and filtering controls as the Regulations Drafts section

· Sorted by default on [DeletedTime] in descending order (most recently archived first) Bulk Actions Applied to user-selected rows:

· Checkbox to select and unselect rows

· Select All — Select all eligible rows

· Rollback — Executes the atomic rollback transaction (Section 7.4) for each selected Archived regulation. Reactivates the Archived regulation and its affiliated rules, archives the successor regulation and its affiliated rules, and re-links any post-publish rules to the reactivated regulation

Archived regulations that are ineligible for rollback (e.g., an active Draft or Staged revision exists for the successor) must appear in the list but with the selection checkbox disabled and a visual indicator (e.g., greyed-out row with tooltip) explaining why rollback is unavailable.

4.2.3.2 Section: Archived Unaffiliated Rules

Displays a selectable list of all Archived FishingRule records that are eligible for rollback (see Section 7.3.2) and whose parent regulation is currently Published (i.e., the rule was archived independently of a regulation-level publish).

Columns Displayed

· Regulation

· Rule Type

· Species Group

· Species

· OriginRuleId

· Archived Date ([DeletedTime])

· Archived By ([DeletedBy])

· Actions

· View Record Table Controls

· Same sorting and filtering controls as the Unaffiliated Rule Drafts section

· Sorted by default on [DeletedTime] in descending order Bulk Actions Applied to user-selected rows:

· Checkbox to select and unselect rows

· Select All — Select all eligible rows

· Rollback — Executes the atomic rollback transaction (Section 7.5) for each selected Archived rule. Reactivates the Archived rule and archives its successor

Archived rules that are ineligible for rollback must appear in the list with the selection checkbox disabled and a tooltip explaining the blocking condition.

4.3 Admin Action Summary

5. UX Requirements The following UX requirements apply to the Promotion Portal to ensure usability, safety, and accountability.

5.1 Confirmation Dialogs

5.1.1 Promotion Dialogs

All bulk actions must be gated behind a confirmation dialog before execution. The dialog must:

· Clearly state the action being taken (e.g., Promote to Staged, Publish, Discard)

· Display the number of Regulation records selected

· Display the number of associated FishingRule records that will be affected (for regulation-level actions)

· Distinguish between reversible actions (Discard Staged → reverts to Draft) and irreversible actions (Discard Draft → permanently deleted)

· Require explicit user confirmation via a labeled button (e.g., Confirm Publish or Confirm Delete) rather than a generic OK

Destructive actions (permanent deletion of Draft records) should use a visually distinct confirmation button — for example, red text or a warning icon — to differentiate from reversible actions.

5.1.2 Rollback Confirmation Dialogs

All rollback actions must be gated behind a confirmation dialog consistent with Section 5.1. The dialog must:

· Clearly state “Rollback” as the action being taken

· Display the number of Archived Regulation records selected

· Display the number of affiliated Archived FishingRule records that will be reactivated

· Display the number of successor records (Regulations and FishingRules) that will be archived as a result

· If post-publish rules exist on any successor regulation, display the count and explain that they will be re-linked to the reactivated regulation

· Require explicit user confirmation via a labeled button (e.g., “Confirm Rollback”)

Rollback is a significant action that reverses a publish transaction. The confirmation dialog should use a visually distinct style — for example, an amber or orange accent — to differentiate it from standard publish or discard actions.

5.2 Row Expansion – Inline Rule Preview

Each Regulation row in both the Drafts and Staged tabs must support inline expansion to reveal associated FishingRules without navigating away from the portal. The expanded view must display:

· FishingRuleId

· Rule Type

· Species Group

· Species

· The rule's current status

· Actions

· View Record

· Edit Record (Draft only)

· Delete Record (Draft only) This allows administrators to inspect what will be promoted or discarded before committing to a bulk action, reducing the risk of unintended changes.

5.3 Badge Counts

Each tab and section within the Promotion Portal must display a live badge count indicating the number of records currently in that state. Counts must update automatically after any action is completed without requiring a full page reload.

5.4 New Audit Columns

All records in the Promotion Portal lists must display user attribution columns drawn from the platform's existing authentication system. The following audit fields are required on both Regulations and FishingRules tables, but will not be displayed by default:

5.5 One-Draft-Per-Regulation Guard

The system must enforce a constraint preventing multiple simultaneous Drafts for the same regulation. If an editor attempts to create a Draft for a regulation that already has an active Draft:

· The system must not create a second Draft

· The user must be routed to the existing Draft with a contextual notification (e.g., 'A draft already exists for Regulation 2.73. You have been redirected to the existing draft.')

5.6 Public API Filtering

The public-facing API must apply the following filter on all queries against the Regulations and FishingRules tables to ensure only current, active content is returned:

status = 'Published' AND [DeletedTime] IS NULL This dual-filter approach provides belt-and-suspenders protection: status = 'Published' ensures only approved records are returned, while [DeletedTime] IS NULL prevents any previously Published record that has been superseded from appearing in results even if a query omits the status filter.

6. Lookup Table Management Lookup tables are shared reference data used by both Draft and Published regulations. Because lookup values are referenced by active Published records, changes to lookup tables must be managed carefully to avoid breaking live content.

6.1 Governed Lookup Tables

The following lookup tables are in scope for promotion workflow governance:

6.2 Recommended Approach

Lookup table records should carry their own status flag (Draft, Staged, Published, Archived) consistent with the regulation lifecycle model. This ensures that:

· New lookup values can be authored without immediately affecting live content

· A lookup value used by a Published regulation cannot be modified or removed without first creating a Draft version

· Lookup value changes follow the same Promotion Portal review and approval flow as regulation changes

· Currently, look-up tables are managed by DTD and can only be updated on Production during weekly scheduled deployment windows that occur once a week. If in the future lookup tables are managed by users, then consider adding the new audit columns proposed in section 5.4 (StagedBy, StagedDate, PublishedBy, PublishedDate) to track when and who initiated the changes

A lookup value that is referenced by a Published regulation must not be Archived or deleted until all referencing Published regulations have been updated and re-published with a replacement value.

7. Rollback Mechanism The Rollback Mechanism provides administrators with the ability to reactivate a previously Archived regulation or rule record, effectively reversing a publish action that was made in error or that needs to be undone. Rollback restores the Archived record to Published status and transitions the record that replaced it to Archived, ensuring only one Published version of a regulation or rule exists at any time.

7.3 Rollback Eligibility

An Archived record is eligible for rollback only when all of the following conditions are met:

7.3.1 Regulation Rollback Eligibility

· The Archived regulation’s [RegulationId] is referenced as the [OriginRegulationId] of a currently Published regulation (i.e., a direct successor exists)

· No Draft or Staged record exists for the same regulation lineage (i.e., no active revision is in progress for the successor). If an in-progress revision exists, it must be discarded before rollback can proceed

· The Archived regulation’s [DeletedTime] is not NULL (confirming it was archived through the publish process, not manually deleted)

7.3.2 Unaffiliated Rule Rollback Eligibility

· The Archived rule’s [FishingRuleId] is referenced as the [OriginRuleId] of a currently Published rule

· No Draft or Staged record exists for the same rule lineage

· The Archived rule’s [DeletedTime] is not NULL If a Draft or Staged revision is in progress for the successor record, the system must block rollback and display a message indicating the in-progress revision must be resolved (discarded or published) before rollback is available.

7.4 Regulation Rollback — Atomic Transaction

When an administrator initiates a rollback on an Archived regulation, the following operations must execute as a single atomic transaction:

7.4.1 Reactivate the Archived Regulation

· Set [Status] = ‘Published’ on the Archived regulation record

· Set [DeletedTime] = NULL and [DeletedBy] = NULL

· Set [RestoredBy] to the user who initiated the rollback

· Set [RestoredTime] to the current UTC datetime

7.4.2 Reactivate Archived Affiliated Rules

For all FishingRule records that were archived in the same publish transaction as the regulation (identified directly via [OriginRuleId] referenced by the successor regulation's affiliated rules):

· Set [Status] = ‘Published’

· Set [DeletedTime] = NULL and [DeletedBy] = NULL

· Set [RestoredBy] and [RestoredTime] to the initiating user and current UTC datetime

7.4.3 Archive the Successor Regulation

The currently Published regulation that replaced the Archived record (the record whose [OriginRegulationId] points to the Archived regulation’s [RegulationId]):

· Set [Status] = ‘Archived’

· Set [DeletedTime] to the current UTC datetime

· Set [DeletedBy] to the user who initiated the rollback

7.4.4 Archive the Successor’s Affiliated Rules

All Published FishingRules affiliated with the successor regulation:

· Set [Status] = ‘Archived’

· Set [DeletedTime] to the current UTC datetime

· Set [DeletedBy] to the user who initiated the rollback

7.4.5 Handle Post-Publish Rules on the Successor

After a regulation is published, new rules may have been created directly against it that did not exist in the original Archived version. These are FishingRules affiliated with the successor regulation where [OriginRuleId] IS NULL (i.e., they are net-new rules, not clones of rules from the Archived regulation).

These post-publish rules must not be silently deleted or archived during rollback. The system must handle them as follows:

· Re-link each post-publish rule by updating its [RegulationId] to point to the reactivated (formerly Archived) regulation

· Retain the rule’s current [Status]. If the rule is Published, it remains Published under the reactivated regulation. If it is Draft or Staged, it retains that status

· Do not modify any other field values on these rules Rationale: Post-publish rules represent new regulatory content that was authored after the successor was published. Discarding or archiving them would result in data loss. Re-linking preserves the work while ensuring the rules remain associated with an active Published regulation after rollback completes.

7.4.6 Transaction Failure

If any step in the rollback transaction fails, the entire operation must be rolled back. No partial state changes may be persisted.

7.5 Unaffiliated Rule Rollback — Atomic Transaction

When an administrator initiates a rollback on an Archived Unaffiliated Rule, the following operations must execute as a single atomic transaction:

· Set [Status] = ‘Published’ on the Archived rule record

· Set [DeletedTime] = NULL, [DeletedBy] = NULL

· Set [RestoredBy] and [RestoredTime] to the initiating user and current UTC datetime

· The currently Published rule that replaced it (identified via [OriginRuleId] on the successor) is updated: [Status] = ‘Archived’, [DeletedTime] set to current datetime, [DeletedBy] set to the initiating user If the rollback transaction fails, the entire operation must be rolled back.

7.6 Required Schema Additions

The following columns are required on both the Regulations and FishingRules tables to support rollback audit tracking:

These fields are populated only during the rollback transaction and are NULL under normal workflow operations.

8 Additional Enhancements

8.2 Diff/Comparison View

Before publishing, showing a side-by-side diff between the Staged draft and the current Published version would help reviewers quickly identify exactly what changed, especially for records with dense or technical content.

Appendix A – Glossary

Record Promotion Workflow Single-Database Status Lifecycle Model

Functional Requirements Document Page 1 Appendix B – Process Flow Diagram image1.png image2.svg