docs/database/POSTGRES_DB.md

-- ============================================================================ -- Database Schema for Sales, Returns & Credit Notes -- PostgreSQL 14+ -- ============================================================================

-- Enable UUID extension CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

CREATE SCHEMA IF NOT EXISTS trans;

CREATE TABLE IF NOT EXISTS trans.branches ( branch_id VARCHAR(26) PRIMARY KEY );

CREATE TABLE IF NOT EXISTS trans.merchants ( merchant_id VARCHAR(26) PRIMARY KEY );

CREATE TABLE IF NOT EXISTS trans.customers ( customer_id VARCHAR(26) PRIMARY KEY );

CREATE TABLE IF NOT EXISTS trans.users ( user_id VARCHAR(26) PRIMARY KEY );

CREATE TABLE IF NOT EXISTS trans.catalogues ( catalogue_id VARCHAR(26) PRIMARY KEY );

-- ========================================================================= -- SALES ORDERS MODULE -- ============================================================================

-- Sales Orders Table CREATE TABLE trans.sales_orders ( sales_order_id VARCHAR(26) PRIMARY KEY, order_number VARCHAR(50) UNIQUE NOT NULL, branch_id VARCHAR(26) NOT NULL, merchant_id VARCHAR(26) NOT NULL, order_date TIMESTAMP WITH TIME ZONE NOT NULL, status VARCHAR(20) NOT NULL CHECK (status IN ('draft', 'confirmed', 'processing', 'fulfilled', 'partially_fulfilled', 'cancelled', 'on_hold')),

-- Customer Information
customer_id VARCHAR(26) NOT NULL,
customer_name VARCHAR(255) NOT NULL,
customer_type VARCHAR(10) NOT NULL CHECK (customer_type IN ('b2b', 'b2c')),
customer_phone VARCHAR(20),
customer_email VARCHAR(255),
customer_gstin VARCHAR(15),

-- Financial Summary
subtotal DECIMAL(12,2) NOT NULL DEFAULT 0,
total_discount DECIMAL(12,2) NOT NULL DEFAULT 0,
total_tax DECIMAL(12,2) NOT NULL DEFAULT 0,
shipping_charges DECIMAL(12,2) NOT NULL DEFAULT 0,
grand_total DECIMAL(12,2) NOT NULL DEFAULT 0,
cgst DECIMAL(12,2) DEFAULT 0,
sgst DECIMAL(12,2) DEFAULT 0,
igst DECIMAL(12,2) DEFAULT 0,

-- Payment Information
payment_type VARCHAR(20) NOT NULL CHECK (payment_type IN ('prepaid', 'cod', 'credit', 'partial')),
payment_status VARCHAR(20) NOT NULL CHECK (payment_status IN ('unpaid', 'partial', 'paid', 'refunded', 'overdue')),
payment_method VARCHAR(50),
payment_date TIMESTAMP WITH TIME ZONE,
payment_reference VARCHAR(100),
amount_paid DECIMAL(12,2) NOT NULL DEFAULT 0,
amount_due DECIMAL(12,2) NOT NULL DEFAULT 0,
credit_terms VARCHAR(50),
credit_limit DECIMAL(12,2),

-- Fulfillment Information
fulfillment_status VARCHAR(20) NOT NULL CHECK (fulfillment_status IN ('pending', 'allocated', 'picked', 'packed', 'shipped', 'delivered')),
expected_delivery_date DATE,
actual_delivery_date DATE,

-- Invoice Information
invoice_id VARCHAR(26),
invoice_number VARCHAR(50),
invoice_date TIMESTAMP WITH TIME ZONE,
invoice_pdf_url TEXT,

-- Additional Information
notes TEXT,
internal_notes TEXT,

-- Audit Fields
created_by VARCHAR(26) NOT NULL,
created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
approved_by VARCHAR(26),
approved_at TIMESTAMP WITH TIME ZONE,

-- Metadata
source VARCHAR(20) DEFAULT 'web',
channel VARCHAR(50),
tags TEXT[],
version INTEGER NOT NULL DEFAULT 1,

-- Foreign Keys
CONSTRAINT fk_sales_order_branch FOREIGN KEY (branch_id) REFERENCES trans.branches(branch_id),
CONSTRAINT fk_sales_order_merchant FOREIGN KEY (merchant_id) REFERENCES trans.merchants(merchant_id),
CONSTRAINT fk_sales_order_customer FOREIGN KEY (customer_id) REFERENCES trans.customers(customer_id),
CONSTRAINT fk_sales_order_created_by FOREIGN KEY (created_by) REFERENCES trans.users(user_id)

);

-- Sales Order Items Table CREATE TABLE trans.sales_order_items ( id VARCHAR(26) PRIMARY KEY, sales_order_id VARCHAR(26) NOT NULL,

-- Product Information
sku VARCHAR(50) NOT NULL,
product_id VARCHAR(26) NOT NULL,
product_name VARCHAR(255) NOT NULL,
item_type VARCHAR(20) NOT NULL CHECK (item_type IN ('product', 'service')),

-- Quantity and Pricing
quantity INTEGER NOT NULL CHECK (quantity > 0),
unit_price DECIMAL(10,2) NOT NULL,
tax_percent DECIMAL(5,2) NOT NULL DEFAULT 0,
discount_percent DECIMAL(5,2) NOT NULL DEFAULT 0,
line_total DECIMAL(12,2) NOT NULL,

-- Tax Information
hsn_code VARCHAR(10),
uom VARCHAR(20) NOT NULL,

-- Traceability
batch_no VARCHAR(50),
serials TEXT[],

-- Service Specific
staff_id VARCHAR(26),
staff_name VARCHAR(255),

-- Additional
remarks TEXT,

-- Audit
created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,

-- Foreign Keys
CONSTRAINT fk_sales_order_item_order FOREIGN KEY (sales_order_id) REFERENCES trans.sales_orders(sales_order_id) ON DELETE CASCADE,
CONSTRAINT fk_sales_order_item_product FOREIGN KEY (product_id) REFERENCES trans.catalogues(catalogue_id)

);

-- Sales Order Addresses Table CREATE TABLE trans.sales_order_addresses ( id VARCHAR(26) PRIMARY KEY, sales_order_id VARCHAR(26) NOT NULL, address_type VARCHAR(20) NOT NULL CHECK (address_type IN ('billing', 'shipping')),

line1 VARCHAR(255) NOT NULL,
line2 VARCHAR(255),
city VARCHAR(100) NOT NULL,
state VARCHAR(100) NOT NULL,
postal_code VARCHAR(20) NOT NULL,
country VARCHAR(100) NOT NULL DEFAULT 'India',
landmark VARCHAR(255),

created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,

CONSTRAINT fk_sales_order_address FOREIGN KEY (sales_order_id) REFERENCES trans.sales_orders(sales_order_id) ON DELETE CASCADE

);

-- Sales Order Shipments Table CREATE TABLE trans.sales_order_shipments ( shipment_id VARCHAR(26) PRIMARY KEY, sales_order_id VARCHAR(26) NOT NULL,

carrier VARCHAR(100),
tracking_number VARCHAR(100),
awb_number VARCHAR(100),
shipping_method VARCHAR(50),

dispatch_date TIMESTAMP WITH TIME ZONE,
expected_delivery_date DATE,
actual_delivery_date DATE,

status VARCHAR(20) CHECK (status IN ('pending', 'picked', 'in_transit', 'out_for_delivery', 'delivered', 'failed')),

created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,

CONSTRAINT fk_sales_order_shipment FOREIGN KEY (sales_order_id) REFERENCES trans.sales_orders(sales_order_id) ON DELETE CASCADE

);

-- Sales Order Documents Table CREATE TABLE trans.sales_order_documents ( id VARCHAR(26) PRIMARY KEY, sales_order_id VARCHAR(26) NOT NULL,

filename VARCHAR(255) NOT NULL,
file_url TEXT NOT NULL,
document_type VARCHAR(50) NOT NULL,
file_size INTEGER,
mime_type VARCHAR(100),

uploaded_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
uploaded_by VARCHAR(26),

CONSTRAINT fk_sales_order_document FOREIGN KEY (sales_order_id) REFERENCES trans.sales_orders(sales_order_id) ON DELETE CASCADE

);