DEV Community: Samer Karwande

A Practical Data Model for Evidence-Ready EPR Compliance

Samer Karwande — Thu, 27 Aug 2026 13:08:58 +0000

Environmental compliance trackers often begin as one spreadsheet with a column for every possible requirement. That works for a small dataset, but Extended Producer Responsibility (EPR) evidence becomes difficult to audit when registrations, material categories, targets, recycler documents and returns are mixed in the same row.

A better approach is to model the compliance record as linked entities.

1. Obligated entity

Keep the legal entity separate from the operating site or brand.

entity_id
legal_name
registration_identifier
business_role
responsible_owner

This prevents a change in contact person or site name from rewriting historical records.

2. Material or equipment category

Store the category mapping as its own controlled list.

category_id
regime
category_name
effective_from
effective_to
source_reference

The effective dates matter because classifications and reporting instructions can change. Historical submissions should continue to point to the version used at that time.

3. Obligation period

Treat each reporting period as a separate record rather than adding another set of columns every year.

period_id
entity_id
category_id
period_start
period_end
quantity_basis
target_quantity
status

The calculation workbook or approved methodology should be linked, not hidden inside a comment cell.

4. Evidence document

Every supporting file needs metadata.

evidence_id
document_type
issuer
document_date
valid_from
valid_to
file_location
checksum
review_status

A checksum or version identifier helps prove which file was reviewed when similarly named documents are replaced.

5. Transaction or certificate record

Keep recycler, certificate and quantity details in a transaction table that can be reconciled against the period obligation.

Useful validation rules include:

no certificate quantity without a valid category
no double allocation of the same evidence
certificate validity must overlap the reporting period
totals must reconcile to the approved obligation calculation
every manual adjustment needs a reason and approver

Build an audit trail, not just a dashboard

Dashboards are useful, but an audit trail is more important. Record who changed a value, when it changed, the previous value and the reason. Keep status transitions explicit: draft, reviewed, approved and submitted should not mean the same thing.

The exact fields depend on the applicable regime and current official requirements, but the design principle is stable: separate master data, obligations, evidence and transactions, then connect them with durable identifiers.

For an overview of EPR applicability, registration, category mapping, certificates and return support, see Ujjwal Engineers’ EPR compliance page.

A clean data model does not replace regulatory review. It makes that review faster, traceable and far easier to repeat.

Designing an Audit-Ready Environmental Compliance Tracker

Samer Karwande — Fri, 21 Aug 2026 13:16:11 +0000

Environmental compliance data is often scattered across calendars, laboratory reports, waste manifests, equipment logs and email threads. A simple tracker can make that information reviewable—but only if its structure supports evidence, ownership and validation.

This is a practical data model for building one without turning it into a large software project.

Start with four connected tables

1. Obligations

Use one row per consent condition or statutory requirement. Useful fields include obligation ID, approval reference, requirement, frequency, owner, due rule, evidence type and status.

Use stable IDs such as AIR-012 or WASTE-007. Do not use the row number as an identifier because sorting or inserting rows will break references.

2. Tasks

Generate individual tasks from recurring obligations. Store a task ID, obligation ID, reporting period, due date, assignee, completion date and review status.

This separates the rule—monitor stack emissions quarterly—from each actual monitoring event.

3. Evidence

Store metadata rather than embedding every file inside the tracker. Useful fields include evidence ID, task ID, document name, revision, issue date, storage link, verifier and verification date.

A task should not be marked complete only because a file exists. It should be complete when the file has been reviewed and linked to the correct obligation and period.

4. Exceptions

Keep missed deadlines, equipment downtime and non-conformities visible. Record the related task, detection date, description, risk level, corrective action, owner, target date and closure evidence.

Deleting an overdue task destroys the audit trail. Close it through an exception and corrective-action record instead.

Add validation before dashboards

Colourful dashboards are useful only after the underlying data is reliable. Start with checks such as:

every active obligation has an owner
every recurring obligation has a next due date
every completed task has verified evidence
no evidence file is linked to unrelated reporting periods
monitoring dates fall inside the required period
production and waste records use consistent units
overdue corrective actions remain visible

In a spreadsheet, these can be implemented with lookup formulas and conditional formatting. In a small application, run them as scheduled tests and write failures to a review queue.

Treat dates as rules, not manual text

Avoid storing quarterly only as a note. Store a frequency code and calculate due dates from an explicit rule. This allows reminders to be generated consistently and makes exceptions easier to explain.

Some deadlines are based on financial years, calendar years, consent dates or days after an event. The rule needs to preserve that context.

Preserve changes

Record who changed an obligation, what changed and why. If the tracker is a database, add an audit table. If it is a spreadsheet, issue locked monthly copies and keep a change log.

Never overwrite an old consent condition after an amendment. Close the old record with an effective-end date and create a new version.

Connect the operational checklist

The tracker should mirror what a facility must demonstrate during an inspection: valid approvals, monitoring continuity, production within consented capacity, reconciled waste records, pollution-control equipment logs and closure evidence for previous findings.

This pollution-board inspection checklist provides a useful operational reference for deciding which evidence fields the tracker should contain.

A good compliance tracker does not replace professional review. Its job is to make obligations, evidence and exceptions traceable enough that the right questions are asked before an inspector asks them.

A Practical Data Workflow for HAZOP and QRA Studies

Samer Karwande — Fri, 21 Aug 2026 13:01:22 +0000

Safety studies often fail for a surprisingly ordinary reason: the data trail is hard to reproduce.

A HAZOP workshop may produce hundreds of deviations, causes, safeguards and actions. A QRA may combine release frequencies, weather data, consequence models, ignition assumptions and population inputs. If those elements live in disconnected spreadsheets with inconsistent naming, the study becomes difficult to review and even harder to update.

Here is a lightweight data workflow that keeps the analysis auditable.

1. Give every object a stable ID

Use permanent identifiers for process nodes, equipment, scenarios, safeguards and actions. A node called REACTOR_FEED_01 should keep that ID even if its display name changes.

Stable IDs make joins predictable and stop small wording changes from creating duplicate records.

2. Separate source data from calculated results

Keep raw inputs immutable:

equipment and line lists
P&IDs and revision numbers
material properties
failure-frequency sources
weather and population datasets
operator or inspection evidence

Calculated fields—risk ranking, release rate, consequence distance and individual-risk contribution—should be generated from those inputs rather than manually pasted over them.

3. Store assumptions as first-class data

An assumption is not a comment buried in a workbook. Give it an owner, source, date, status and affected scenario IDs.

A small assumptions table can contain:

Field	Example
assumption_id	A-014
statement	Detector response time is 30 seconds
source	Vendor datasheet, rev 3
owner	Instrumentation lead
affected_scenarios	S-021, S-022
review_status	Approved

This makes later revalidation much faster.

4. Build validation checks into the workflow

Before review, automatically flag:

scenarios without a source or node ID
safeguards with no evidence reference
duplicated scenario IDs
actions without owners or due dates
consequence results generated from superseded inputs
risk values whose units do not match the project standard

Even basic spreadsheet formulas or a short script can catch these issues before a workshop or client review.

5. Preserve version history

Record the dataset version, model version and document revision used for each run. A result without its input version is not reproducible.

For teams using Git, text-based CSV, JSON or YAML exports can provide a useful change history. For spreadsheet-heavy projects, a controlled document register and locked issue copies are still better than emailing files named final_v7_revised.xlsx.

6. Link HAZOP and QRA without mixing their purposes

HAZOP is primarily a structured qualitative method for identifying deviations, causes, consequences and safeguards. QRA quantifies selected accident scenarios using frequency and consequence analysis.

The datasets should connect through scenario IDs, but the methods should not be treated as interchangeable. A clear explanation of the distinction is available in this HAZOP vs QRA guide.

A minimal schema

A practical starting point is five connected tables:

nodes
hazop_deviations
scenarios
safeguards
actions

The goal is not to build a large software platform. It is to make every conclusion traceable from source input to review decision.

When the data structure is clean, workshops become easier to facilitate, assumptions are easier to challenge, and future updates cost much less.