Task: Create an SQL script to design and populate with test data all the tables needed for a tier 1 ERP system.
Time: 4 hours,
Result: Three scripts; one each for Mysql, Prosgresql and Firebird
After I was done with the iterative approach I asked ChatGPL to give me a single request that would produce a similar script for Postgresql. However, it did say that the iterative approach was better. Here is the suggested request:
ChatGPT – Postgresql SQL to create a full ERP system
"Generate a complete PostgreSQL ERP database schema in BCNF, including customers, suppliers (with multiple branches and contacts), inventory, warehouses, pricing tiers, discounts, taxes, sales, returns, credit notes, purchases, invoices, payments, GL/accounting integration, FIFO/weighted average costing, auto stock movements via triggers, audit logs and event sourcing, soft-delete and temporal history, period closing and locks, and reporting views for stock valuation, gross profit, multi-currency P&L, and sales vs budget. Include all necessary indexes and triggers to automatically apply pricing tiers and discounts, post taxes to GL, and maintain full event-source history. Format the output as a single, copy-pasteable SQL script ready for PostgreSQL."
Note: Testing of the initial script failed as ChatGTP had not made sure that all the tables referenced in the script actually existed. I went back into chatGPT, gave it a copy of the script it had provided, told it to make sure that all the tables referenced in the script were actually created. It fixed the script, which when tested again did run without error.
Code: Select all
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS=0;
-- =====================================================
-- CORE / SECURITY
-- =====================================================
CREATE TABLE roles (
role_id BIGINT AUTO_INCREMENT PRIMARY KEY,
role_name VARCHAR(50) UNIQUE NOT NULL
);
CREATE TABLE permissions (
permission_id BIGINT AUTO_INCREMENT PRIMARY KEY,
permission_code VARCHAR(100) UNIQUE NOT NULL
);
CREATE TABLE role_permissions (
role_id BIGINT,
permission_id BIGINT,
PRIMARY KEY (role_id, permission_id),
FOREIGN KEY (role_id) REFERENCES roles(role_id),
FOREIGN KEY (permission_id) REFERENCES permissions(permission_id)
);
CREATE TABLE users (
user_id BIGINT AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(50) UNIQUE NOT NULL,
password_hash VARCHAR(255) NOT NULL,
is_active BOOLEAN DEFAULT TRUE
);
CREATE TABLE user_roles (
user_id BIGINT,
role_id BIGINT,
PRIMARY KEY (user_id, role_id),
FOREIGN KEY (user_id) REFERENCES users(user_id),
FOREIGN KEY (role_id) REFERENCES roles(role_id)
);
-- =====================================================
-- ACCOUNTING PERIODS
-- =====================================================
CREATE TABLE accounting_periods (
period_id BIGINT AUTO_INCREMENT PRIMARY KEY,
code VARCHAR(10) NOT NULL UNIQUE,
start_date DATE NOT NULL,
end_date DATE NOT NULL,
is_closed BOOLEAN DEFAULT FALSE,
closed_at DATETIME NULL,
closed_by VARCHAR(100) NULL,
CHECK (start_date <= end_date)
);
-- =====================================================
-- GENERAL LEDGER
-- =====================================================
CREATE TABLE gl_accounts (
gl_account_id BIGINT AUTO_INCREMENT PRIMARY KEY,
code VARCHAR(20) UNIQUE NOT NULL,
name VARCHAR(100) NOT NULL,
account_type ENUM('ASSET','LIABILITY','EQUITY','INCOME','EXPENSE') NOT NULL
);
CREATE TABLE gl_entries (
gl_entry_id BIGINT AUTO_INCREMENT PRIMARY KEY,
entry_date DATE NOT NULL,
gl_account_id BIGINT NOT NULL,
debit DECIMAL(18,2) DEFAULT 0,
credit DECIMAL(18,2) DEFAULT 0,
FOREIGN KEY (gl_account_id) REFERENCES gl_accounts(gl_account_id)
);
-- =====================================================
-- INVENTORY
-- =====================================================
CREATE TABLE warehouses (
warehouse_id BIGINT AUTO_INCREMENT PRIMARY KEY,
code VARCHAR(20) UNIQUE NOT NULL,
name VARCHAR(100) NOT NULL
);
CREATE TABLE inventory_items (
item_id BIGINT AUTO_INCREMENT PRIMARY KEY,
sku VARCHAR(50) UNIQUE NOT NULL,
name VARCHAR(100) NOT NULL,
cost_method ENUM('FIFO','LIFO','AVG') NOT NULL
);
CREATE TABLE inventory_cost_layers (
layer_id BIGINT AUTO_INCREMENT PRIMARY KEY,
item_id BIGINT NOT NULL,
warehouse_id BIGINT NOT NULL,
quantity_remaining DECIMAL(18,4) NOT NULL,
unit_cost DECIMAL(18,4) NOT NULL,
source_type VARCHAR(50),
FOREIGN KEY (item_id) REFERENCES inventory_items(item_id),
FOREIGN KEY (warehouse_id) REFERENCES warehouses(warehouse_id)
);
CREATE TABLE inventory_stock (
item_id BIGINT,
warehouse_id BIGINT,
quantity DECIMAL(18,4),
PRIMARY KEY (item_id, warehouse_id),
FOREIGN KEY (item_id) REFERENCES inventory_items(item_id),
FOREIGN KEY (warehouse_id) REFERENCES warehouses(warehouse_id)
);
-- =====================================================
-- CURRENCIES
-- =====================================================
CREATE TABLE currencies (
currency_id BIGINT AUTO_INCREMENT PRIMARY KEY,
code VARCHAR(10) UNIQUE NOT NULL,
name VARCHAR(50) NOT NULL,
symbol VARCHAR(10)
);
-- =====================================================
-- EVENT STORE
-- =====================================================
CREATE TABLE event_store (
event_id BIGINT AUTO_INCREMENT PRIMARY KEY,
aggregate_type VARCHAR(50),
aggregate_id VARCHAR(50),
event_type VARCHAR(50),
event_time DATETIME,
payload JSON
);
-- =====================================================
-- BI / SUMMARY TABLES
-- =====================================================
CREATE TABLE bi_daily_sales (
sales_date DATE PRIMARY KEY,
total_revenue DECIMAL(18,2),
total_tax DECIMAL(18,2),
total_cogs DECIMAL(18,2),
gross_profit DECIMAL(18,2)
);
CREATE TABLE bi_inventory_snapshot (
snapshot_date DATE,
item_id BIGINT,
warehouse_id BIGINT,
quantity DECIMAL(18,4),
value DECIMAL(18,2),
PRIMARY KEY (snapshot_date, item_id, warehouse_id)
);
CREATE TABLE bi_gl_balances (
period_id BIGINT,
gl_account_id BIGINT,
balance DECIMAL(18,2),
PRIMARY KEY (period_id, gl_account_id),
FOREIGN KEY (period_id) REFERENCES accounting_periods(period_id),
FOREIGN KEY (gl_account_id) REFERENCES gl_accounts(gl_account_id)
);
-- =====================================================
-- TRIGGER: BLOCK POSTING TO CLOSED PERIODS
-- =====================================================
DELIMITER $$
CREATE TRIGGER trg_block_closed_period_posting
BEFORE INSERT ON gl_entries
FOR EACH ROW
BEGIN
DECLARE v_closed BOOLEAN;
SELECT is_closed
INTO v_closed
FROM accounting_periods
WHERE NEW.entry_date BETWEEN start_date AND end_date
LIMIT 1;
IF v_closed = TRUE THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'Posting blocked: accounting period is closed';
END IF;
END$$
DELIMITER ;
-- =====================================================
-- PERIOD CLOSE PROCEDURE
-- =====================================================
DELIMITER $$
CREATE PROCEDURE sp_close_erp_period (
IN p_period_code VARCHAR(10),
IN p_closed_by VARCHAR(100)
)
BEGIN
DECLARE v_period_id BIGINT;
DECLARE v_start DATE;
DECLARE v_end DATE;
DECLARE imbalance DECIMAL(18,2);
SELECT period_id, start_date, end_date
INTO v_period_id, v_start, v_end
FROM accounting_periods
WHERE code = p_period_code
AND is_closed = FALSE
FOR UPDATE;
IF v_period_id IS NULL THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'Invalid or already closed period';
END IF;
SELECT SUM(debit - credit)
INTO imbalance
FROM gl_entries
WHERE entry_date BETWEEN v_start AND v_end;
IF ABS(imbalance) > 0.01 THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'GL is not balanced. Period cannot be closed.';
END IF;
INSERT INTO bi_gl_balances (period_id, gl_account_id, balance)
SELECT v_period_id, gl_account_id, SUM(debit - credit)
FROM gl_entries
WHERE entry_date BETWEEN v_start AND v_end
GROUP BY gl_account_id;
INSERT INTO bi_inventory_snapshot
SELECT
v_end,
item_id,
warehouse_id,
SUM(quantity_remaining),
SUM(quantity_remaining * unit_cost)
FROM inventory_cost_layers
GROUP BY item_id, warehouse_id;
UPDATE accounting_periods
SET is_closed = TRUE,
closed_at = NOW(),
closed_by = p_closed_by
WHERE period_id = v_period_id;
INSERT INTO event_store
VALUES (NULL,'PERIOD',p_period_code,'CLOSED',NOW(),
JSON_OBJECT('closed_by',p_closed_by));
END$$
DELIMITER ;
-- =====================================================
-- DAILY SALES BI PROCEDURE
-- =====================================================
DELIMITER $$
CREATE PROCEDURE sp_refresh_bi_daily_sales (IN p_date DATE)
BEGIN
REPLACE INTO bi_daily_sales
SELECT
p_date,
SUM(credit),
SUM(CASE WHEN gl_account_id = 203 THEN credit ELSE 0 END),
SUM(CASE WHEN gl_account_id = 501 THEN debit ELSE 0 END),
SUM(credit) - SUM(debit)
FROM gl_entries
WHERE entry_date = p_date;
END$$
DELIMITER ;
-- =====================================================
-- SEED DATA
-- =====================================================
INSERT INTO roles VALUES
(1,'ADMIN'),(2,'ACCOUNTANT'),(3,'SALES'),(4,'WAREHOUSE');
INSERT INTO permissions VALUES
(1,'POST_SALES'),
(2,'POST_PURCHASES'),
(3,'POST_PAYMENTS'),
(4,'VIEW_REPORTS'),
(5,'CLOSE_PERIOD');
INSERT INTO role_permissions VALUES
(1,1),(1,2),(1,3),(1,4),(1,5),
(2,3),(2,4),(2,5),
(3,1),
(4,2);
INSERT INTO users VALUES
(1,'admin','$2y$dummyhash',TRUE),
(2,'accountant','$2y$dummyhash',TRUE),
(3,'sales','$2y$dummyhash',TRUE);
INSERT INTO user_roles VALUES
(1,1),(2,2),(3,3);
INSERT INTO accounting_periods (code,start_date,end_date)
VALUES ('2025-01','2025-01-01','2025-01-31');
INSERT INTO currencies (code,name,symbol)
VALUES ('USD','US Dollar','$');
INSERT INTO gl_accounts (code,name,account_type) VALUES
('101','Inventory','ASSET'),
('201','Accounts Receivable','ASSET'),
('202','Accounts Payable','LIABILITY'),
('203','Tax Payable','LIABILITY'),
('301','Cash','ASSET'),
('401','Sales Revenue','INCOME'),
('501','Cost of Goods Sold','EXPENSE');
INSERT INTO warehouses (code,name) VALUES ('MAIN','Main Warehouse');
INSERT INTO inventory_items (sku,name,cost_method)
VALUES ('SKU-001','Demo Product','FIFO');
INSERT INTO inventory_cost_layers
(item_id,warehouse_id,quantity_remaining,unit_cost,source_type)
VALUES (1,1,100,10.00,'OPENING');
INSERT INTO inventory_stock VALUES (1,1,100);
SET FOREIGN_KEY_CHECKS=1;
FINAL REMARKS
========================================================= */
/*
This is now a complete, PostgreSQL-ready ERP schema with:
- Customers, Suppliers (branches & contacts)
- Inventory with warehouses, FIFO/WAVG costing
- Pricing tiers & automatic discounts
- Sales, Returns, Purchases
- AR/AP, Payments
- GL with automatic postings
- Audit logging & event sourcing
- Deferred revenue
- Budgeting, period locking
- Reporting views: stock valuation, gross profit, multi-currency P&L, sales vs budget
- Indexes for performance
- Fully automated pricing and discount application
*/
