Vyapar Migration

Vyapar Schema Skill

Author: NexaraTech | Version: 1.0.0 | Vyapar: 5.x Android | License: MIT Tools: Bash, Read, Write, Edit | Dependencies: Python 3.x + sqlite3 (stdlib), unzip or Python zipfile Evidence: Live .vyp SQLite backups + full jadx 1.5.5 DEX decompilation (classes8.dex)

This skill contains only verified facts about Vyapar's internal database. It has no opinions about what to migrate to. The agent using this skill decides what to do with the data — this skill ensures they start with accurate source knowledge.

File Format

• Vyapar backup extension: .vyb — a standard ZIP archive
• Inside the ZIP: a single .vyp file — a SQLite 3 database
• Extract: unzip backup.vyb → produces <name>.vyp
• Open: sqlite3 <name>.vyp or DB Browser for SQLite
• There is always exactly one .vyp per .vyb

Architecture

• Single-company, single-user accounting + inventory app
• One SQLite file = one company's complete data
• No multi-tenancy, no foreign server, no hidden tables
• All financial data lives in kb_transactions + kb_lineitems
• All party data lives in kb_names
• All product data lives in kb_items

Reference Files

Load on demand — not all at once:

Core Tables — Quick Reference

kb_firms              — company profile, GSTIN, address
kb_names              — all parties (customers + vendors)
kb_items              — products and services
kb_item_categories    — product categories
kb_item_units         — units of measure
kb_transactions       — every transaction header
kb_lineitems          — line items for each transaction
kb_tax_code           — GST and other tax definitions
kb_paymentTypes       — payment methods (Cash, Cheque, UPI, Bank)
kb_prefix             — invoice prefix registry; maps txn_prefix_id → prefix string per txn_type
txn_payment_mapping   — links payment methods to transactions
kb_txn_links          — links payments to invoices (optional, settings-driven)
kb_linked_transactions— links converted documents (e.g. estimate → invoice)
journal_entry         — manual journal entries
journal_entry_line_items — lines for manual journals
kb_serial_details     — serial/IMEI master registry (one row per tracked unit)
kb_serial_mapping     — links serials to lineitems (sale) or adjustments (stock-in)
kb_item_stock_tracking— stock lot/batch tracking (empty in live data — use kb_serial_details)
other_accounts        — chart of accounts (non-party accounts)
party_to_party_transfer — inter-party transfers (separate from kb_transactions)

Transaction Types — Critical Mapping

Always read references/enums.md for the full table. Key types:

Non-financial types (24, 27, 28, 30, 65, 83) do not affect balances unless txn_status=4 (converted). Confirmed by SQL: WHERE txn_type NOT IN (24,28,30,27,83,70).

Amount Fields — Exact Meaning

txn_cash_amount       — amount already collected/paid
txn_balance_amount    — amount still outstanding
txn_ac1_amount / txn_ac2_amount / txn_ac3_amount — additional charges (freight, etc.)
txn_tax_amount        — total tax
txn_discount_amount   — transaction-level discount

Invoice total = txn_cash_amount + txn_balance_amount ⚠️ txn_total_amount does NOT exist — do not use it.

Full gross cross-check:

SUM(total_amount)
+ txn_ac1_amount + txn_ac2_amount + txn_ac3_amount
+ txn_tax_amount
- txn_discount_amount
+ txn_round_off_amount
= txn_cash_amount + txn_balance_amount

Tax Logic

• txn_tax_inclusive=1 → price INCLUDES tax
• txn_tax_inclusive=2 → price EXCLUDES tax (most common)
• GST components stored as separate rows in kb_lineitems joined to kb_tax_code
• txn_place_of_supply determines inter-state (IGST) vs intra-state (CGST+SGST)
• If txn_place_of_supply is empty → default to intra-state

Party Rules

• name_type=1 = Customer, name_type=2 = Vendor
• A single name_id can appear in both types — same entity, dual role
• Unique key for deduplication: name_id (internal) or full_name + phone_number
• GSTIN stored in name_gstin_number — may be blank for unregistered parties
• name_is_active = 1 (active), 0 (archived) — ALWAYS filter WHERE name_is_active = 1 unless you need archived parties
• kb_names.amount = cached outstanding balance snapshot — unreliable, do not use; derive balance from SUM(txn_cash_amount + txn_balance_amount) on transactions instead

Payment Linking

• txn_payment_mapping.payment_id → kb_paymentTypes.paymentType_id This is the payment METHOD (Cash=1, Cheque=2) — NOT a linked payment transaction
• kb_txn_links — optionally links payment transactions to invoices This link is user-controlled via a settings toggle — NOT guaranteed to exist
• kb_linked_transactions — links converted documents (estimate → invoice, order → invoice)

Stock

• kb_items.item_stock_quantity = current snapshot quantity
• For historical opening quantity: use kb_item_stock_tracking.ist_opening_quantity
• Negative stock is possible — Vyapar allows sales without stock enforcement

Data Quality — Known Issues

Read references/data-rules.md for the full SQL validation suite. Key issues:

• Lineitems can exist without a matching transaction (orphaned rows)
• Parties can exist without GSTIN or phone (minimal required fields)
• kb_lineitems.lineitem_serial_number — column exists but is always empty in live data. For serial/IMEI tracking use kb_serial_details + kb_serial_mapping (see references/schema.md)
• Duplicate GSTIN across parties is possible (data entry error)
• txn_place_of_supply is frequently blank

Full-Text Search (FTS) Engine

Source: Verified by jadx 1.5.5 full decompilation of Vyapar APK classes8.dex. Exact class mapping:

Tables

kb_fts_vtable          — SQLite FTS3 virtual table (search interface)
kb_fts_vtable_content  — physical backing store (columns: docid, c0fts_name_id, c1fts_txn_id, c2fts_text)
kb_fts_vtable_segdir   — FTS index segments directory
kb_fts_vtable_segments — FTS index segment data

DDL:

CREATE VIRTUAL TABLE kb_fts_vtable USING fts3 (fts_name_id, fts_txn_id, fts_text)

Important: When querying the virtual table use logical column names (fts_txn_id). When querying the physical backing table use storage column names (c0fts_name_id, c1fts_txn_id, c2fts_text).

fts_text Blob Format — Exact Field Order

Verified from TxnDbManager.updateFTSTxnRecord() (x31.o.e(), line 438–481):

{partyName} {displayName} {description} {cashAmount} {balanceAmount} {totalAmount} {prefix}{refNo} {refNo} {payRef1} {payRef2}... {ewayBillNumber}

Why invoice number appears twice (positions 7 and 8): Intentional — allows FTS MATCH to work whether the user searches 2526FT1026 (full) or 1026 (bare number only).

Example blob:

Acme Corp Acme Corp  1500.0 500.0 2000.0 2526FT1026 1026 UTR123456  EWB001234

Upsert Logic

# Vyapar upserts FTS records — UPDATE if docid exists, INSERT if not
cursor = db.rawQuery("SELECT docid FROM kb_fts_vtable WHERE fts_txn_id=?", [txn_id])
if cursor has row:
    db.update("kb_fts_vtable", {fts_name_id, fts_txn_id, fts_text}, "docid=?", [docid])
else:
    db.insert("kb_fts_vtable", {fts_name_id, fts_txn_id, fts_text})

a41.g Field Mapping (obfuscated → DB column)

From v31/h.java:o() — the transaction holder class used by TxnDbManager:

Search Behaviour

Critical: kb_names has no FTS index of its own. Party search only works via the transaction blob — a party with zero transactions cannot be found via FTS.

Direct DB Edit Warning

If you edit the DB directly via Python/SQLite (bypassing the Vyapar app), the FTS index is NOT automatically updated. The FTS trigger only fires through Vyapar's save routine. Direct edits to these tables require a manual FTS repair:

• kb_transactions — any field in positions 1–8 above
• kb_lineitems / kb_items — item name, HSN code, and item code ARE in the blob (appended after the transaction-level fields via a separate item-level FTS update path through FtsDbManager.updateFTSTxnRecord + TransactionModel.ftsSearchTags)
• kb_serial_details / kb_serial_mapping — serial numbers ARE in the blob, written by the same item-level FTS update path — direct edits bypass this and leave the blob stale
• kb_names — party name changes break position 1

FTS Repair Utility (Python)

Use this after any direct Python edits to keep Vyapar search working:

import sqlite3

def repair_fts(db_path: str, txn_ids: list[int]):
    conn = sqlite3.connect(db_path)
    cur = conn.cursor()

    for txn_id in txn_ids:
        # Fetch transaction fields
        cur.execute("""
            SELECT t.txn_name_id, t.txn_display_name, t.txn_description,
                   t.txn_cash_amount, t.txn_balance_amount,
                   t.txn_invoice_prefix, t.txn_ref_number_char,
                   t.txn_eway_bill_number,
                   n.full_name
            FROM kb_transactions t
            LEFT JOIN kb_names n ON n.name_id = t.txn_name_id
            WHERE t.txn_id = ?
        """, [txn_id])
        row = cur.fetchone()
        if not row:
            continue

        (name_id, display_name, description, cash, balance,
         prefix, ref, eway, party_name) = row

        # Fetch payment references
        cur.execute("""
            SELECT tpm.payment_reference
            FROM txn_payment_mapping tpm
            WHERE tpm.txn_id = ? AND tpm.payment_reference IS NOT NULL
              AND tpm.payment_reference != ''
        """, [txn_id])
        pay_refs = " ".join(r[0] for r in cur.fetchall())

        # Build transaction-level blob — field order from APK x31.o.e
        total = (cash or 0) + (balance or 0)
        full_inv = (prefix or "") + (ref or "")
        parts = [
            party_name or "",
            display_name or "",
            description or "",
            str(cash or 0),
            str(balance or 0),
            str(total),
            full_inv,
            ref or "",
            pay_refs,
            "",           # unused field (txn.G)
            eway or "",
        ]
        fts_text = " ".join(parts)

        # Append item-level data (item_name, hsn, item_code, cess, serial)
        # Observed format: each lineitem's data appended space-separated after txn fields
        cur.execute("""
            SELECT ki.item_name, ki.item_hsn_sac_code, ki.item_code,
                   ki.item_additional_cess_per_unit, li.lineitem_id
            FROM kb_lineitems li
            JOIN kb_items ki ON ki.item_id = li.item_id
            WHERE li.lineitem_txn_id = ?
        """, [txn_id])
        for item_name, hsn, item_code, cess, li_id in cur.fetchall():
            # Look up serial number for this lineitem
            cur.execute("""
                SELECT sd.serial_number FROM kb_serial_details sd
                JOIN kb_serial_mapping sm ON sm.serial_mapping_serial_id = sd.serial_id
                WHERE sm.serial_mapping_lineitem_id = ?
            """, [li_id])
            serial_row = cur.fetchone()
            serial = serial_row[0] if serial_row else ""
            item_parts = [
                item_name or "",
                hsn or "",
                item_code or "",
                str(cess or 0.0),
                serial,
            ]
            fts_text += " " + " ".join(item_parts)

        # Upsert into FTS
        cur.execute("SELECT docid FROM kb_fts_vtable WHERE fts_txn_id=?", [txn_id])
        existing = cur.fetchone()
        if existing:
            cur.execute(
                "UPDATE kb_fts_vtable SET fts_name_id=?, fts_txn_id=?, fts_text=? WHERE docid=?",
                [name_id, txn_id, fts_text, existing[0]]
            )
        else:
            cur.execute(
                "INSERT INTO kb_fts_vtable (fts_name_id, fts_txn_id, fts_text) VALUES (?,?,?)",
                [name_id, txn_id, fts_text]
            )

    conn.commit()
    conn.close()
    print(f"FTS repaired for {len(txn_ids)} transactions.")

# Usage:
# repair_fts("finetune.vyp", [71, 1594])          # fix specific txns
# repair_fts("finetune.vyp", list(range(1, 1733))) # full rebuild

Transaction Write — Complete Table Impact (Live-Verified)

Source: Diff of live .vyp before/after a single Sale Invoice created in Vyapar 5.x Android. Every table touched by Vyapar when saving a txn_type=1 Sale Invoice.

Tables written on every Sale Invoice save

txn_payment_mapping — Required INSERT

Every transaction needs at least one row here regardless of payment status.

INSERT INTO txn_payment_mapping
  (payment_id, txn_id, amount, payment_reference)
VALUES
  (1, <txn_id>, 0.0, '');
-- payment_id: 1=Cash, 2=Cheque (from kb_paymentTypes)
-- amount: 0.0 if credit/unpaid, actual amount if collected at time of sale

⚠️ Missing this row causes Vyapar to silently malfunction on the transaction.

txn_time — Seconds Since Midnight

from datetime import datetime
now = datetime.now()
txn_time = now.hour * 3600 + now.minute * 60 + now.second
# Example: 15:48:12 → 56892

kb_serial_details — serial_current_quantity Behaviour

In FINETUNE's workflow (phones sold directly without prior stock adjustment), value lands at -1.0.

audit_trails — Do Not Write

Vyapar auto-populates ~3 rows per transaction. Do not INSERT manually — leave to the app or ignore entirely when migrating via direct DB write.