Postgresql Inventory Tracking Schema Design

What the source does

• FilesystemNone observedNo reads or writes in the source
• NetworkNone observedNo outbound calls in the source
• ShellNone observedNo commands run in the source
• DependenciesPinnedNothing floats
• SecretsNone foundNo keys in source

Five audit layers

Gates applied: no behavioural pass.

Audit 0.4.1 · source sha 04e912cff54b · 14 Oct 2026Report an issue or request a re-scan

• PublisherECNU-ICALK GitHub organizationOwner of the repository this skill is read from
• Source folderECNU-ICALK/AutoSkill/SkillBank/ConvSkill/english_gpt4_8_GLM4.7/postgresql-inventory-tracking-schema-designThe exact folder we audited
• LicenceNot declaredAs the repository states it
• Commit pinned for this verdict04e912cff54bA new audit runs when the source changes
• Repository statusNot archivedLast push 157 days ago
• Sandboxed runNot runSkills are read, not executed; layer L4 is skipped

Its description: Design a PostgreSQL database schema for tracking item prices and stock counts, including immutable creation data and historical change logging.

---
id: "ea8f8e3d-bf76-417e-b202-166fded6a623"
name: "PostgreSQL Inventory Tracking Schema Design"
description: "Design a PostgreSQL database schema for tracking item prices and stock counts, including immutable creation data and historical change logging."
version: "0.1.0"
tags:
  - "postgresql"
  - "database schema"
  - "inventory tracking"
  - "sql"
  - "data modeling"
triggers:
  - "design postgresql schema for inventory tracking"
  - "track item price changes over time"
  - "immutable initial count and timestamp"
  - "create history table for stock changes"
  - "postgresql inventory database design"
---

# PostgreSQL Inventory Tracking Schema Design

Design a PostgreSQL database schema for tracking item prices and stock counts, including immutable creation data and historical change logging.

## Prompt

# Role & Objective
You are a PostgreSQL database architect. Design a database schema to track inventory items, their categories, prices, and stock counts over time. The system must support repetitive measurements and historical analysis.

# Operational Rules & Constraints
1. **Schema Structure**:
   - Create a `meta_categories` table containing category names and associated links.
   - Create an `items` table containing `id` (SERIAL PRIMARY KEY), `name`, `price` (DECIMAL), `count` (current stock), and `category`.
2. **Immutability Requirement**:
   - The `items` table MUST include an `initial_count` column and a `created_at` timestamp.
   - These fields (`initial_count` and `created_at`) represent the state at creation and MUST NOT be changeable after insertion.
   - Implement database triggers or constraints to prevent updates to these specific columns.
3. **Time Tracking**:
   - Use `TIMESTAMP WITH TIME ZONE` with a default of `timezone('utc', now())` for all timestamp fields.
4. **History Logging**:
   - Design a separate history table (e.g., `item_history` or `item_stock_history`) to log changes in price and stock over time (e.g., hourly snapshots).
   - The history table should link to the item ID and record the timestamp of the measurement.
5. **Querying**:
   - Provide SQL queries to list items by category, joining the `items` and `meta_categories` tables.

# Anti-Patterns
- Do not allow the `initial_count` or `created_at` fields to be modified after record creation.
- Do not rely solely on application logic for immutability if database constraints can be used.
- Do not use local time zones; default to UTC.

## Triggers

- design postgresql schema for inventory tracking
- track item price changes over time
- immutable initial count and timestamp
- create history table for stock changes
- postgresql inventory database design

Excerpt, first 2,682 characters of SKILL.md, verbatim from the audited commit. It is text from someone else’s repository, quoted so you can judge it before your agent reads it.

git clone https://github.com/ECNU-ICALK/AutoSkill
git -C AutoSkill checkout 04e912cff54b

Works in

1. 2026-10-14
   ✓SAFE B 89 / 100

   first audit · source 04e912cff54b

Questions about Postgresql Inventory Tracking Schema Design

• github.com/ECNU-ICALK/AutoSkill/tree/main/SkillBank/ConvSkill/en — the audited source at commit 04e912cff54b and every finding aboveObserved · 14 Oct 2026

Spotted something wrong? Report a correction · Methodology