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