All-in-One ERP — System Design Document

High-Level Architecture (Modular Monolith + Microservices)

┌─────────────────────────────────────────────────────────────────────┐
│                        Client Layer                                  │
│  ┌──────────┐  ┌──────────┐  ┌──────────┐  ┌───────────────────┐   │
│  │  Web App │  │ Mobile   │  │  Desktop │  │ 3rd-Party APIs    │   │
│  │ (React)  │  │ (Flutter)│  │ (Electron)│  │ (E-comm, Banks)   │   │
│  └────┬─────┘  └────┬─────┘  └────┬─────┘  └────────┬──────────┘   │
└───────┼──────────────┼─────────────┼──────────────────┼──────────────┘
        │              │             │                  │
┌───────┼──────────────┼─────────────┼──────────────────┼──────────────┐
│       ▼              ▼             ▼                  ▼              │
│  ┌──────────────────────────────────────────────────────────────┐   │
│  │                 API Gateway (Supabase + Custom)               │   │
│  │  ┌──────────┐  ┌──────────┐  ┌──────────┐  ┌──────────────┐ │   │
│  │  │ REST API │  │ GraphQL  │  │ Webhooks │  │ Edge Functions│ │   │
│  │  └──────────┘  └──────────┘  └──────────┘  └──────────────┘ │   │
│  └──────────────────────────┬───────────────────────────────────┘   │
│                              │                                       │
│  ┌──────────────────────────▼───────────────────────────────────┐   │
│  │                    Service Modules                             │   │
│  │  ┌──────────┐ ┌──────────┐ ┌──────────┐ ┌──────────────────┐ │   │
│  │  │ Auth &   │ │ Accounting│ │Inventory │ │ GST Compliance   │ │   │
│  │  │ RBAC     │ │ Ledger    │ │ Mgmt     │ │ Engine           │ │   │
│  │  └──────────┘ └──────────┘ └──────────┘ └──────────────────┘ │   │
│  │  ┌──────────┐ ┌──────────┐ ┌──────────┐ ┌──────────────────┐ │   │
│  │  │ Invoicing│ │ Reporting│ │Industry  │ │ E-Way / E-Invoice│ │   │
│  │  │ Billing  │ │ Analytics│ │Configs   │ │ Generator         │ │   │
│  │  └──────────┘ └──────────┘ └──────────┘ └──────────────────┘ │   │
│  └──────────────────────────┬───────────────────────────────────┘   │
│                              │                                       │
│  ┌──────────────────────────▼───────────────────────────────────┐   │
│  │                   Data Layer (Supabase)                        │   │
│  │  ┌──────────┐ ┌──────────┐ ┌──────────┐ ┌──────────────────┐ │   │
│  │  │PostgreSQL│ │ Realtime │ │ Storage  │ │ Auth (GoTrue)    │ │   │
│  │  │  (Core)  │ │ (WS)     │ │ (Files)  │ │ + RLS            │ │   │
│  │  └──────────┘ └──────────┘ └──────────┘ └──────────────────┘ │   │
│  └──────────────────────────────────────────────────────────────┘   │
└─────────────────────────────────────────────────────────────────────┘

Core Tables

organizations

CREATE TABLE organizations (
  id            BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name          TEXT NOT NULL,
  gstin         TEXT UNIQUE,
  pan           TEXT,
  address       JSONB,           -- {street,city,state,pincode,country}
  contact       JSONB,           -- {phone,email}
  industry_type TEXT,            -- pharmacy, retail, fmcg, ...
  variant       TEXT DEFAULT 'blue',  -- express, blue, saffron, emerald
  settings      JSONB DEFAULT '{}',
  created_at    TIMESTAMPTZ DEFAULT NOW()
);

users (Supabase Auth + profile)

CREATE TABLE user_profiles (
  id            UUID PRIMARY KEY REFERENCES auth.users(id) ON DELETE CASCADE,
  org_id        BIGINT REFERENCES organizations(id),
  full_name     TEXT,
  role          TEXT NOT NULL DEFAULT 'staff'
                CHECK (role IN ('admin','accountant','sales','inventory_manager','staff')),
  phone         TEXT,
  avatar_url    TEXT,
  is_active     BOOLEAN DEFAULT TRUE,
  created_at    TIMESTAMPTZ DEFAULT NOW()
);

chart_of_accounts (Ledger)

CREATE TABLE chart_of_accounts (
  id            BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  org_id        BIGINT REFERENCES organizations(id),
  code          TEXT NOT NULL,         -- Account code
  name          TEXT NOT NULL,         -- Account name
  type          TEXT NOT NULL           -- asset, liability, income, expense, equity
                CHECK (type IN ('asset','liability','income','expense','equity')),
  parent_id     BIGINT REFERENCES chart_of_accounts(id),
  is_active     BOOLEAN DEFAULT TRUE,
  UNIQUE(org_id, code)
);

journal_entries

CREATE TABLE journal_entries (
  id            BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  org_id        BIGINT REFERENCES organizations(id),
  entry_date    DATE NOT NULL DEFAULT CURRENT_DATE,
  reference     TEXT,                  -- Invoice #, Voucher #
  description   TEXT,
  entry_type    TEXT DEFAULT 'manual'  -- manual, invoice, payment, contra, gst
                CHECK (entry_type IN ('manual','invoice','payment','contra','gst')),
  created_by    UUID REFERENCES auth.users(id),
  created_at    TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE journal_lines (
  id            BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  entry_id      BIGINT REFERENCES journal_entries(id) ON DELETE CASCADE,
  account_id    BIGINT REFERENCES chart_of_accounts(id),
  debit         NUMERIC(14,2) DEFAULT 0,
  credit        NUMERIC(14,2) DEFAULT 0,
  description   TEXT
);

products / inventory

CREATE TABLE products (
  id            BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  org_id        BIGINT REFERENCES organizations(id),
  name          TEXT NOT NULL,
  sku           TEXT,
  hsn_code      TEXT,                  -- GST HSN
  sac_code      TEXT,                  -- GST SAC (services)
  unit          TEXT DEFAULT 'nos',    -- kg, m, box, nos, ltr
  gst_rate      NUMERIC(5,2) DEFAULT 0,
  cess_rate     NUMERIC(5,2) DEFAULT 0,
  category      TEXT,
  brand         TEXT,
  attributes    JSONB DEFAULT '[]',    -- [{name:'Color',value:'Red'},...]
  mrp           NUMERIC(12,2),
  selling_price NUMERIC(12,2),
  purchase_price NUMERIC(12,2),
  is_active     BOOLEAN DEFAULT TRUE,
  created_at    TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE inventory_batches (
  id            BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  org_id        BIGINT REFERENCES organizations(id),
  product_id    BIGINT REFERENCES products(id),
  batch_no      TEXT,
  mfg_date      DATE,
  expiry_date   DATE,
  quantity      NUMERIC(12,3) DEFAULT 0,
  cost_price    NUMERIC(12,2),
  location      TEXT,                  -- Warehouse / Rack
  is_active     BOOLEAN DEFAULT TRUE
);

CREATE TABLE inventory_serial_numbers (
  id            BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  org_id        BIGINT REFERENCES organizations(id),
  product_id    BIGINT REFERENCES products(id),
  batch_id      BIGINT REFERENCES inventory_batches(id),
  serial_no     TEXT NOT NULL UNIQUE,
  status        TEXT DEFAULT 'available'
                CHECK (status IN ('available','sold','damaged','returned'))
);

invoices

CREATE TABLE invoices (
  id                BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  org_id            BIGINT REFERENCES organizations(id),
  invoice_no        TEXT NOT NULL,
  invoice_date      DATE NOT NULL DEFAULT CURRENT_DATE,
  due_date          DATE,
  customer_id       BIGINT REFERENCES parties(id),
  billing_address   JSONB,
  shipping_address  JSONB,
  gstin             TEXT,
  place_of_supply   TEXT,              -- State code for GST
  invoice_type      TEXT DEFAULT 'regular'
                    CHECK (invoice_type IN ('regular','export','debit_note','credit_note','proforma')),
  reverse_charge    BOOLEAN DEFAULT FALSE,
  payment_terms     TEXT,
  subtotal          NUMERIC(14,2),
  discount_pct      NUMERIC(5,2) DEFAULT 0,
  discount_amt      NUMERIC(14,2) DEFAULT 0,
  taxable_amt       NUMERIC(14,2),
  cgst_amt          NUMERIC(14,2) DEFAULT 0,
  sgst_amt          NUMERIC(14,2) DEFAULT 0,
  igst_amt          NUMERIC(14,2) DEFAULT 0,
  cess_amt          NUMERIC(14,2) DEFAULT 0,
  total             NUMERIC(14,2),
  status            TEXT DEFAULT 'draft'
                    CHECK (status IN ('draft','sent','paid','overdue','cancelled')),
  irn               TEXT,              -- Invoice Reference Number (e-invoice)
  eway_bill_no      TEXT,
  created_by        UUID REFERENCES auth.users(id),
  created_at        TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE invoice_lines (
  id              BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  invoice_id      BIGINT REFERENCES invoices(id) ON DELETE CASCADE,
  product_id      BIGINT REFERENCES products(id),
  batch_id        BIGINT REFERENCES inventory_batches(id),
  description     TEXT,
  quantity        NUMERIC(12,3),
  unit            TEXT,
  rate            NUMERIC(14,2),
  discount_pct    NUMERIC(5,2) DEFAULT 0,
  taxable_amt     NUMERIC(14,2),
  gst_rate        NUMERIC(5,2),
  cgst_amt        NUMERIC(14,2) DEFAULT 0,
  sgst_amt        NUMERIC(14,2) DEFAULT 0,
  igst_amt        NUMERIC(14,2) DEFAULT 0,
  total           NUMERIC(14,2)
);

parties (customers / suppliers)

CREATE TABLE parties (
  id            BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  org_id        BIGINT REFERENCES organizations(id),
  type          TEXT NOT NULL CHECK (type IN ('customer','supplier','both')),
  name          TEXT NOT NULL,
  gstin         TEXT,
  pan           TEXT,
  phone         TEXT,
  email         TEXT,
  address       JSONB,
  credit_limit  NUMERIC(14,2),
  opening_bal   NUMERIC(14,2) DEFAULT 0,
  is_active     BOOLEAN DEFAULT TRUE,
  created_at    TIMESTAMPTZ DEFAULT NOW()
);

gst_records

CREATE TABLE gst_records (
  id              BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  org_id          BIGINT REFERENCES organizations(id),
  return_type     TEXT NOT NULL CHECK (return_type IN ('GSTR1','GSTR2A','GSTR3B','GSTR9')),
  return_period   TEXT NOT NULL,      -- '042024' (Apr 2024)
  invoice_id      BIGINT REFERENCES invoices(id),
  party_id        BIGINT REFERENCES parties(id),
  gstin           TEXT,
  invoice_no      TEXT,
  invoice_date    DATE,
  taxable_amt     NUMERIC(14,2),
  cgst_amt        NUMERIC(14,2) DEFAULT 0,
  sgst_amt        NUMERIC(14,2) DEFAULT 0,
  igst_amt        NUMERIC(14,2) DEFAULT 0,
  status          TEXT DEFAULT 'pending'
                  CHECK (status IN ('pending','matched','mismatched','filed')),
  filed_at        TIMESTAMPTZ
);

industry_configs

CREATE TABLE industry_configs (
  id              BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  industry_type   TEXT NOT NULL UNIQUE,
  label           TEXT NOT NULL,
  features        JSONB DEFAULT '[]',  -- feature flags
  default_accounts JSONB DEFAULT '[]', -- COA templates
  gst_required    BOOLEAN DEFAULT TRUE,
  is_active       BOOLEAN DEFAULT TRUE
);

INSERT INTO industry_configs (industry_type, label, features) VALUES
  ('retail','Retail Shop','["billing","inventory","gst"]'),
  ('pharmacy','Pharmacy','["batch","expiry","schedule_h","gst"]'),
  ('fmcg','FMCG','["batch","expiry","distribution","gst"]'),
  ('auto_parts','Auto Parts','["serial_no","interchangeable","gst"]'),
  ('food_beverages','Food & Beverages','["batch","expiry","fssai","gst"]'),
  ('chemical','Chemical','["batch","hazardous","gst"]'),
  ('computer_hardware','Computer Hardware','["serial_no","warranty","gst"]'),
  ('furniture','Furniture','["attributes","dimensions","gst"]'),
  ('book_publishing','Book Publishing','["isbn","author","edition","gst"]'),
  ('travel','Travel','["booking","itinerary","gst"]'),
  ('electrical','Electrical','["serial_no","warranty","gst"]'),
  ('paper_mill','Paper Mill','["batch","weight","gst"]'),
  ('paint','Paint','["batch","shade","gst"]'),
  ('mobile','Mobile','["imei","brand","warranty","gst"]'),
  ('garments','Garments','["size","color","season","gst"]'),
  ('jewellery','Jewellery','["hallmark","carat","weight","gst"]'),
  ('agriculture','Agriculture','["mandi","commission","gst"]'),
  ('stationery','Stationery','["batch","gst"]'),
  ('electronics','Electronics','["serial_no","warranty","gst"]'),
  ('real_estate','Real Estate','["project","unit","rera","gst"]'),
  ('grocery','Grocery','["batch","expiry","gst"]'),
  ('ecommerce','Ecommerce','["order_sync","marketplace","gst"]');

RESTful Endpoints

All endpoints are prefixed with /api/v1.
Authentication: Bearer token (Supabase JWT).
Row-Level Security (RLS) enforced at database level.

RLS Policies (Supabase)

Each table has RLS enabled. Users see only their organization's data.

-- Base policy: users can only access their org's data
CREATE POLICY org_isolation ON products
  FOR ALL USING (
    org_id IN (
      SELECT org_id FROM user_profiles WHERE id = auth.uid()
    )
  );

-- Admin: full access
CREATE POLICY admin_all ON products
  FOR ALL USING (
    EXISTS (
      SELECT 1 FROM user_profiles
      WHERE id = auth.uid() AND role = 'admin'
    )
  );

-- Inventory Manager: CRUD on products, batches, serials
CREATE POLICY inv_mgr_products ON products
  FOR ALL USING (
    EXISTS (
      SELECT 1 FROM user_profiles
      WHERE id = auth.uid() AND role = 'inventory_manager'
    )
  );

-- Sales: read only products, CRUD on invoices, parties
CREATE POLICY sales_read_products ON products
  FOR SELECT USING (
    EXISTS (
      SELECT 1 FROM user_profiles
      WHERE id = auth.uid() AND role = 'sales'
    )
  );

-- Accountant: full on accounts, read on others
CREATE POLICY accountant_finance ON chart_of_accounts
  FOR ALL USING (
    EXISTS (
      SELECT 1 FROM user_profiles
      WHERE id = auth.uid() AND role IN ('accountant','admin')
    )
  );

Role Matrix

E-Invoice Flow

  1. User creates invoice  ──>  Invoice status = 'draft'
  2. User clicks "Generate E-Invoice"
  3. System validates: GSTIN, HSN/SAC, amounts
  4. POST to Govt ERP API (IRP):
     - Request: {invoice JSON as per GST schema}
     - Response: {irn, ack_no, ack_date, signed_invoice, qr_code}
  5. Store IRN in invoices.irn
  6. Attach QR code to PDF invoice
  7. Invoice status → 'sent'

E-Way Bill Flow

  1. Triggered when invoice value > ₹50,000 (configurable)
  2. Required fields: transporter, vehicle_no, distance, from_pincode, to_pincode
  3. POST to GST e-way bill API
  4. Store ewb_no in invoices.eway_bill_no
  5. Auto-regenerate on route change

GST Return Reconciliation

  1. Fetch GSTR-2A from GST Portal (inward supplies)
  2. Match against purchase invoices:
     - Match: vendor GSTIN, invoice no, date, taxable amount, tax
     - Status: matched / mismatched / missing
  3. GSTR-1 (outward) auto-populated from sales invoices
  4. GSTR-3B auto-calculated summary
  5. GSTR-9 annual return compilation

GST Rate Reference Table

CREATE TABLE gst_rates (
  hsn_code    TEXT PRIMARY KEY,
  description TEXT,
  cgst        NUMERIC(5,2),
  sgst        NUMERIC(5,2),
  igst        NUMERIC(5,2),
  cess        NUMERIC(5,2) DEFAULT 0,
  effective_from DATE,
  effective_to   DATE
);

INSERT INTO gst_rates VALUES
  ('0101','Live animals',2.5,2.5,5,0,'2017-07-01','9999-12-31'),
  ('0402','Milk powder',2.5,2.5,5,0,'2017-07-01','9999-12-31'),
  ('0901','Coffee',2.5,2.5,5,0,'2017-07-01','9999-12-31'),
  ('1701','Sugar',2.5,2.5,5,0,'2017-07-01','9999-12-31'),
  ('2710','Petrol',NULL,NULL,NULL,0,'2017-07-01','9999-12-31'), -- exempt
  ('3004','Pharmaceuticals',6,6,12,0,'2017-07-01','9999-12-31'),
  ('6204','Women garments',6,6,12,0,'2017-07-01','9999-12-31'),
  ('8471','Computers',9,9,18,0,'2017-07-01','9999-12-31'),
  ('8517','Mobile phones',9,9,18,0,'2017-07-01','9999-12-31'),
  ('9999','Services (general)',9,9,18,0,'2017-07-01','9999-12-31')
  ON CONFLICT (hsn_code) DO NOTHING;

Industry Feature Flags

Per-Industry COA Templates

-- Example: Pharmacy chart of accounts seed
INSERT INTO chart_of_accounts (org_id, code, name, type)
SELECT
  o.id, a.code, a.name, a.type
FROM organizations o
CROSS JOIN (VALUES
  ('10001','Cash','asset'),
  ('10002','Bank Account','asset'),
  ('10003','Inventory - Medicines','asset'),
  ('10004','Accounts Receivable','asset'),
  ('20001','Accounts Payable','liability'),
  ('20002','GST Payable','liability'),
  ('30001','Sales Revenue','income'),
  ('30002','Discount Given','income'),
  ('40001','Cost of Goods Sold','expense'),
  ('40002','Salary Expense','expense'),
  ('40003','Rent Expense','expense')
) AS a(code, name, type)
WHERE o.industry_type = 'pharmacy' AND o.id = org_id_param;

Run this in Supabase SQL Editor to bootstrap the entire schema:

-- ============================================================
-- All-in-One ERP — Database Bootstrap
-- ============================================================

-- 1. Core tables
CREATE TABLE organizations (
  id            BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name          TEXT NOT NULL,
  gstin         TEXT UNIQUE,
  pan           TEXT,
  address       JSONB,
  contact       JSONB,
  industry_type TEXT,
  variant       TEXT DEFAULT 'blue',
  settings      JSONB DEFAULT '{}',
  is_active     BOOLEAN DEFAULT TRUE,
  created_at    TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE user_profiles (
  id            UUID PRIMARY KEY REFERENCES auth.users(id) ON DELETE CASCADE,
  org_id        BIGINT REFERENCES organizations(id),
  full_name     TEXT,
  role          TEXT NOT NULL DEFAULT 'staff'
                CHECK (role IN ('admin','accountant','sales','inventory_manager','staff')),
  phone         TEXT,
  avatar_url    TEXT,
  is_active     BOOLEAN DEFAULT TRUE,
  created_at    TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE chart_of_accounts (
  id            BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  org_id        BIGINT REFERENCES organizations(id),
  code          TEXT NOT NULL,
  name          TEXT NOT NULL,
  type          TEXT NOT NULL CHECK (type IN ('asset','liability','income','expense','equity')),
  parent_id     BIGINT REFERENCES chart_of_accounts(id),
  is_active     BOOLEAN DEFAULT TRUE,
  UNIQUE(org_id, code)
);

CREATE TABLE journal_entries (
  id            BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  org_id        BIGINT REFERENCES organizations(id),
  entry_date    DATE NOT NULL DEFAULT CURRENT_DATE,
  reference     TEXT,
  description   TEXT,
  entry_type    TEXT DEFAULT 'manual' CHECK (entry_type IN ('manual','invoice','payment','contra','gst')),
  created_by    UUID REFERENCES auth.users(id),
  created_at    TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE journal_lines (
  id            BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  entry_id      BIGINT REFERENCES journal_entries(id) ON DELETE CASCADE,
  account_id    BIGINT REFERENCES chart_of_accounts(id),
  debit         NUMERIC(14,2) DEFAULT 0,
  credit        NUMERIC(14,2) DEFAULT 0,
  description   TEXT
);

CREATE TABLE parties (
  id            BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  org_id        BIGINT REFERENCES organizations(id),
  type          TEXT NOT NULL CHECK (type IN ('customer','supplier','both')),
  name          TEXT NOT NULL,
  gstin         TEXT,
  pan           TEXT,
  phone         TEXT,
  email         TEXT,
  address       JSONB,
  credit_limit  NUMERIC(14,2),
  opening_bal   NUMERIC(14,2) DEFAULT 0,
  is_active     BOOLEAN DEFAULT TRUE,
  created_at    TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE products (
  id            BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  org_id        BIGINT REFERENCES organizations(id),
  name          TEXT NOT NULL,
  sku           TEXT,
  hsn_code      TEXT,
  sac_code      TEXT,
  unit          TEXT DEFAULT 'nos',
  gst_rate      NUMERIC(5,2) DEFAULT 0,
  cess_rate     NUMERIC(5,2) DEFAULT 0,
  category      TEXT,
  brand         TEXT,
  attributes    JSONB DEFAULT '[]',
  mrp           NUMERIC(12,2),
  selling_price NUMERIC(12,2),
  purchase_price NUMERIC(12,2),
  is_active     BOOLEAN DEFAULT TRUE,
  created_at    TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE inventory_batches (
  id            BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  org_id        BIGINT REFERENCES organizations(id),
  product_id    BIGINT REFERENCES products(id),
  batch_no      TEXT,
  mfg_date      DATE,
  expiry_date   DATE,
  quantity      NUMERIC(12,3) DEFAULT 0,
  cost_price    NUMERIC(12,2),
  location      TEXT,
  is_active     BOOLEAN DEFAULT TRUE
);

CREATE TABLE inventory_serial_numbers (
  id            BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  org_id        BIGINT REFERENCES organizations(id),
  product_id    BIGINT REFERENCES products(id),
  batch_id      BIGINT REFERENCES inventory_batches(id),
  serial_no     TEXT NOT NULL UNIQUE,
  status        TEXT DEFAULT 'available' CHECK (status IN ('available','sold','damaged','returned'))
);

CREATE TABLE invoices (
  id                BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  org_id            BIGINT REFERENCES organizations(id),
  invoice_no        TEXT NOT NULL,
  invoice_date      DATE NOT NULL DEFAULT CURRENT_DATE,
  due_date          DATE,
  customer_id       BIGINT REFERENCES parties(id),
  billing_address   JSONB,
  shipping_address  JSONB,
  gstin             TEXT,
  place_of_supply   TEXT,
  invoice_type      TEXT DEFAULT 'regular' CHECK (invoice_type IN ('regular','export','debit_note','credit_note','proforma')),
  reverse_charge    BOOLEAN DEFAULT FALSE,
  payment_terms     TEXT,
  subtotal          NUMERIC(14,2),
  discount_pct      NUMERIC(5,2) DEFAULT 0,
  discount_amt      NUMERIC(14,2) DEFAULT 0,
  taxable_amt       NUMERIC(14,2),
  cgst_amt          NUMERIC(14,2) DEFAULT 0,
  sgst_amt          NUMERIC(14,2) DEFAULT 0,
  igst_amt          NUMERIC(14,2) DEFAULT 0,
  cess_amt          NUMERIC(14,2) DEFAULT 0,
  total             NUMERIC(14,2),
  status            TEXT DEFAULT 'draft' CHECK (status IN ('draft','sent','paid','overdue','cancelled')),
  irn               TEXT,
  eway_bill_no      TEXT,
  created_by        UUID REFERENCES auth.users(id),
  created_at        TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE invoice_lines (
  id              BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  invoice_id      BIGINT REFERENCES invoices(id) ON DELETE CASCADE,
  product_id      BIGINT REFERENCES products(id),
  batch_id        BIGINT REFERENCES inventory_batches(id),
  description     TEXT,
  quantity        NUMERIC(12,3),
  unit            TEXT,
  rate            NUMERIC(14,2),
  discount_pct    NUMERIC(5,2) DEFAULT 0,
  taxable_amt     NUMERIC(14,2),
  gst_rate        NUMERIC(5,2),
  cgst_amt        NUMERIC(14,2) DEFAULT 0,
  sgst_amt        NUMERIC(14,2) DEFAULT 0,
  igst_amt        NUMERIC(14,2) DEFAULT 0,
  total           NUMERIC(14,2)
);

CREATE TABLE payments (
  id            BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  org_id        BIGINT REFERENCES organizations(id),
  invoice_id    BIGINT REFERENCES invoices(id),
  party_id      BIGINT REFERENCES parties(id),
  amount        NUMERIC(14,2) NOT NULL,
  mode          TEXT DEFAULT 'cash' CHECK (mode IN ('cash','bank','upi','card','cheque','online')),
  reference_no  TEXT,
  payment_date  DATE NOT NULL DEFAULT CURRENT_DATE,
  created_by    UUID REFERENCES auth.users(id),
  created_at    TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE gst_records (
  id              BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  org_id          BIGINT REFERENCES organizations(id),
  return_type     TEXT NOT NULL CHECK (return_type IN ('GSTR1','GSTR2A','GSTR3B','GSTR9')),
  return_period   TEXT NOT NULL,
  invoice_id      BIGINT REFERENCES invoices(id),
  party_id        BIGINT REFERENCES parties(id),
  gstin           TEXT,
  invoice_no      TEXT,
  invoice_date    DATE,
  taxable_amt     NUMERIC(14,2),
  cgst_amt        NUMERIC(14,2) DEFAULT 0,
  sgst_amt        NUMERIC(14,2) DEFAULT 0,
  igst_amt        NUMERIC(14,2) DEFAULT 0,
  status          TEXT DEFAULT 'pending' CHECK (status IN ('pending','matched','mismatched','filed')),
  filed_at        TIMESTAMPTZ
);

CREATE TABLE gst_rates (
  hsn_code      TEXT PRIMARY KEY,
  description   TEXT,
  cgst          NUMERIC(5,2),
  sgst          NUMERIC(5,2),
  igst          NUMERIC(5,2),
  cess          NUMERIC(5,2) DEFAULT 0,
  effective_from DATE,
  effective_to   DATE
);

CREATE TABLE industry_configs (
  id              BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  industry_type   TEXT NOT NULL UNIQUE,
  label           TEXT NOT NULL,
  features        JSONB DEFAULT '[]',
  default_accounts JSONB DEFAULT '[]',
  gst_required    BOOLEAN DEFAULT TRUE,
  is_active       BOOLEAN DEFAULT TRUE
);

CREATE TABLE keepalive (
  id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  pinged_at TIMESTAMPTZ DEFAULT NOW()
);

-- 2. Enable RLS
ALTER TABLE organizations ENABLE ROW LEVEL SECURITY;
ALTER TABLE user_profiles ENABLE ROW LEVEL SECURITY;
ALTER TABLE chart_of_accounts ENABLE ROW LEVEL SECURITY;
ALTER TABLE journal_entries ENABLE ROW LEVEL SECURITY;
ALTER TABLE journal_lines ENABLE ROW LEVEL SECURITY;
ALTER TABLE parties ENABLE ROW LEVEL SECURITY;
ALTER TABLE products ENABLE ROW LEVEL SECURITY;
ALTER TABLE inventory_batches ENABLE ROW LEVEL SECURITY;
ALTER TABLE inventory_serial_numbers ENABLE ROW LEVEL SECURITY;
ALTER TABLE invoices ENABLE ROW LEVEL SECURITY;
ALTER TABLE invoice_lines ENABLE ROW LEVEL SECURITY;
ALTER TABLE payments ENABLE ROW LEVEL SECURITY;
ALTER TABLE gst_records ENABLE ROW LEVEL SECURITY;

-- 3. RLS Policies (org isolation)
CREATE POLICY org_isolation ON organizations
  FOR ALL USING (
    id IN (SELECT org_id FROM user_profiles WHERE id = auth.uid())
  );

CREATE POLICY org_isolation ON products
  FOR ALL USING (
    org_id IN (SELECT org_id FROM user_profiles WHERE id = auth.uid())
  );

CREATE POLICY org_isolation ON invoices
  FOR ALL USING (
    org_id IN (SELECT org_id FROM user_profiles WHERE id = auth.uid())
  );

CREATE POLICY org_isolation ON parties
  FOR ALL USING (
    org_id IN (SELECT org_id FROM user_profiles WHERE id = auth.uid())
  );

CREATE POLICY org_isolation ON chart_of_accounts
  FOR ALL USING (
    org_id IN (SELECT org_id FROM user_profiles WHERE id = auth.uid())
  );

CREATE POLICY org_isolation ON journal_entries
  FOR ALL USING (
    org_id IN (SELECT org_id FROM user_profiles WHERE id = auth.uid())
  );

CREATE POLICY org_isolation ON payments
  FOR ALL USING (
    org_id IN (SELECT org_id FROM user_profiles WHERE id = auth.uid())
  );

-- Admin-specific full access
CREATE POLICY admin_all ON organizations
  FOR ALL USING (
    EXISTS (SELECT 1 FROM user_profiles WHERE id = auth.uid() AND role = 'admin')
  );

-- 4. Seed industry configs
INSERT INTO industry_configs (industry_type, label, features) VALUES
  ('retail','Retail Shop','["billing","inventory","gst"]'),
  ('pharmacy','Pharmacy','["batch","expiry","schedule_h","gst"]'),
  ('fmcg','FMCG','["batch","expiry","distribution","gst"]'),
  ('auto_parts','Auto Parts','["serial_no","interchangeable","gst"]'),
  ('food_beverages','Food & Beverages','["batch","expiry","fssai","gst"]'),
  ('chemical','Chemical','["batch","hazardous","gst"]'),
  ('computer_hardware','Computer Hardware','["serial_no","warranty","gst"]'),
  ('furniture','Furniture','["attributes","dimensions","gst"]'),
  ('book_publishing','Book Publishing','["isbn","author","edition","gst"]'),
  ('travel','Travel','["booking","itinerary","gst"]'),
  ('electrical','Electrical','["serial_no","warranty","gst"]'),
  ('paper_mill','Paper Mill','["batch","weight","gst"]'),
  ('paint','Paint','["batch","shade","gst"]'),
  ('mobile','Mobile','["imei","brand","warranty","gst"]'),
  ('garments','Garments','["size","color","season","gst"]'),
  ('jewellery','Jewellery','["hallmark","carat","weight","gst"]'),
  ('agriculture','Agriculture','["mandi","commission","gst"]'),
  ('stationery','Stationery','["batch","gst"]'),
  ('electronics','Electronics','["serial_no","warranty","gst"]'),
  ('real_estate','Real Estate','["project","unit","rera","gst"]'),
  ('grocery','Grocery','["batch","expiry","gst"]'),
  ('ecommerce','Ecommerce','["order_sync","marketplace","gst"]')
ON CONFLICT (industry_type) DO NOTHING;

-- 5. Seed GST rates (sample)
INSERT INTO gst_rates VALUES
  ('0101','Live animals',2.5,2.5,5,0,'2017-07-01','9999-12-31'),
  ('0402','Milk powder',2.5,2.5,5,0,'2017-07-01','9999-12-31'),
  ('1701','Sugar',2.5,2.5,5,0,'2017-07-01','9999-12-31'),
  ('2710','Petroleum',NULL,NULL,NULL,0,'2017-07-01','9999-12-31'),
  ('3004','Pharmaceuticals',6,6,12,0,'2017-07-01','9999-12-31'),
  ('6204','Women garments',6,6,12,0,'2017-07-01','9999-12-31'),
  ('8471','Computers',9,9,18,0,'2017-07-01','9999-12-31'),
  ('8517','Mobile phones',9,9,18,0,'2017-07-01','9999-12-31'),
  ('9999','Services (general)',9,9,18,0,'2017-07-01','9999-12-31')
ON CONFLICT (hsn_code) DO NOTHING;

-- 6. Functions

-- Get next invoice number
CREATE OR REPLACE FUNCTION next_invoice_no(org_id_param BIGINT)
RETURNS TEXT
LANGUAGE plpgsql
SECURITY DEFINER
AS $$
DECLARE
  prefix TEXT;
  next_num INT;
BEGIN
  SELECT COALESCE(SUBSTRING(settings->>'invoice_prefix' FROM '^(.+)$'), 'INV-')
  INTO prefix FROM organizations WHERE id = org_id_param;
  SELECT COALESCE(MAX(CAST(SPLIT_PART(invoice_no, '-', 2) AS INTEGER)), 0) + 1
  INTO next_num FROM invoices WHERE org_id = org_id_param;
  RETURN prefix || LPAD(next_num::TEXT, 5, '0');
END;
$$;

-- GST auto-calculation
CREATE OR REPLACE FUNCTION calculate_gst(
  taxable_amt NUMERIC,
  gst_rate NUMERIC,
  place_of_supply TEXT,
  org_state TEXT
) RETURNS JSONB
LANGUAGE plpgsql
IMMUTABLE
AS $$
DECLARE
  cgst NUMERIC;
  sgst NUMERIC;
  igst NUMERIC;
BEGIN
  IF place_of_supply = org_state THEN
    cgst := ROUND(taxable_amt * gst_rate / 200, 2);
    sgst := cgst;
    igst := 0;
  ELSE
    cgst := 0;
    sgst := 0;
    igst := ROUND(taxable_amt * gst_rate / 100, 2);
  END IF;
  RETURN jsonb_build_object('cgst', cgst, 'sgst', sgst, 'igst', igst);
END;
$$;

-- 7. Indexes
CREATE INDEX idx_products_org ON products(org_id);
CREATE INDEX idx_invoices_org ON invoices(org_id);
CREATE INDEX idx_invoices_customer ON invoices(customer_id);
CREATE INDEX idx_parties_org ON parties(org_id);
CREATE INDEX idx_journal_entries_org ON journal_entries(org_id);
CREATE INDEX idx_inventory_batches_product ON inventory_batches(product_id);
CREATE INDEX idx_inventory_batches_expiry ON inventory_batches(expiry_date);
CREATE INDEX idx_gst_records_period ON gst_records(org_id, return_period);
CREATE INDEX idx_invoices_irn ON invoices(irn);
CREATE INDEX idx_user_profiles_org ON user_profiles(org_id);

┌─────────────────────────────────────────────────────────────┐
│                    Deployment Pipeline                        │
├─────────────────────────────────────────────────────────────┤
│  GitHub → GitHub Actions → Supabase (DB + Edge Fns)         │
│                           → Vercel (Frontend)                │
│                           → Flutter Build (App Store/Play)   │
└─────────────────────────────────────────────────────────────┘

Infrastructure:
├── Supabase (Managed PostgreSQL + Auth + Storage + Realtime)
├── Vercel (Frontend - React, SSR for SEO pages)
├── Supabase Edge Functions (Deno - lightweight APIs)
├── Node.js Microservices (Fastify - complex business logic)
│   ├── gst-engine        (e-invoice, e-way bill, GSTR)
│   ├── ecommerce-sync    (marketplace order sync)
│   └── report-generator  (PDF, Excel exports)
├── AWS S3 / Supabase Storage (backups, reports)
└── Cloudflare (DNS, CDN, DDoS protection)

Scaling:
├── Horizontal: Supabase auto-scales PostgreSQL read replicas
├── Edge Functions scale per-request (Deno isolates)
├── Vercel: Global CDN with edge caching
├── Connection pooling via PgBouncer (Supabase managed)
└── Background jobs via pg_cron or Edge Function queues

• Row-Level Security on every table — users only see their org's data
• Supabase Auth with JWT — no raw passwords stored
• MFA support via Supabase Auth (TOTP)
• API rate limiting at Edge Function level
• Audit logs — all financial mutations logged to audit_log table
• Data encryption at rest (Supabase-managed)
• HTTPS enforced across all endpoints
• GDPR / IT Act compliance — data export & delete APIs
• Backup — Supabase daily backups, point-in-time recovery