Database Schema

Database Documentation

Complete database schema reference for ExportReady-Battery. The database is PostgreSQL hosted on Supabase.

Entity Relationships


┌──────────────┐       ┌──────────────┐       ┌──────────────┐
│   TENANTS    │       │   BATCHES    │       │  PASSPORTS   │
├──────────────┤       ├──────────────┤       ├──────────────┤
│ id (PK)      │──┐    │ id (PK)      │──┐    │ uuid (PK)    │
│ company_name │  │    │ tenant_id(FK)│◄─┘    │ batch_id(FK) │◄─┐
│ email (UK)   │  │    │ batch_name   │       │ serial_number│  │
│ password_hash│  └───►│ specs (JSONB)│       │ manufacture_ │  │
│ epr_number   │       │ market_region│       │   date       │  │
│ bis_r_number │       │ pli_compliant│       │ status       │  │
│ iec_code     │       │ domestic_va  │       │ created_at   │  │
│ created_at   │       │ created_at   │       └──────────────┘  │
└──────────────┘       └──────────────┘                         │
                                                                │
┌──────────────┐       ┌──────────────┐                         │
│  TEMPLATES   │       │ SCAN_EVENTS  │                         │
├──────────────┤       ├──────────────┤                         │
│ id (PK)      │       │ id (PK)      │                         │
│ tenant_id(FK)│       │ passport_id  │─────────────────────────┘
│ name         │       │   (FK)       │
│ specs (JSONB)│       │ ip_address   │
│ created_at   │       │ city         │
└──────────────┘       │ country      │
                       │ device_type  │
                       │ scanned_at   │
                       └──────────────┘

PK = Primary Key, FK = Foreign Key, UK = Unique Key

Table Specifications

tenants

Stores company/organization information for multi-tenant support

Column	Type	Nullable	Description
id	UUID	NOT NULL	Primary key, auto-generated
company_name	VARCHAR(255)	NOT NULL	Company display name
email	VARCHAR(255)	NOT NULL	Unique login email
password_hash	VARCHAR(255)	NOT NULL	bcrypt hashed password
address	TEXT	NULL	Company address
logo_url	VARCHAR(500)	NULL	Company logo URL
support_email	VARCHAR(255)	NULL	Support contact email
website	VARCHAR(255)	NULL	Company website
epr_registration_number	VARCHAR(100)	NULL	CPCB EPR registration
bis_r_number	VARCHAR(50)	NULL	BIS CRS R-number
iec_code	VARCHAR(20)	NULL	Import Export Code
reset_token	VARCHAR(255)	NULL	Password reset token
reset_token_expires	TIMESTAMPTZ	NULL	Token expiry time
quota_balance	INT	NOT NULL	Passport quota remaining
created_at	TIMESTAMPTZ	NOT NULL	Account creation time

batches

Production batches containing battery specifications

Column	Type	Nullable	Description
id	UUID	NOT NULL	Primary key
tenant_id	UUID	NOT NULL	FK to tenants.id
batch_name	VARCHAR(100)	NOT NULL	User-defined name
specs	JSONB	NOT NULL	Battery specifications
market_region	VARCHAR(20)	NOT NULL	INDIA, EU, or GLOBAL
pli_compliant	BOOLEAN	NULL	PLI eligibility (India)
domestic_value_add	DECIMAL(5,2)	NULL	DVA percentage
cell_source	VARCHAR(20)	NULL	IMPORTED or DOMESTIC
bill_of_entry_no	VARCHAR(50)	NULL	Customs entry no.
country_of_origin	VARCHAR(100)	NULL	Cell origin country
customs_date	DATE	NULL	Customs clearance date
deleted_at	TIMESTAMPTZ	NULL	Soft delete timestamp
created_at	TIMESTAMPTZ	NOT NULL	Batch creation time

passports

Individual battery passports with unique identifiers

Column	Type	Nullable	Description
uuid	UUID	NOT NULL	Primary key, passport ID
batch_id	UUID	NOT NULL	FK to batches.id
serial_number	VARCHAR(100)	NOT NULL	Battery serial no.
manufacture_date	DATE	NOT NULL	Manufacturing date
status	VARCHAR(50)	NOT NULL	CREATED, ACTIVE, SHIPPED, IN_SERVICE, RECALLED, RECYCLED, END_OF_LIFE
created_at	TIMESTAMPTZ	NOT NULL	Creation timestamp

templates

Reusable battery specification templates

Column	Type	Nullable	Description
id	UUID	NOT NULL	Primary key
tenant_id	UUID	NOT NULL	FK to tenants.id
name	VARCHAR(100)	NOT NULL	Template name
specs	JSONB	NOT NULL	Saved specifications
created_at	TIMESTAMPTZ	NOT NULL	Creation timestamp

scan_events

QR code scan tracking with geolocation

Column	Type	Nullable	Description
id	UUID	NOT NULL	Primary key
passport_id	UUID	NOT NULL	FK to passports.uuid
ip_address	VARCHAR(45)	NULL	Scanner IP address
city	VARCHAR(100)	NULL	Geolocated city
country	VARCHAR(100)	NULL	Geolocated country
device_type	VARCHAR(50)	NULL	Mobile/Desktop
user_agent	TEXT	NULL	Browser user agent
scanned_at	TIMESTAMPTZ	NOT NULL	Scan timestamp

transactions

Quota purchase and usage history

Column	Type	Nullable	Description
id	UUID	NOT NULL	Primary key
tenant_id	UUID	NOT NULL	FK to tenants.id
description	VARCHAR(255)	NOT NULL	Reason for transaction
quota_change	INT	NOT NULL	Quota amount (+/-)
batch_id	UUID	NULL	Related batch (optional)
created_at	TIMESTAMPTZ	NOT NULL	Transaction time

JSONB Schema: specs

The specs column stores battery specifications as JSONB:

BatchSpec JSONB Structure

json

{
  "chemistry": "Li-ion NMC",
  "voltage": "48V",
  "capacity": "100Ah",
  "manufacturer": "Acme Batteries",
  "manufacturer_address": "Industrial Area, Delhi",
  "weight": "45kg",
  "carbon_footprint": "45 kg CO2e/kWh",
  "country_of_origin": "India",
  "recyclable": true,
  
  // EU-specific fields
  "materials": {
    "cobalt": { "percentage": 8.5, "recycled": 12 },
    "lithium": { "percentage": 3.2, "recycled": 4 },
    "nickel": { "percentage": 25.0, "recycled": 8 }
  },
  "certifications": ["CE", "UN38.3"],
  "eu_representative": "EU Rep GmbH",
  "eu_representative_email": "contact@eurep.eu"
}

Indexes

Database Indexes

sql

-- Performance indexes
CREATE INDEX idx_batches_tenant_id ON batches(tenant_id);
CREATE INDEX idx_passports_batch_id ON passports(batch_id);
CREATE INDEX idx_passports_serial_number ON passports(serial_number);
CREATE INDEX idx_passports_status ON passports(status);
CREATE INDEX idx_scan_events_passport_id ON scan_events(passport_id);
CREATE INDEX idx_scan_events_scanned_at ON scan_events(scanned_at);

-- Unique constraints
CREATE UNIQUE INDEX idx_tenants_email ON tenants(email);
CREATE UNIQUE INDEX idx_passports_batch_serial 
  ON passports(batch_id, serial_number);

-- Password reset lookup
CREATE INDEX idx_tenants_reset_token ON tenants(reset_token) 
  WHERE reset_token IS NOT NULL;

Migration History

000001initial_schemaCore tables: tenants, batches, passports

000002create_batch_templatesTemplates table for saved specs

000003create_scan_eventsQR scan tracking table

000004add_dual_modeMarket region, PLI, DVA fields

000005add_tenant_detailsProfile fields (address, website)

000006add_soft_delete_batchesdeleted_at for soft delete

000007add_india_complianceEPR, BIS, IEC, customs fields

000008add_quota_systemQuota balance and transactions

000009add_password_resetReset token fields