Data Engineering · University DBMS Project

Entropy-Aware Data Preservation System

Case study published 3 September 2026 Project built 22 February–17 May 2026

A PostgreSQL system that tracks state snapshots, records every change, and gives each snapshot an "entropy" score — in practice a weighted change-density ratio — then automatically recommends whether it should be discarded, compressed, preserved or archived. The scoring and routing run inside the database as PL/pgSQL functions and triggers, with a PHP dashboard for trends and audit logs. Its full title is Universe State Compression & Entropy-Aware Data Preservation System.

Problem

Systems that snapshot their state accumulate data faster than it can reasonably be stored. Deleting on a fixed schedule is blunt: it throws away the snapshots that actually carry new information just as readily as the ones that repeat what is already known.

The question the project set out to answer: can a database decide for itself which of its own snapshots are worth keeping?

Solution

Retention is treated as a measurement problem rather than a scheduling one. Each snapshot is scored by how much change it packs into its footprint, and that score drives an automatic recommendation.

Snapshot tracking — universe/state snapshots and their state changes are recorded.
Entropy scoring — a weighted change-density score is computed per snapshot by in-database functions.
Preservation decisions — each snapshot is routed to discard, compress, preserve or archive.
Integrity checks and audit logs — changes and decisions are traceable after the fact.
Snapshot comparison and export — snapshots can be diffed and exported.

Implementation

The logic lives in the database rather than in application code, so the scoring cannot be bypassed by whichever client happens to write the data.

Schema: 10 tables in 01_schema.sql — universes, state_snapshots, state_changes, entropy_metrics, preservation_rules, preservation_decisions, compressed_states, archives, integrity_checks and audit_logs.
Logic: 10 PL/pgSQL functions, 12 triggers and 3 views in 02_functions_triggers_views.sql.
Interface: PHP 8 web application reading the same database, charting entropy trends with Chart.js.
Containers: An optional Docker Compose environment brings up a PHP 8.3 app container and a PostgreSQL 16 database container, loading the three SQL files automatically.
Setup: Schema, functions/triggers/views and sample data load as three ordered psql scripts.

Because the triggers fire on write, a snapshot is scored and classified as it arrives — the recommendation is a property of the data, not the result of a batch job run later.

Fig. 1 — what happens to a snapshot between the INSERT and the dashboard, taken from 01_schema.sql and 02_functions_triggers_views.sql. The counts and function names are the ones in the SQL files, not a simplification.

How the entropy score is actually calculated

The name is borrowed from information theory, but the metric is a deliberately simple weighted change-density score, not Shannon entropy and not a logarithmic measure. calculate_entropy(snapshot_id) computes:

entropy = (SUM(change_weight) × COUNT(state_changes)) / snapshot_size_mb

Each recorded change carries a weight assigned by fn_change_weight() — CREATE 1, UPDATE 2, DELETE 3, CORRUPTION 5 — so a snapshot scores highly when it packs many heavily-weighted changes into a small footprint. decide_preservation() then matches that score against threshold rows in the preservation_rules table and writes a decision plus a generated reason into preservation_decisions. Naming it "entropy" is the honest weak point of the project: it measures change density, and calling it entropy promises more information-theoretic rigour than the formula delivers.

Technology

PostgreSQL
PL/pgSQL
SQL
PHP 8
JavaScript
Chart.js
HTML / CSS
Docker

Team and my contribution

A three-person university DBMS project — Sajid Islam, Azwar Khan and Abbas Bajwa — built for coursework at FAST NUCES, as listed in the repository's submission README. My résumé records my contribution as the PostgreSQL scoring pipeline and the rule-based preservation logic.

The repository does not document which member wrote which file, so the split is not broken down further here.

Limitations and lessons learned

The domain is synthetic: "universe state" snapshots are an academic framing, and the system was validated against sample data rather than a production workload.
Preservation decisions are rule-based thresholds over the entropy score, not a learned policy — predictable and inspectable, but not adaptive.
The metric is a weighted change-density ratio, not Shannon entropy — the project's name overstates the mathematics, which is the correction this write-up exists to make.
It runs locally against a PostgreSQL instance; there is no hosted demo, and the bundled web interface ships with a single hard-coded demo login rather than real user management.
The lesson that transferred furthest: putting the rules in the database. Triggers and views meant the classification held no matter which client wrote the data — the same instinct behind grounding an LLM in retrieved records instead of trusting the caller.