This is the second iteration of a former post looking to tighten up a pattern I originally demonstrated with planning application history but here I am trying to implement with asset ownership.
One of the most reliable architectural patterns for handling changing ownership, responsibility, or allocation in business systems is to model changes as dated events rather than continually updating a single field.
This works exceptionally well for:
asset ownership
cost centre allocation
property responsibility
infrastructure stewardship
equipment assignment
operational accountability
The pattern becomes especially powerful when combined with:
UTC timestamps
timezone-aware user input
append-only history
future-dated ownership changes
user audit tracking
soft deletion
The result is a highly scalable and audit-friendly design that works extremely well in PostgreSQL and PHPRunner applications.
The Core Principle
The primary asset table stores the original ownership position:
accountcode_initial
Any subsequent ownership changes are stored separately in a history table.
This means:
the original ownership remains preserved
the complete ownership history is retained
future ownership changes can be scheduled safely
full audit history is maintained
deleted rows can be hidden without destroying history
Recommended Table Structure
Primary Asset Table
CREATE TABLE IF NOT EXISTS public.t001assets
(
gid integer NOT NULL GENERATED ALWAYS AS IDENTITY,
asset_name varchar(200),
accountcode_initial varchar(100) NOT NULL,
softdelete smallint DEFAULT NULL
CHECK (softdelete IS NULL OR softdelete IN (0,1)),
created_at timestamptz DEFAULT now(),
CONSTRAINT pk_t001assets
PRIMARY KEY (gid)
);
Ownership Change History Table
CREATE TABLE IF NOT EXISTS public.t002account_code_changes
(
gid integer NOT NULL GENERATED ALWAYS AS IDENTITY,
pkidt001 integer NOT NULL,
input_time timestamp without time zone NOT NULL,
timezone text NOT NULL,
utc_datetime timestamptz NOT NULL,
new_owner_accountcode varchar(100) NOT NULL,
change_notes text,
user_creator varchar(100) NOT NULL,
user_editor varchar(100),
softdelete smallint DEFAULT NULL
CHECK (softdelete IS NULL OR softdelete IN (0,1)),
created_at timestamptz DEFAULT now(),
updated_at timestamptz DEFAULT now(),
CONSTRAINT pk_t002account_code_changes
PRIMARY KEY (gid),
CONSTRAINT fk_t002account_code_changes_t001
FOREIGN KEY (pkidt001)
REFERENCES public.t001assets (gid)
);
Why This Pattern Works Well
The asset table contains the original ownership:
accountcode_initial
The history table stores only subsequent changes.
This creates:
immutable ownership history
minimal duplication
future scheduling support
audit-friendly architecture
simplified reporting
operational rollback capability
The system becomes event-driven rather than overwrite-driven.
The Soft Delete Pattern
A particularly useful refinement is adding:
softdelete
to both tables.
Recommended values:
Value Meaning
NULL Active
0 Active
1 Deleted / ignored
This provides several advantages over hard deletion:
historical integrity preserved
accidental deletion recovery
simpler auditing
safer integrations
reduced referential integrity problems
The recommended filtering pattern is:
COALESCE(softdelete,0) <> 1
This treats:
NULL as active
0 as active
1 as deleted
The Timezone Pattern
The ownership change table uses three separate fields:
Field Purpose
input_time What the user entered
timezone Intended timezone
utc_datetime Canonical UTC value
Example:
Field Value
input_time 2026-05-23 10:00
timezone Europe/London
utc_datetime 2026-05-23 09:00:00+00
This preserves both:
user intent
globally consistent ordering
Recommended Timezone Values
Always use official IANA timezone names.
Recommended examples:
Display Name Stored Value
UTC UTC
United Kingdom Europe/London
Central Europe Europe/Paris
Eastern US America/New_York
Central US America/Chicago
Pacific US America/Los_Angeles
Dubai Asia/Dubai
India Asia/Kolkata
Japan Asia/Tokyo
Sydney Australia/Sydney
Avoid ambiguous abbreviations such as:
BST
GMT
EST
PST
because daylight savings behaviour becomes unreliable.
Automatic UTC Calculation
Users should never manually edit:
utc_datetime
It should always be automatically derived.
Trigger function
CREATE OR REPLACE FUNCTION public.fn_t002account_code_changes_set_utc()
RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
IF NEW.input_time IS NULL
OR NEW.timezone IS NULL
OR btrim(NEW.timezone) = '' THEN
NEW.utc_datetime := NULL;
RETURN NEW;
END IF;
IF NOT EXISTS
(
SELECT 1
FROM pg_timezone_names
WHERE name = NEW.timezone
)
THEN
RAISE EXCEPTION
'Invalid timezone: %',
NEW.timezone;
END IF;
NEW.utc_datetime :=
NEW.input_time AT TIME ZONE NEW.timezone;
RETURN NEW;
END;
$$;
Trigger
CREATE TRIGGER trg_t002account_code_changes_set_utc BEFORE INSERT OR UPDATE OF input_time, timezone ON public.t002account_code_changes FOR EACH ROW EXECUTE FUNCTION public.fn_t002account_code_changes_set_utc();
Audit Tracking
A major refinement is adding:
user_creator
user_editor
created_at
updated_at
This creates a fully auditable ownership timeline.
The system can then record:
who created the ownership change
who last edited it
when it was created
when it was modified
when it becomes effective
Recommended Audit Trigger
CREATE OR REPLACE FUNCTION public.fn_t002account_code_changes_audit()
RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
IF TG_OP = 'INSERT'
THEN
NEW.created_at := now();
NEW.updated_at := now();
IF NEW.user_creator IS NULL
THEN
NEW.user_creator := current_user;
END IF;
NEW.user_editor := NEW.user_creator;
ELSIF TG_OP = 'UPDATE'
THEN
NEW.updated_at := now();
IF NEW.user_editor IS NULL
THEN
NEW.user_editor := current_user;
END IF;
END IF;
RETURN NEW;
END;
$$;
Audit Trigger
CREATE TRIGGER trg_t002account_code_changes_audit BEFORE INSERT OR UPDATE ON public.t002account_code_changes FOR EACH ROW EXECUTE FUNCTION public.fn_t002account_code_changes_audit();
Supporting Future Ownership Changes
One of the strongest advantages of this design is the ability to safely schedule future ownership changes.
Example:
Asset UTC Datetime Owner Status
Vehicle A 2026-01-01 10:00 ACC100 Historical
Vehicle A 2026-06-01 09:00 ACC200 Current
Vehicle A 2026-09-01 09:00 ACC300 Future
The future row already exists in the database but should not become active until its effective date arrives.
The Critical Requirement
If an asset has:
no ownership change rows
or no effective ownership change rows yet
then the system should return:
accountcode_initial
from:
t001assets
This makes the ownership model operationally complete.
Current Ownership View
CREATE OR REPLACE VIEW public.v001currentassetownership AS
WITH latest_changes AS
(
SELECT DISTINCT ON (c.pkidt001)
c.pkidt001,
c.new_owner_accountcode,
c.utc_datetime,
c.user_creator,
c.user_editor,
c.updated_at
FROM public.t002account_code_changes c
WHERE c.utc_datetime <= now()
AND COALESCE(c.softdelete,0) <> 1
ORDER BY
c.pkidt001,
c.utc_datetime DESC,
c.gid DESC
)
SELECT
a.gid AS pkidt001,
a.asset_name,
COALESCE
(
lc.new_owner_accountcode,
a.accountcode_initial
) AS current_accountcode,
lc.utc_datetime AS ownership_change_utc,
lc.user_creator,
lc.user_editor,
lc.updated_at
FROM public.t001assets a
LEFT JOIN latest_changes lc
ON a.gid = lc.pkidt001
WHERE COALESCE(a.softdelete,0) <> 1;
Why This View Works Well
The logic becomes:
Situation Result
No ownership changes Initial account code
Only future changes Initial account code
Current effective change exists Latest effective change
Multiple historical changes Most recent effective change
The system always derives the current state dynamically from historical events.
Future Ownership View
Future scheduled changes can also be exposed separately.
CREATE OR REPLACE VIEW public.v002futureassetownership AS
SELECT
c.pkidt001,
a.asset_name,
c.new_owner_accountcode,
c.utc_datetime,
c.timezone,
c.user_creator,
c.user_editor
FROM public.t002account_code_changes c
INNER JOIN public.t001assets a
ON a.gid = c.pkidt001
WHERE c.utc_datetime > now()
AND COALESCE(a.softdelete,0) <> 1
AND COALESCE(c.softdelete,0) <> 1
ORDER BY
c.pkidt001,
c.utc_datetime ASC,
c.gid ASC;
Recommended Index
For performance:
CREATE INDEX idx_t002account_code_changes_asset_utc
ON public.t002account_code_changes
(
pkidt001,
utc_datetime DESC
);
This significantly improves ownership lookups.
Recommended Timezone Lookup Table
A very useful refinement is maintaining a dedicated timezone lookup table rather than allowing free-text timezone entry.
This provides:
validation
consistent naming
cleaner PHPRunner dropdowns
simpler administration
safer reporting
Timezone Table
CREATE TABLE IF NOT EXISTS public.t003timezones
(
gid integer NOT NULL GENERATED ALWAYS AS IDENTITY,
timezone_name varchar(100) NOT NULL,
timezone_display varchar(200) NOT NULL,
timezone_active boolean DEFAULT true,
created_at timestamptz DEFAULT now(),
CONSTRAINT pk_t003timezones
PRIMARY KEY (gid),
CONSTRAINT uq_t003timezones_name
UNIQUE (timezone_name)
);
Recommended Seed Data
INSERT INTO public.t003timezones
(
timezone_name,
timezone_display
)
VALUES
('UTC', 'UTC'),
('Europe/London', 'United Kingdom'),
('Europe/Paris', 'Central Europe'),
('America/New_York', 'Eastern United States'),
('America/Chicago', 'Central United States'),
('America/Los_Angeles', 'Pacific United States'),
('Asia/Dubai', 'Dubai'),
('Asia/Kolkata', 'India'),
('Asia/Tokyo', 'Japan'),
('Australia/Sydney', 'Sydney');
PHPRunner Lookup Configuration
In PHPRunner the timezone field would normally use:
Setting Value
Link field timezone_name
Display field timezone_display
This allows users to see friendly values such as:
United Kingdom
Central Europe
Eastern United States
while PostgreSQL stores the official IANA timezone identifiers.
Final Thoughts
This pattern combines several very strong architectural principles:
immutable history
UTC canonical timestamps
timezone-aware user entry
append-only ownership changes
future scheduling support
soft deletion
audit tracking
derived current-state views
The result is a highly scalable, operationally safe, and audit-friendly ownership model that performs exceptionally well in PostgreSQL and PHPRunner applications.
