How we build software

From idea to live app, one safe step at a time

Our developers work alongside AI agents to build the Energy Matrix Group app. This guide shows, in plain English, how every change is checked, rehearsed, approved and reversible before it reaches the people who use it.

Start the guided tour Read the two-minute briefing

No technical background needed. Developers: the playbook is in chapter 07.

Choose where to start

Board and executives The two-minute briefing

What we control, what could go wrong, and what stops it.

Read the briefing2 min read Everyone The guided tour

Watch a change travel from an idea to the live app, then see what happens when something goes wrong.

Take the tour3 short stories Developers The developer playbook

The branching model, the commands and the rules every change follows.

Open the playbookStep by step

01The briefingBoard and executives

What leaders need to know

Each of our developers works with AI agents, which helps us move faster. The way we build is designed so that speed never comes at the expense of control.

5safety checks on every change
2complete copies of the app: a rehearsal and the real thing
100%of changes reviewed by a person before they're merged
100%of app data stored in Australia

What could go wrong, and what stops it

The riskWhat stops it

A mistake reaches the people using the app

Every change passes automated tests and a teammate's review, then a full rehearsal in the test app, before anyone can approve it for release.

Someone changes the live app without permission

The live app's code is protected. It only changes through an approved merge, and nobody, person or AI, can go around it.

An AI agent gets something wrong

AI agents only work in private feature branches. A person reviews every change they draft, and people approve everything that goes live.

A release causes a problem

Every release keeps the previous version as a save point. We can switch back quickly, then fix the problem through the normal route.

Our data leaves Australia

Databases and files for both copies of the app are stored in Microsoft Azure data centres in Australia.

People keep access after they leave

People sign in with their Microsoft work account. When a work account is switched off, access to the app ends too.

In short

AI helps us move faster. People stay in charge. Every change is checked, rehearsed, approved and reversible.

02The guided tourEveryone

Follow a change from idea to live app

Choose a story and press play, or select any part of the map to see what it does. Work moves left to right. The top row is where we rehearse; the bottom row is the live app people use.

Stories

Swipe sideways to see the whole map. It follows the story as it moves.

Overview

The whole system on one page

Tip: use ← and → to move between steps while the map is on screen.

03Inside the appEveryone

What happens when someone uses the app

The app has three layers, a bit like a shop: the shop front you walk into, the staff behind the counter, and the storeroom out the back.

You sign in and ask for last month's usage report.You use your normal Microsoft work login, so there's no extra password.
The server checks you're allowed to see it.It applies the business rules, then asks storage for what it needs.
Storage hands back the pieces.The numbers come from the SQL database; the PDF comes from file storage.
The report appears on your screen.All of this happens in the live app, the bottom row of the map.

In tech terms: a web interface secured with single sign-on, a Node.js server, Azure SQL Database and Azure Blob Storage.

04Two copiesEveryone

Two complete copies: a rehearsal and the real show

We run two separate copies of the app, each with its own website, server and databases. Every change plays in the rehearsal before it reaches the real show.

Question	Test appthe rehearsal, top row of the map	Live appthe real show, bottom row of the map
Who uses it	Our build team	Everyone who uses the app
What changes it	Any reviewed change merged into the test branch, released automatically	Only work that passed in test and was approved into main, released automatically
Its data	A copy of live data, refreshed every month	The real, up-to-date records and files
If something goes wrong	Only the team notices. We fix it and try again.	We switch back to the last save point, then fix it in test.
In tech terms	Render test services, test Azure SQL Database and Blob Storage	Render production services, production Azure SQL Database and Blob Storage

Why copy live data every month?

A rehearsal with old data slowly stops behaving like the real thing. A fresh monthly copy keeps the test app realistic, so problems show up there rather than in front of our users. Because test holds a copy of real data, it's protected by the same work sign-in and kept in Australia too.

05People and AIEveryone

AI agents propose. People decide.

Every piece of work is done by a developer paired with an AI agent. The agent is fast and tireless. The developer brings judgement, context and accountability.

What AI agents do

Draft code from a clearly written task
Write the automated tests that prove the code works
Explain existing code and suggest fixes
Take on repetitive work quickly, so people can focus on judgement

What people always do

Decide what to build, and why
Review every change before it's merged
Approve what goes live
Own the outcome when something needs fixing

AI agents work only inside feature branches. Nothing they write reaches the live app without a person reviewing and approving it.

06Safety checksEveryone

Five checks stand between an idea and our users

Each check works like a circuit breaker. If something isn't right, it trips, and the change goes no further. They happen in this order, every time.

CHECK 01
Automated tests
Run on every change. A failing test stops the change straight away.
CHECK 02
Peer review
A teammate reads and approves every change before it joins the test branch.
CHECK 03
Rehearsal in the test app
The team tries the feature in a full copy of the app, with realistic data.
CHECK 04
Approval to go live
Main is protected. Nobody can change the live code without an approved merge.
CHECK 05
A save point on every release
The previous version is kept ready, so we can switch back quickly.

Always on

Microsoft work sign-inPeople use the work login they already have. When a work account is switched off, access to the app ends too.

Data kept in AustraliaDatabases and files for both copies are stored in Microsoft Azure data centres in Australia.

07The developer playbookDevelopers

One route for every change, step by step

Whether a person or an AI agent drafted it, every change follows this route. Here's the branching model, the commands, and the rules we don't bend.

The branching model

Start from a clear task

Every piece of work begins as a small task on the task board. It spells out what "done" looks like, so you and your AI agent build the same thing.
2
Branch from test

Create a feature branch for the task. It's your private working copy: nothing you do here can affect the team or the live app.
Terminal
```
$ git switch test
$ git pull
$ git switch -c feature/monthly-usage-report
```
Branch names in these examples are illustrative.
3
Build it with your AI agent

The agent drafts the code and the unit tests that prove it works. You steer, review every line and correct it: you're accountable for the result. Commit as you go and push your branch.
Terminal
```
$ git add .
$ git commit -m "Add monthly usage report"
$ git push -u origin feature/monthly-usage-report
```
4
Open a pull request into test

Unit tests run on the pull request, and a teammate reviews it. It can only merge once the tests pass and the teammate approves.
Terminal
```
$ gh pr create --base test --fill
```
Prefer the website? Open the pull request on GitHub and choose test as the base branch.
Merge, and the test app updates itself

Merging into test triggers an automatic deployment on Render. Try your change in the test app, which runs on a monthly copy of live data.
6
Release through an approved pull request into main

When the work is ready to go live, open a pull request from test into main. Branch protection means it can't merge without approval. Merging it triggers the production deployment.
Terminal
```
$ gh pr create --base main --head test
```
Live, with a save point

Render keeps the previous deploy ready. If something goes wrong, roll the live app back to it in Render, then fix forward on a new branch from test, through this same route. There are no shortcuts.

The rules we don't bend

main is protected. It only changes through an approved pull request from test.
Branch from test, and merge back into test through a pull request.
Every pull request needs passing tests and a teammate's approval.
Write the tests with the code. They run on every change.
AI agents work only in feature branches. You review everything they write, and you own the result.
Fixes follow the same route. There are no shortcuts to live.
Test data is a copy of real data. Treat it with the same care.

Before you merge

Ready for `test` when

It does what the task says "done" looks like
Unit tests are written and passing
You've reviewed everything your AI agent drafted
A teammate has reviewed and approved it

Ready for `main` when

The team has tried it in the test app
The pull request from test into main is approved

08The databasesDevelopers

Two databases, one picture each

Our systems keep their records in SQL databases. Choose one, then select a table to see what it holds and how it connects to the rest.

In tech terms: both are T-SQL databases in schema dbo, with GUID keys generated by NEWSEQUENTIALID() and UTC timestamps.

EMG Company App Core

Show

Swipe sideways to see the whole diagram.

Many rows can point at one row
Exactly one
Optional: the column can be empty
Cascade: deleting the parent deletes these rows
Plain reference, no foreign key
PKFKUQREFPrimary key, foreign key, unique, plain reference
? after a type can be empty

Overview

Tip: select a table, then use the buttons in the panel to move to the tables it connects to. Press Esc to clear.

What the database enforces for you

Roles stay inside their applet

Roles and permissions are linked by pairs of keys, (AppletId, id). The database itself refuses to give a role a permission from another applet, or to give a person a role from the wrong applet.

One role per person per applet

UserAppletAccess is keyed on (UserId, AppletId), so nobody can hold two roles in the same applet. IsActive switches access off without losing the record. A person’s own applet order is tied to the same row, so it can only list applets they have been given access to.

Deleting a user clears their own data

Their settings, applet access, applet order and applet preferences go with them. Rows that record history do not cascade, so a user who created, granted or audited anything is switched off with IsActive instead.

The audit trail outlives people

AuditLog.TargetUserId has no foreign key on purpose, so an entry stays even after the person it concerns is deleted.

Preferences are always valid JSON

A check constraint on UserAppletPreferences.Prefs rejects anything ISJSON() says is not JSON. The default is an empty object.

Common lookups are indexed

Six indexes cover who has access to an applet or role, which roles hold a permission, which pages need a permission, and recent audit entries by actor or target.

See the full SQL script for EMG Company App Core

T-SQL

CREATE TABLE dbo.Users (
    UserId             UNIQUEIDENTIFIER NOT NULL CONSTRAINT DF_Users_UserId DEFAULT NEWSEQUENTIALID(),
    Username           NVARCHAR(100)    NOT NULL,
    Email              NVARCHAR(256)    NOT NULL,
    PasswordHash       NVARCHAR(255)    NOT NULL,  -- bcrypt / Argon2 / PBKDF2 string, never plain text
    MustChangePassword BIT              NOT NULL CONSTRAINT DF_Users_MustChange DEFAULT 1,
    IsAdmin            BIT              NOT NULL CONSTRAINT DF_Users_IsAdmin DEFAULT 0,
    IsActive           BIT              NOT NULL CONSTRAINT DF_Users_IsActive DEFAULT 1,
    FailedLoginCount   INT              NOT NULL CONSTRAINT DF_Users_Failed DEFAULT 0,
    LockoutUntil       DATETIME2(0)     NULL,
    CreatedBy          UNIQUEIDENTIFIER NULL,
    CreatedAt          DATETIME2(0)     NOT NULL CONSTRAINT DF_Users_CreatedAt DEFAULT SYSUTCDATETIME(),
    LastLoginAt        DATETIME2(0)     NULL,
    CONSTRAINT PK_Users PRIMARY KEY (UserId),
    CONSTRAINT UQ_Users_Username UNIQUE (Username),
    CONSTRAINT UQ_Users_Email    UNIQUE (Email),
    CONSTRAINT FK_Users_CreatedBy FOREIGN KEY (CreatedBy) REFERENCES dbo.Users (UserId)
);
GO

CREATE TABLE dbo.AppSettings (
    SettingKey   NVARCHAR(100)    NOT NULL,
    SettingValue NVARCHAR(MAX)    NULL,
    UpdatedBy    UNIQUEIDENTIFIER NULL,
    UpdatedAt    DATETIME2(0)     NOT NULL CONSTRAINT DF_AppSettings_UpdatedAt DEFAULT SYSUTCDATETIME(),
    CONSTRAINT PK_AppSettings PRIMARY KEY (SettingKey),
    CONSTRAINT FK_AppSettings_UpdatedBy FOREIGN KEY (UpdatedBy) REFERENCES dbo.Users (UserId)
);
GO

CREATE TABLE dbo.UserSettings (
    UserId   UNIQUEIDENTIFIER NOT NULL,
    Theme    NVARCHAR(20)     NULL,   -- NULL = use the app-wide default
    Language NVARCHAR(10)     NULL,
    CONSTRAINT PK_UserSettings PRIMARY KEY (UserId),
    CONSTRAINT FK_UserSettings_Users FOREIGN KEY (UserId) REFERENCES dbo.Users (UserId) ON DELETE CASCADE
);
GO

/* ---------- Applet registry ---------- */

CREATE TABLE dbo.Applets (
    AppletId  UNIQUEIDENTIFIER NOT NULL CONSTRAINT DF_Applets_AppletId DEFAULT NEWSEQUENTIALID(),
    Code      NVARCHAR(50)     NOT NULL,   -- e.g. 'applet1'
    Name      NVARCHAR(100)    NOT NULL,
    IsEnabled BIT              NOT NULL CONSTRAINT DF_Applets_IsEnabled DEFAULT 1,
    URL       NVARCHAR(500)    NULL,       -- where the applet is hosted, e.g. 'https://applet1.yourcompany.com'
    CONSTRAINT PK_Applets PRIMARY KEY (AppletId),
    CONSTRAINT UQ_Applets_Code UNIQUE (Code)
);
GO

/* ---------- Applet roles and permissions ---------- */

CREATE TABLE dbo.AppletRoles (
    AppletRoleId UNIQUEIDENTIFIER NOT NULL CONSTRAINT DF_AppletRoles_Id DEFAULT NEWSEQUENTIALID(),
    AppletId     UNIQUEIDENTIFIER NOT NULL,
    Name         NVARCHAR(100)    NOT NULL,
    CONSTRAINT PK_AppletRoles PRIMARY KEY (AppletRoleId),
    CONSTRAINT FK_AppletRoles_Applets FOREIGN KEY (AppletId) REFERENCES dbo.Applets (AppletId),
    CONSTRAINT UQ_AppletRoles_Name UNIQUE (AppletId, Name),
    CONSTRAINT UQ_AppletRoles_Applet_Role UNIQUE (AppletId, AppletRoleId)  -- target for composite FKs
);
GO

CREATE TABLE dbo.AppletPermissions (
    PermissionId UNIQUEIDENTIFIER NOT NULL CONSTRAINT DF_AppletPermissions_Id DEFAULT NEWSEQUENTIALID(),
    AppletId     UNIQUEIDENTIFIER NOT NULL,
    Code         NVARCHAR(100)    NOT NULL,   -- e.g. 'reports.view'
    CONSTRAINT PK_AppletPermissions PRIMARY KEY (PermissionId),
    CONSTRAINT FK_AppletPermissions_Applets FOREIGN KEY (AppletId) REFERENCES dbo.Applets (AppletId),
    CONSTRAINT UQ_AppletPermissions_Code UNIQUE (AppletId, Code),
    CONSTRAINT UQ_AppletPermissions_Applet_Perm UNIQUE (AppletId, PermissionId)  -- target for composite FKs
);
GO

-- AppletId is repeated here so both composite FKs force role and permission
-- to belong to the same applet.
CREATE TABLE dbo.AppletRolePermissions (
    AppletRoleId UNIQUEIDENTIFIER NOT NULL,
    PermissionId UNIQUEIDENTIFIER NOT NULL,
    AppletId     UNIQUEIDENTIFIER NOT NULL,
    CONSTRAINT PK_AppletRolePermissions PRIMARY KEY (AppletRoleId, PermissionId),
    CONSTRAINT FK_ARP_Role FOREIGN KEY (AppletId, AppletRoleId)
        REFERENCES dbo.AppletRoles (AppletId, AppletRoleId) ON DELETE CASCADE,
    CONSTRAINT FK_ARP_Permission FOREIGN KEY (AppletId, PermissionId)
        REFERENCES dbo.AppletPermissions (AppletId, PermissionId)
);
GO

/* ---------- Which applets a user can open (set by admin) ---------- */

CREATE TABLE dbo.UserAppletAccess (
    UserId       UNIQUEIDENTIFIER NOT NULL,
    AppletId     UNIQUEIDENTIFIER NOT NULL,
    AppletRoleId UNIQUEIDENTIFIER NOT NULL,
    IsActive     BIT              NOT NULL CONSTRAINT DF_UAA_IsActive DEFAULT 1,
    GrantedBy    UNIQUEIDENTIFIER NULL,
    GrantedAt    DATETIME2(0)     NOT NULL CONSTRAINT DF_UAA_GrantedAt DEFAULT SYSUTCDATETIME(),
    CONSTRAINT PK_UserAppletAccess PRIMARY KEY (UserId, AppletId),  -- one role per user per applet
    CONSTRAINT FK_UAA_Users FOREIGN KEY (UserId) REFERENCES dbo.Users (UserId) ON DELETE CASCADE,
    CONSTRAINT FK_UAA_Role FOREIGN KEY (AppletId, AppletRoleId)
        REFERENCES dbo.AppletRoles (AppletId, AppletRoleId),         -- role must belong to the same applet
    CONSTRAINT FK_UAA_GrantedBy FOREIGN KEY (GrantedBy) REFERENCES dbo.Users (UserId)
);
GO

/* ---------- Page access ---------- */

CREATE TABLE dbo.AppletPages (
    PageId       UNIQUEIDENTIFIER NOT NULL CONSTRAINT DF_AppletPages_Id DEFAULT NEWSEQUENTIALID(),
    AppletId     UNIQUEIDENTIFIER NOT NULL,
    Route        NVARCHAR(200)    NOT NULL,
    PermissionId UNIQUEIDENTIFIER NULL,   -- NULL = any user with applet access can open it
    CONSTRAINT PK_AppletPages PRIMARY KEY (PageId),
    CONSTRAINT FK_AppletPages_Applets FOREIGN KEY (AppletId) REFERENCES dbo.Applets (AppletId),
    CONSTRAINT FK_AppletPages_Permission FOREIGN KEY (AppletId, PermissionId)
        REFERENCES dbo.AppletPermissions (AppletId, PermissionId),
    CONSTRAINT UQ_AppletPages_Route UNIQUE (AppletId, Route)
);
GO

/* ---------- Per-user, per-applet preferences ---------- */

CREATE TABLE dbo.UserAppletPreferences (
    UserId    UNIQUEIDENTIFIER NOT NULL,
    AppletId  UNIQUEIDENTIFIER NOT NULL,
    Prefs     NVARCHAR(MAX)    NOT NULL CONSTRAINT DF_UAP_Prefs DEFAULT N'{}',
    UpdatedAt DATETIME2(0)     NOT NULL CONSTRAINT DF_UAP_UpdatedAt DEFAULT SYSUTCDATETIME(),
    CONSTRAINT PK_UserAppletPreferences PRIMARY KEY (UserId, AppletId),
    CONSTRAINT FK_UAP_Users FOREIGN KEY (UserId) REFERENCES dbo.Users (UserId) ON DELETE CASCADE,
    CONSTRAINT FK_UAP_Applets FOREIGN KEY (AppletId) REFERENCES dbo.Applets (AppletId),
    CONSTRAINT CK_UAP_PrefsJson CHECK (ISJSON(Prefs) = 1)
);
GO

/* ---------- Per-user sort order ---------- */

CREATE TABLE dbo.[emg-applet-sort-order] (
    UserId    UNIQUEIDENTIFIER NOT NULL,
    AppletId  UNIQUEIDENTIFIER NOT NULL,
    SortOrder INT              NOT NULL CONSTRAINT DF_EmgAppletSortOrder_SortOrder DEFAULT 0,
    UpdatedAt DATETIME2(0)     NOT NULL CONSTRAINT DF_EmgAppletSortOrder_UpdatedAt DEFAULT SYSUTCDATETIME(),
    CONSTRAINT [PK_emg-applet-sort-order] PRIMARY KEY (UserId, AppletId),
    -- Only applets the user has access to can be ordered. If access is removed,
    -- the sort row is removed automatically.
    CONSTRAINT [FK_emg-applet-sort-order_Access] FOREIGN KEY (UserId, AppletId)
        REFERENCES dbo.UserAppletAccess (UserId, AppletId) ON DELETE CASCADE
);
GO

/* ---------- Audit ---------- */

CREATE TABLE dbo.AuditLog (
    AuditId      UNIQUEIDENTIFIER NOT NULL CONSTRAINT DF_AuditLog_Id DEFAULT NEWSEQUENTIALID(),
    ActorUserId  UNIQUEIDENTIFIER NOT NULL,
    TargetUserId UNIQUEIDENTIFIER NULL,   -- no FK: audit rows must outlive deleted users
    AppletId     UNIQUEIDENTIFIER NULL,
    Action       NVARCHAR(100)    NOT NULL,  -- 'user.create', 'applet.grant', ...
    Details      NVARCHAR(MAX)    NULL,
    CreatedAt    DATETIME2(0)     NOT NULL CONSTRAINT DF_AuditLog_CreatedAt DEFAULT SYSUTCDATETIME(),
    CONSTRAINT PK_AuditLog PRIMARY KEY (AuditId),
    CONSTRAINT FK_AuditLog_Actor FOREIGN KEY (ActorUserId) REFERENCES dbo.Users (UserId)
);
GO

/* ---------- Indexes ---------- */

CREATE INDEX IX_UserAppletAccess_Applet ON dbo.UserAppletAccess (AppletId, UserId);
CREATE INDEX IX_UserAppletAccess_Role   ON dbo.UserAppletAccess (AppletId, AppletRoleId);
CREATE INDEX IX_AppletRolePerm_Perm     ON dbo.AppletRolePermissions (AppletId, PermissionId);
CREATE INDEX IX_AppletPages_Permission  ON dbo.AppletPages (AppletId, PermissionId);
CREATE INDEX IX_AuditLog_Actor          ON dbo.AuditLog (ActorUserId, CreatedAt DESC);
CREATE INDEX IX_AuditLog_Target         ON dbo.AuditLog (TargetUserId, CreatedAt DESC);
GO

EMG Ticketing System

Show

Swipe sideways to see the whole diagram.

Many rows can point at one row
Exactly one
Optional: the column can be empty
Plain reference, no foreign key
PKFKUQREFPrimary key, foreign key, unique, plain reference
? after a type can be empty

Overview

Tip: select a table, then use the buttons in the panel to move to the tables it connects to. Press Esc to clear.

What the database enforces for you

Status and priority are checked

CHECK constraints on Tickets allow only Low, Medium, High or Urgent for Priority, and Open, In Progress, Closed, On Hold or Resolved for Status.

Nearly every link is optional

Every foreign key can be empty except TicketReassignments.TicketId, so a reassignment always belongs to a ticket.

People are plain IDs

RequesterId, AssignedPersonId and CommentedById hold a person’s GUID but are not foreign keys, so the database does not check them.

Category is stored as text

Tickets.Category holds the category name, not its key, so the database cannot tell whether a ticket’s category still exists or has been renamed.

Audit entries can point anywhere

AuditLogs records an EntityType and EntityId for any table, so neither column is a foreign key.

Common filters are indexed

Nine indexes cover ticket status, requester, assigned person and assigned team, comments and reassignments by ticket, subcategories by category, and audit entries by date and by entity.

See the full SQL script for EMG Ticketing System

T-SQL

/* =====================================================================
   Help Desk Ticket System - SQL Server (T-SQL) equivalent of the
   Dataverse tables used by the app (emg_* tables in src/generated).

   Naming:   PascalCase for tables, columns, constraints and indexes.
             Dataverse emg_/cr005_ prefixes dropped, plural table names.
             Lookup columns end in Id.  Dataverse primary GUID -> <Singular>Id.
   Types:    Text -> NVARCHAR(n) using the Dataverse max length
             Memo -> NVARCHAR(MAX)      Yes/No -> BIT
             DateTime -> DATETIME2(0), stored in UTC
             Choice (option set) -> NVARCHAR + CHECK using the Dataverse labels
   Omitted:  Dataverse system plumbing (ownerid, owningbusinessunit, statecode,
             statuscode, versionnumber, importsequencenumber, timezone cols,
             createdby/modifiedby).  RowCreatedOn / RowModifiedOn kept.
   ===================================================================== */

-- CREATE DATABASE HelpDesk;  GO
-- USE HelpDesk;              GO

/* ---------------------------------------------------------------------
   Teams  (emg_teams)
   --------------------------------------------------------------------- */
CREATE TABLE dbo.Teams (
    TeamId             UNIQUEIDENTIFIER NOT NULL CONSTRAINT DF_Teams_Id        DEFAULT NEWSEQUENTIALID(),
    Name               NVARCHAR(850)    NOT NULL,
    Description        NVARCHAR(100)    NULL,
    Email              NVARCHAR(100)    NULL,
    Active             BIT              NULL     CONSTRAINT DF_Teams_Active    DEFAULT 1,
    IsExternalParty    BIT              NULL     CONSTRAINT DF_Teams_External  DEFAULT 0,
    RowCreatedOn       DATETIME2(0)     NOT NULL CONSTRAINT DF_Teams_Created   DEFAULT SYSUTCDATETIME(),
    RowModifiedOn      DATETIME2(0)     NOT NULL CONSTRAINT DF_Teams_Modified  DEFAULT SYSUTCDATETIME(),
    CONSTRAINT PK_Teams PRIMARY KEY (TeamId)
);
GO

/* ---------------------------------------------------------------------
   TeamMembers  (emg_teammembers)
   NOTE: Dataverse has no link between teams and team members.
   --------------------------------------------------------------------- */
CREATE TABLE dbo.TeamMembers (
    TeamMemberId       UNIQUEIDENTIFIER NOT NULL CONSTRAINT DF_TeamMembers_Id       DEFAULT NEWSEQUENTIALID(),
    Name               NVARCHAR(850)    NOT NULL,   -- emg_teammembername
    Email              NVARCHAR(100)    NULL,
    RowCreatedOn       DATETIME2(0)     NOT NULL CONSTRAINT DF_TeamMembers_Created  DEFAULT SYSUTCDATETIME(),
    RowModifiedOn      DATETIME2(0)     NOT NULL CONSTRAINT DF_TeamMembers_Modified DEFAULT SYSUTCDATETIME(),
    CONSTRAINT PK_TeamMembers PRIMARY KEY (TeamMemberId)
);
GO

/* ---------------------------------------------------------------------
   Categories  (emg_categories)
   --------------------------------------------------------------------- */
CREATE TABLE dbo.Categories (
    CategoryId              UNIQUEIDENTIFIER NOT NULL CONSTRAINT DF_Categories_Id       DEFAULT NEWSEQUENTIALID(),
    Name                    NVARCHAR(850)    NOT NULL,
    Description             NVARCHAR(100)    NULL,
    Active                  BIT              NULL     CONSTRAINT DF_Categories_Active   DEFAULT 1,
    DefaultSlaHours         INT              NULL,
    DefaultAssigneeTeamId   UNIQUEIDENTIFIER NULL,
    RowCreatedOn            DATETIME2(0)     NOT NULL CONSTRAINT DF_Categories_Created  DEFAULT SYSUTCDATETIME(),
    RowModifiedOn           DATETIME2(0)     NOT NULL CONSTRAINT DF_Categories_Modified DEFAULT SYSUTCDATETIME(),
    CONSTRAINT PK_Categories PRIMARY KEY (CategoryId),
    CONSTRAINT FK_Categories_DefaultTeam FOREIGN KEY (DefaultAssigneeTeamId) REFERENCES dbo.Teams (TeamId)
);
GO

/* ---------------------------------------------------------------------
   Subcategories  (emg_subcategories)
   --------------------------------------------------------------------- */
CREATE TABLE dbo.Subcategories (
    SubcategoryId           UNIQUEIDENTIFIER NOT NULL CONSTRAINT DF_Subcategories_Id       DEFAULT NEWSEQUENTIALID(),
    Name                    NVARCHAR(850)    NOT NULL,
    Description             NVARCHAR(100)    NULL,
    Active                  BIT              NULL     CONSTRAINT DF_Subcategories_Active   DEFAULT 1,
    SlaHours                INT              NULL,
    CategoryId              UNIQUEIDENTIFIER NULL,
    DefaultAssigneeTeamId   UNIQUEIDENTIFIER NULL,   -- emg_DefaultAssigneeTeam
    RowCreatedOn            DATETIME2(0)     NOT NULL CONSTRAINT DF_Subcategories_Created  DEFAULT SYSUTCDATETIME(),
    RowModifiedOn           DATETIME2(0)     NOT NULL CONSTRAINT DF_Subcategories_Modified DEFAULT SYSUTCDATETIME(),
    CONSTRAINT PK_Subcategories PRIMARY KEY (SubcategoryId),
    CONSTRAINT FK_Subcategories_Category    FOREIGN KEY (CategoryId)             REFERENCES dbo.Categories (CategoryId),
    CONSTRAINT FK_Subcategories_DefaultTeam FOREIGN KEY (DefaultAssigneeTeamId)  REFERENCES dbo.Teams (TeamId)
);
GO

/* ---------------------------------------------------------------------
   Tickets  (emg_tickets)  - central table
   Category is the category NAME as free text (not a FK) in Dataverse.
   RequesterId AND AssignedPersonId are plain GUID columns (no FK).
   --------------------------------------------------------------------- */
CREATE TABLE dbo.Tickets (
    TicketId            UNIQUEIDENTIFIER NOT NULL CONSTRAINT DF_Tickets_Id       DEFAULT NEWSEQUENTIALID(),
    TicketNumber        NVARCHAR(850)    NOT NULL,   -- e.g. TKT-00001
    Subject             NVARCHAR(100)    NOT NULL,
    Description         NVARCHAR(4000)   NULL,
    Category            NVARCHAR(100)    NULL,
    Priority            NVARCHAR(10)     NULL,       -- 787670000..3
    Status              NVARCHAR(20)     NULL,       -- 787670000..4
    RequesterId         UNIQUEIDENTIFIER NOT NULL,
    AssignedPersonId    UNIQUEIDENTIFIER NULL,
    AssignedTeamId      UNIQUEIDENTIFIER NULL,
    CreatedOn           DATETIME2(0)     NULL,       -- emg_created_on (business date)
    DueDate             DATETIME2(0)     NULL,
    FirstResponseOn     DATETIME2(0)     NULL,
    ResolvedOn          DATETIME2(0)     NULL,
    ResolutionNotes     NVARCHAR(4000)   NULL,
    TicketUrl           NVARCHAR(500)    NULL,
    RowCreatedOn        DATETIME2(0)     NOT NULL CONSTRAINT DF_Tickets_Created  DEFAULT SYSUTCDATETIME(),
    RowModifiedOn       DATETIME2(0)     NOT NULL CONSTRAINT DF_Tickets_Modified DEFAULT SYSUTCDATETIME(),
    CONSTRAINT PK_Tickets PRIMARY KEY (TicketId),
    CONSTRAINT UQ_Tickets_TicketNumber UNIQUE (TicketNumber),
    CONSTRAINT CK_Tickets_Priority CHECK (Priority IN ('Low', 'Medium', 'High', 'Urgent')),
    CONSTRAINT CK_Tickets_Status   CHECK (Status   IN ('Open', 'In Progress', 'Closed', 'On Hold', 'Resolved')),
    CONSTRAINT FK_Tickets_AssignedTeam   FOREIGN KEY (AssignedTeamId)   REFERENCES dbo.Teams (TeamId)
);
GO

/* ---------------------------------------------------------------------
   TicketComments  (emg_ticketcomments)
   --------------------------------------------------------------------- */
CREATE TABLE dbo.TicketComments (
    TicketCommentId    UNIQUEIDENTIFIER NOT NULL CONSTRAINT DF_TicketComments_Id       DEFAULT NEWSEQUENTIALID(),
    CommentSummary     NVARCHAR(850)    NOT NULL,
    Comment            NVARCHAR(100)    NULL,       -- Dataverse caps this at 100 chars
    CommentDate        DATETIME2(0)     NULL,
    InternalOnly       BIT              NULL     CONSTRAINT DF_TicketComments_Internal DEFAULT 0,
    TicketId           UNIQUEIDENTIFIER NULL,
    CommentedById      UNIQUEIDENTIFIER NULL,
    RowCreatedOn       DATETIME2(0)     NOT NULL CONSTRAINT DF_TicketComments_Created  DEFAULT SYSUTCDATETIME(),
    RowModifiedOn      DATETIME2(0)     NOT NULL CONSTRAINT DF_TicketComments_Modified DEFAULT SYSUTCDATETIME(),
    CONSTRAINT PK_TicketComments PRIMARY KEY (TicketCommentId),
    CONSTRAINT FK_TicketComments_Ticket FOREIGN KEY (TicketId) REFERENCES dbo.Tickets (TicketId)
);
GO

/* ---------------------------------------------------------------------
   TicketReassignments  (emg_ticketreassignments)
   TicketId links each reassignment to its ticket (added; not in Dataverse).
   TicketNumber is free text in Dataverse (the app also packs
   "TKT-00001 -> Person (Team)" into it) and is kept as-is, but is NOT the FK.
   AssignedTeamMemberId target is inferred from the column name.
   --------------------------------------------------------------------- */
CREATE TABLE dbo.TicketReassignments (
    TicketReassignmentId   UNIQUEIDENTIFIER NOT NULL CONSTRAINT DF_TicketReassignments_Id       DEFAULT NEWSEQUENTIALID(),
    TicketId               UNIQUEIDENTIFIER NOT NULL,
    TicketNumber           NVARCHAR(850)    NOT NULL,
    ReassignmentDate       DATETIME2(0)     NULL,
    AssignedTeamMemberId   UNIQUEIDENTIFIER NULL,
    RowCreatedOn           DATETIME2(0)     NOT NULL CONSTRAINT DF_TicketReassignments_Created  DEFAULT SYSUTCDATETIME(),
    RowModifiedOn          DATETIME2(0)     NOT NULL CONSTRAINT DF_TicketReassignments_Modified DEFAULT SYSUTCDATETIME(),
    CONSTRAINT PK_TicketReassignments PRIMARY KEY (TicketReassignmentId),
    CONSTRAINT FK_TicketReassignments_Ticket FOREIGN KEY (TicketId)             REFERENCES dbo.Tickets (TicketId),
    CONSTRAINT FK_TicketReassignments_Member FOREIGN KEY (AssignedTeamMemberId) REFERENCES dbo.TeamMembers (TeamMemberId)
);
GO

/* ---------------------------------------------------------------------
   AuditLogs  (emg_auditlogs)
   EntityType + EntityId point at any table, so no FK.
   --------------------------------------------------------------------- */
CREATE TABLE dbo.AuditLogs (
    AuditLogId         UNIQUEIDENTIFIER NOT NULL CONSTRAINT DF_AuditLogs_Id       DEFAULT NEWSEQUENTIALID(),
    Name               NVARCHAR(850)    NOT NULL,
    Action             NVARCHAR(100)    NULL,
    EntityType         NVARCHAR(100)    NULL,
    EntityId           NVARCHAR(100)    NULL,
    Changes            NVARCHAR(MAX)    NULL,
    PerformedOn        DATETIME2(0)     NULL,
    RowCreatedOn       DATETIME2(0)     NOT NULL CONSTRAINT DF_AuditLogs_Created  DEFAULT SYSUTCDATETIME(),
    RowModifiedOn      DATETIME2(0)     NOT NULL CONSTRAINT DF_AuditLogs_Modified DEFAULT SYSUTCDATETIME(),
    CONSTRAINT PK_AuditLogs PRIMARY KEY (AuditLogId)
);
GO

/* ---------------------------------------------------------------------
   Indexes (foreign keys + the app's common filters). Not in Dataverse.
   --------------------------------------------------------------------- */
CREATE INDEX IX_Tickets_Status             ON dbo.Tickets (Status);
CREATE INDEX IX_Tickets_Requester          ON dbo.Tickets (RequesterId);
CREATE INDEX IX_Tickets_AssignedPerson     ON dbo.Tickets (AssignedPersonId);
CREATE INDEX IX_Tickets_AssignedTeam       ON dbo.Tickets (AssignedTeamId);
CREATE INDEX IX_TicketComments_Ticket      ON dbo.TicketComments (TicketId);
CREATE INDEX IX_TicketReassignments_Ticket ON dbo.TicketReassignments (TicketId);
CREATE INDEX IX_Subcategories_Category     ON dbo.Subcategories (CategoryId);
CREATE INDEX IX_AuditLogs_PerformedOn      ON dbo.AuditLogs (PerformedOn DESC);
CREATE INDEX IX_AuditLogs_Entity           ON dbo.AuditLogs (EntityType, EntityId);
GO

09QuestionsEveryone

Questions people often ask

Can an AI agent put something live on its own?

No. AI agents only work inside feature branches. Every change they draft is checked by automated tests and reviewed by a person before it can join the test branch. Going live needs a further approval to merge into main, which is protected.

Do AI agents follow the same rules as people?

Yes. Every change takes the same route, whoever drafted it: automated tests, a teammate's review, a rehearsal in the test app and approval to go live. A person is accountable for everything an AI agent produces.

How do we know a change works before it goes live?

Automated tests prove the code does what it should, and a teammate reviews it. Then the team tries it in the test app, a complete copy of the app running on a recent copy of real data. Only then can it be approved into main.

What happens if a release goes wrong?

Render keeps the previous version ready as a save point, so we can switch the live app back to it quickly. The fix then follows the normal route: its own branch, automated tests, a rehearsal in the test app, and approval to go live.

Why not make changes directly in the live app?

Because one mistake would reach everyone at once. Working this way means every change has been tested and rehearsed before anyone relies on it, and there's always a way back.

Where is our data kept?

In Microsoft Azure data centres in Australia. Records such as accounts and usage figures live in SQL databases. Documents and files live in Blob Storage. The test and live copies are kept separate.

How do people sign in?

With the Microsoft work account they already have, known as single sign-on. There's no separate password to manage, and when someone's work account is switched off, their access to the app ends too.

Does the test app contain real data?

It holds a copy of live data, refreshed monthly, so rehearsals behave like the real thing. That's why the test app has the same sign-in protection and Australian hosting as the live app.

10GlossaryEveryone

Words you'll hear, in plain English

AI agent: Software that writes and changes code when given a task. On our team it works alongside a developer, who reviews everything it produces.
Applet: One of the separate tools inside the app. An admin chooses which applets each person can open, and with which role.
Branch: A separate working copy of the code. Changes on a branch don't affect anyone else until they're merged.
Feature branch: A short-lived branch for one piece of work, such as a new report.
Test branch: The shared branch for finished work that isn't live yet. It feeds the test app.
Main branch: The branch that holds the live app's code. It's protected.
Protected branch: A branch nobody can change directly. Changes need review and approval.
Commit: A saved set of changes to the code, with a short note describing what changed.
Pull request: A request to merge one branch into another. It's where changes are tested, reviewed and approved before they're combined.
Merge: Combining the changes from one branch into another.
Unit test: A small automated check that confirms one piece of code does what it should.
Deployment: Installing a new version of the app so people can use it.
Pipeline: The automatic steps that run when code changes, ending in a deployment.
Rollback: Switching the app back to its previous version, using a save point.
Production: Another word for the live app that people use.
GitHub: The online service where our code is stored, tracked and reviewed.
Render: The cloud service that runs our website and server, and releases new versions automatically.
Node.js: The technology our server, the app's engine, is built with.
SQL database: Stores structured records, like accounts and usage figures, in tables.
Primary key: The column, or columns, that uniquely identify each row in a table.
Foreign key: A column that holds the key of a row in another table. The database uses it to keep the two tables consistent.
Cascade delete: When a row is deleted, the rows that depend on it are deleted automatically.
ERD: Entity relationship diagram: a picture of a database's tables and how they connect.
Blob storage: Stores files, like PDFs and images.
Single sign-on (SSO): Signing in with an existing work account instead of a separate password.
Microsoft Entra ID: Microsoft's service for work accounts and sign-in. It powers our single sign-on.

From idea to live app, one safe step at a time

Choose where to start

What leaders need to know

What could go wrong, and what stops it

Follow a change from idea to live app

The whole system on one page

What happens when someone uses the app

Two complete copies: a rehearsal and the real show

Why copy live data every month?

AI agents propose. People decide.

What AI agents do

What people always do

Five checks stand between an idea and our users

Automated tests

Peer review

Rehearsal in the test app

Approval to go live

A save point on every release

Always on

One route for every change, step by step

The branching model

Start from a clear task

Branch from test

Build it with your AI agent

Open a pull request into test

Merge, and the test app updates itself

Release through an approved pull request into main

Live, with a save point

Two databases, one picture each

EMG Company App Core

What the database enforces for you

Roles stay inside their applet

One role per person per applet

Deleting a user clears their own data

The audit trail outlives people

Preferences are always valid JSON

Common lookups are indexed

EMG Ticketing System

What the database enforces for you

Status and priority are checked

Nearly every link is optional

People are plain IDs

Category is stored as text

Audit entries can point anywhere

Common filters are indexed

Questions people often ask

Words you'll hear, in plain English

Branch from `test`

Open a pull request into `test`

Release through an approved pull request into `main`