ERP_PostrgreSQL

Tables (11)

materials

  • id — SERIAL
  • tenant_id — VARCHAR(50) — Foreign key → tenants.id — NOT NULL
  • code — VARCHAR(50)
  • name — VARCHAR(100)
  • specification — VARCHAR(200)
  • unit — VARCHAR(20)
  • unit_price — DECIMAL(15,2)
  • stock_quantity — DECIMAL(15,2) — NOT NULL
  • warehouse — VARCHAR(50)
  • supplier_id — INTEGER — Foreign key → partners.id
  • created_by — VARCHAR(50) — Foreign key → users.id
  • created_at — TIMESTAMP — NOT NULL
  • updated_at — TIMESTAMP — NOT NULL

metadata

  • id — SERIAL
  • tenant_id — VARCHAR(50) — Foreign key → tenants.id — NOT NULL
  • scope — VARCHAR(20) — NOT NULL
  • business_type — VARCHAR(20)
  • user_id — VARCHAR(50) — Foreign key → users.id
  • meta_key — VARCHAR(200) — NOT NULL
  • config — JSONB — NOT NULL
  • created_at — TIMESTAMP — NOT NULL
  • updated_at — TIMESTAMP — NOT NULL

partners

  • id — SERIAL
  • tenant_id — VARCHAR(50) — Foreign key → tenants.id — NOT NULL
  • code — VARCHAR(50)
  • name — VARCHAR(100)
  • business_number — VARCHAR(20)
  • representative — VARCHAR(50)
  • contact — VARCHAR(50)
  • address — TEXT
  • email — VARCHAR(100)
  • partner_type — VARCHAR(10)
  • created_by — VARCHAR(50) — Foreign key → users.id
  • created_at — TIMESTAMP — NOT NULL
  • updated_at — TIMESTAMP — NOT NULL

products

  • id — SERIAL
  • tenant_id — VARCHAR(50) — Foreign key → tenants.id — NOT NULL
  • code — VARCHAR(50)
  • name — VARCHAR(100)
  • specification — VARCHAR(200)
  • unit — VARCHAR(20)
  • selling_price — DECIMAL(15,2)
  • cost_price — DECIMAL(15,2)
  • stock_quantity — DECIMAL(15,2) — NOT NULL
  • warehouse — VARCHAR(50)
  • category — VARCHAR(50)
  • is_active — BOOLEAN — NOT NULL
  • created_by — VARCHAR(50) — Foreign key → users.id
  • created_at — TIMESTAMP — NOT NULL
  • updated_at — TIMESTAMP — NOT NULL

purchase_items

  • id — SERIAL
  • purchase_id — INTEGER — Foreign key → purchases.id — NOT NULL
  • tenant_id — VARCHAR(50) — Foreign key → tenants.id — NOT NULL
  • material_id — INTEGER — Foreign key → materials.id
  • quantity — DECIMAL(15,2)
  • unit_price — DECIMAL(15,2)
  • amount — DECIMAL(15,2)
  • warehouse — VARCHAR(50)
  • sort_order — INTEGER — NOT NULL

purchases

  • id — SERIAL
  • tenant_id — VARCHAR(50) — Foreign key → tenants.id — NOT NULL
  • purchase_number — VARCHAR(50)
  • purchase_date — DATE
  • partner_id — INTEGER — Foreign key → partners.id
  • contact_info — VARCHAR(50)
  • status — VARCHAR(20)
  • total_amount — DECIMAL(15,2) — NOT NULL
  • memo — TEXT
  • created_by — VARCHAR(50) — Foreign key → users.id
  • created_at — TIMESTAMP — NOT NULL
  • updated_at — TIMESTAMP — NOT NULL

sale_items

  • id — SERIAL
  • sale_id — INTEGER — Foreign key → sales.id — NOT NULL
  • tenant_id — VARCHAR(50) — Foreign key → tenants.id — NOT NULL
  • product_id — INTEGER — Foreign key → products.id
  • quantity — DECIMAL(15,2)
  • unit_price — DECIMAL(15,2)
  • amount — DECIMAL(15,2)
  • warehouse — VARCHAR(50)
  • sort_order — INTEGER — NOT NULL

sales

  • id — SERIAL
  • tenant_id — VARCHAR(50) — Foreign key → tenants.id — NOT NULL
  • sale_number — VARCHAR(50)
  • sale_date — DATE
  • partner_id — INTEGER — Foreign key → partners.id
  • contact_info — VARCHAR(50)
  • status — VARCHAR(20)
  • total_amount — DECIMAL(15,2) — NOT NULL
  • memo — TEXT
  • created_by — VARCHAR(50) — Foreign key → users.id
  • created_at — TIMESTAMP — NOT NULL
  • updated_at — TIMESTAMP — NOT NULL

sequences

  • id — SERIAL
  • tenant_id — VARCHAR(50) — Foreign key → tenants.id — NOT NULL
  • seq_type — VARCHAR(50) — NOT NULL
  • seq_date — DATE — NOT NULL
  • last_seq — INTEGER — NOT NULL
  • UNIQUE(tenant_id, — seq_type,

tenants

  • id — VARCHAR(50)
  • name — VARCHAR(100) — NOT NULL
  • created_at — TIMESTAMP — NOT NULL

users

  • id — VARCHAR(50)
  • tenant_id — VARCHAR(50) — Foreign key → tenants.id — NOT NULL
  • login_id — VARCHAR(50) — NOT NULL
  • password — VARCHAR(200) — NOT NULL
  • name — VARCHAR(100) — NOT NULL
  • role — VARCHAR(20) — NOT NULL
  • created_at — TIMESTAMP — NOT NULL
  • UNIQUE(tenant_id, — login_id)