ഈ ലേഖനം ഇന്ത്യൻ ഫിൻടെക്, ഇ-കൊമേഴ്സ് ആപ്ലിക്കേഷനുകൾക്കുള്ള ഡേറ്റാബേസ് ഡിസൈൻ മികച്ച രീതികൾ വിവരിക്കുന്നു — GST-അവബോധമുള്ള സ്കീമ (IGST/CGST/SGST), UPI ഇടപാട് ഘടന, DECIMAL(15,2) ഉപയോഗിച്ചുള്ള ധനപരമായ ഡേറ്റ ടൈപ്പുകൾ, ഓഡിറ്റ് ട്രയലുകൾ, DPDP ആക്ടിന് അനുസൃതമായ ഡേറ്റ നിലനിർത്തൽ നയം. FLOAT ഉപയോഗിച്ചതിന്റെ ഫലമായി ₹4 ലക്ഷം ചെലവഴിക്കേണ്ടി വന്ന ഒരു കേരള ഫിൻടെക് കമ്പനിയുടെ യഥാർത്ഥ കഥ.
A Kerala fintech startup discovered their database design error 18 months after launch: all amounts were stored as FLOAT instead of DECIMAL. Floating-point rounding errors had accumulated across 50,000+ transactions. Reconciliation took 3 weeks and cost ₹4 lakhs. This guide covers 12 database design decisions — from GST schemas to DPDP retention — that separate sustainable Indian fintech and e-commerce applications from ones that create expensive technical debt.
India-Specific Database Requirements That Western Tutorials Skip
Most database design tutorials are written for US or European markets and assume a simple tax model, two or three payment methods, and a single currency. Indian fintech and e-commerce applications operate under fundamentally different constraints: a dual-tax structure (IGST vs CGST+SGST) that changes per transaction, 8-10 active payment methods with distinct identifier formats, six-digit pincodes with state-of-supply implications, and regulated identifiers (PAN, GSTIN) with checksum validation requirements.
Getting these design decisions wrong at launch creates a specific kind of technical debt — one that is difficult to migrate out of because financial records must remain historically accurate even as your schema evolves. The 12 design decisions below address the most common mistakes Kerala developers make when building fintech and e-commerce applications for the Indian market.
GST-Aware Schema Design
An invoice table built without GST awareness creates immediate problems. A generic "tax_amount" column cannot represent the IGST/CGST/SGST split that Indian accounting software (Tally, Zoho Books, QuickBooks India) requires for GST returns. Your invoice table needs at minimum: base_amount DECIMAL(15,2), igst_amount DECIMAL(15,2), cgst_amount DECIMAL(15,2), sgst_amount DECIMAL(15,2), total_amount DECIMAL(15,2), and a tax_type ENUM('IGST', 'CGST_SGST') column that records which regime applied to this invoice.
The determination of whether IGST or CGST+SGST applies depends on the supplier state and buyer state. Store GST state codes (Kerala = 32, Karnataka = 29, Tamil Nadu = 33, Maharashtra = 27) in your address tables, and compute the tax type at order creation time — not at report generation time. Historical invoices must remain static; retroactive recalculation when the company expands to new states creates accounting discrepancies.
For businesses handling multiple GST slabs (5%, 12%, 18%, 28%), store the applicable HSN code and GST rate on each product record. The invoice line item table should record the rate that applied at purchase time, not reference the product's current rate — product GST rates do change with government notifications.
Monetary Data Types: DECIMAL Not FLOAT
FLOAT and DOUBLE are binary floating-point types — they cannot exactly represent decimal fractions. 0.1 + 0.2 in a FLOAT column does not equal exactly 0.3. For a single transaction this error is sub-paisa. Across 50,000 transactions with multiple line items, the rounding error compounds into visible discrepancies — exactly what the Kerala fintech startup discovered at ₹4 lakhs of reconciliation cost.
The correct types for every monetary column in an Indian fintech or e-commerce application:
- INR transaction amounts:
DECIMAL(15,2)— supports up to ₹999,999,999,999.99, sufficient for any B2C or SMB transaction. - Exchange rates:
DECIMAL(15,6)— 6 decimal places prevent rounding errors in multi-currency conversions. - GST component amounts:
DECIMAL(15,2)— always validate thatigst_amount = base_amount * (gst_rate/100)before insert, with a CHECK constraint or application-layer assertion. - Quantities and unit prices:
DECIMAL(15,4)— handles fractional units in commodity and manufacturing contexts.
For multi-currency SaaS serving global customers, add amount_usd DECIMAL(15,4) and exchange_rate DECIMAL(15,6) alongside the INR amount. Store the exchange rate at transaction time — never recalculate historical exchange rates from current rates.
UPI and Indian Payment Method Schema
An Indian payment table needs a payment_method column that accounts for the actual diversity of Indian payments: ENUM('upi', 'card_debit', 'card_credit', 'net_banking', 'wallet_paytm', 'wallet_phonepe', 'emi', 'bnpl', 'cod', 'neft', 'rtgs', 'cheque'). Using a generic payment_type VARCHAR column invites inconsistent values and makes reconciliation queries fragile.
UPI transactions have a UTR (Unique Transaction Reference) number — a 12-digit alphanumeric identifier issued by the acquiring bank. This is your idempotency key for UPI payments. Include a upi_ref_id VARCHAR(50) column with a UNIQUE constraint. Payment gateways like Razorpay and PayU return this as the UTR. Storing it enables reconciliation with bank statements and prevents duplicate processing.
For addresses, store pincode VARCHAR(6) not CHAR(6) — while all current Indian pincodes are 6 digits, this preserves future-proofing. More critically, pincodes beginning with 011 (Delhi) would be silently truncated to 11 in some CHAR implementations. Derive the state code from the pincode at insert time using a pincode-to-state lookup — this avoids the common bug where a user selects "Kerala" in the state dropdown but provides a Karnataka pincode.
PAN, GSTIN, and Regulated Identifier Storage
B2B invoicing requires buyer GSTIN and in some transactions buyer PAN. These identifiers have specific formats and checksums. GSTIN is 15 characters: the first two characters are the state code (e.g., 32 for Kerala), the next 10 are the PAN, character 13 is the entity number within PAN, character 14 is always 'Z', and character 15 is a checksum. PAN is 10 characters with the format AAAAA9999A (5 alpha, 4 numeric, 1 alpha).
Store both as VARCHAR — gstin VARCHAR(15), pan VARCHAR(10) — and validate at the application layer before insert. Do not rely solely on regex validation; a correctly formatted GSTIN with a wrong checksum will pass regex but fail GSTN (GST Network) verification. Validate the checksum before storing. This prevents storing invalid GSTINs that will cause GST return filing failures months later when your customer tries to claim input tax credit.
Audit Trail Design for DPDP and Companies Act Compliance
The DPDP Act and Companies Act 2013 both require that you can demonstrate what happened to data — who changed what, when, and from which context. A well-designed audit trail table covers all three. The recommended structure: id BIGSERIAL PRIMARY KEY, table_name VARCHAR(100) NOT NULL, record_id VARCHAR(100) NOT NULL, operation ENUM('INSERT','UPDATE','DELETE') NOT NULL, old_values JSONB, new_values JSONB, changed_by VARCHAR(100), changed_at TIMESTAMPTZ DEFAULT NOW(), ip_address INET, session_id VARCHAR(200).
PostgreSQL triggers can auto-populate this table for any INSERT, UPDATE, or DELETE on sensitive tables. The JSONB old_values and new_values columns capture the full row state before and after the change — this gives you a complete change history without needing point-in-time database backups for audit purposes. Index on (table_name, record_id, changed_at) to support common audit queries efficiently.
Never truncate audit tables — they are legal records. Archive to cold storage (AWS Glacier or similar) after 2 years, but do not delete. The Companies Act 2013 requires financial record retention for 8 years.
DPDP-Compliant Data Retention and Deletion Architecture
The DPDP Act requires deletion or anonymisation of personal data when the purpose of collection is fulfilled and when no longer legally required. The design challenge: transaction records must be retained for Companies Act compliance (8 years), but the personal data attached to those transactions must be deletable when consent lapses.
The solution is schema separation: create a user_personal_data table containing only PII columns — name, email, phone, date of birth, address. Your main users table retains non-PII identifiers and preferences. Transactions link to users.id (a pseudonymous UUID), not directly to email or phone. When a user requests deletion or their purpose period expires, you soft-delete the user_personal_data row and replace PII columns with anonymised tokens (DELETED_USER_8472xk as email, null phone). The transaction records remain intact for compliance — they reference a user UUID that no longer maps to identifiable information.
This architecture requires planning from day one. Retrofitting it into a schema where orders.customer_email is a direct column (rather than a join through users.id) requires a multi-month migration with high risk of data consistency errors.
Indexing for Indian E-Commerce Query Patterns
Generic database optimisation advice focuses on primary key lookups and single-column indexes. Indian e-commerce applications have specific multi-column query patterns that need covering indexes designed upfront.
The four highest-priority composite indexes for a typical Indian e-commerce application:
- Order history queries:
CREATE INDEX idx_orders_user_date ON orders (user_id, created_at DESC)— supportsWHERE user_id = ? ORDER BY created_at DESC LIMIT 20with no sequential scan. - Catalogue listing queries:
CREATE INDEX idx_products_category_active ON products (category_id, is_active, sort_order)— supports paginated category pages without a full table scan. - Payment reconciliation:
CREATE INDEX idx_payments_method_status_date ON payments (payment_method, status, created_at)— supports daily reconciliation queries by payment method and status. - Checkout address lookup:
CREATE INDEX idx_addresses_user_default ON addresses (user_id, is_default)— ensures the checkout default address lookup is a single-row index seek, not a table scan.
Analyse your slow query log quarterly. Indian e-commerce traffic is highly concentrated in evening hours (7-10 PM) and peaks during sale events — add query analysis as part of pre-sale preparation, not post-mortem.
PostgreSQL vs MySQL for Indian Startups
Both PostgreSQL and MySQL are viable for Indian fintech and e-commerce. The choice should be based on your team's existing expertise, not on abstract performance benchmarks. That said, PostgreSQL has specific advantages for Indian business applications: native JSONB type (useful for flexible GST metadata, product attributes, and payment gateway response storage), row-level security (critical for multi-tenant SaaS where tenant data isolation is a compliance requirement), and native UUID type (better than VARCHAR(36) for primary keys in distributed systems).
MySQL's advantage is familiarity — the majority of Kerala developers have more MySQL experience, and many shared hosting providers (still used by small businesses) offer MySQL but not PostgreSQL. If your team has zero PostgreSQL experience and a tight timeline, MySQL with InnoDB and proper schema design will perform adequately. The schema design decisions in this guide apply equally to both databases.
For new greenfield projects with a team open to learning: choose PostgreSQL. For projects integrating with existing MySQL infrastructure: stay on MySQL. For a broader architecture view of SaaS applications built for the Indian market, see the guide to building SaaS products in Kerala. If you need custom database architecture review for your fintech or e-commerce product, the custom software development service includes database design consulting.
Frequently Asked Questions
What data type should be used for monetary values in Indian fintech applications?
All monetary values in Indian fintech applications must use DECIMAL (also called NUMERIC in PostgreSQL) with at least 2 decimal places for INR amounts, and 4-6 decimal places for exchange rates and crypto/commodity prices. The standard recommendation is DECIMAL(15,2) for INR consumer transaction amounts and DECIMAL(15,4) for exchange rates and calculations involving currency conversion. Never use FLOAT or DOUBLE for monetary values — their binary floating-point representation cannot exactly represent decimal fractions, causing rounding errors that accumulate across many transactions. For GST calculations where you need to split a total into IGST/CGST/SGST components, use DECIMAL(15,2) for each component and validate that the sum of components equals the total before insertion.
How should an Indian e-commerce database handle GST calculations for inter-state vs intra-state transactions?
GST calculation logic in Indian e-commerce databases must determine whether a transaction is intra-state (supplier and buyer in the same state) or inter-state (different states) to split tax correctly. The supplier state is fixed (your company's GST registration state), but the buyer state changes with each transaction. Store state codes in your address table (Maharashtra = 27, Kerala = 32, Karnataka = 29, etc., as per GST state codes). At order creation: if supplier_state_code equals buyer_state_code, apply CGST (half the GST rate) + SGST (half the GST rate). If different, apply full IGST. This logic must be implemented at the application layer before insert — do not calculate it retroactively. Store the determined tax type (IGST or CGST+SGST) in the invoice record so historical invoices remain valid even if the company later opens a branch in a new state.
How long must an Indian fintech company retain database records under DPDP and Companies Act?
Indian fintech companies must navigate two overlapping retention requirements. The Companies Act 2013 requires financial records retention for 8 years from the end of the relevant financial year — this covers all transaction records, invoices, and accounting entries. The DPDP Act 2023 requires deletion of personal data when the purpose for which it was collected is fulfilled and when no longer legally required. The practical reconciliation: transaction records (amounts, dates, payment methods, invoice numbers) must be retained for 8 years for Companies Act compliance. The personal identifiers attached to those transactions (name, email, phone, address) can be anonymized after the purpose period (typically subscription termination + 1 year) while keeping the transaction shell intact for financial compliance. Design your database schema to support this separation from day one — retrofitting anonymization into a schema that conflates personal data with financial records is expensive.