-- migrate.sql

-- Clean schema for all tables (SQLite).

-- This mirrors what server-go/store/db/migrate.go applies on startup.

-- Run with: sqlite3 server-go/app.db < sql/migrate.sql

PRAGMA journal_mode=WAL;

PRAGMA foreign_keys=ON;

CREATE TABLE IF NOT EXISTS users (

id INTEGER PRIMARY KEY AUTOINCREMENT,

username TEXT UNIQUE,

password TEXT,

is_admin INTEGER DEFAULT 0

);

CREATE TABLE IF NOT EXISTS logs (

id INTEGER PRIMARY KEY AUTOINCREMENT,

username TEXT,

input_value REAL,

result REAL,

timestamp DATETIME DEFAULT CURRENT_TIMESTAMP

);

CREATE TABLE IF NOT EXISTS tenants (

id INTEGER PRIMARY KEY AUTOINCREMENT,

name TEXT NOT NULL,

tax_code TEXT NOT NULL UNIQUE,

legal_name TEXT,

address TEXT,

accounting_regime TEXT,

vat_method TEXT,

vat_quarterly INTEGER DEFAULT 0,

email TEXT NOT NULL UNIQUE,

phone TEXT,

created_at DATETIME DEFAULT CURRENT_TIMESTAMP,

registration_enabled INTEGER DEFAULT 0,

registration_code TEXT

);

CREATE TABLE IF NOT EXISTS user_tenants (

user_id INTEGER NOT NULL,

tenant_id INTEGER NOT NULL,

is_tenant_admin INTEGER DEFAULT 0,

created_at DATETIME DEFAULT CURRENT_TIMESTAMP,

PRIMARY KEY (user_id, tenant_id),

FOREIGN KEY (user_id) REFERENCES users(id),

FOREIGN KEY (tenant_id) REFERENCES tenants(id)

);

CREATE TABLE IF NOT EXISTS account_category (

id INTEGER PRIMARY KEY NOT NULL,

code VARCHAR,

short_text VARCHAR,

parent_id INTEGER,

tenant_id INTEGER REFERENCES tenants(id)

);

CREATE TABLE IF NOT EXISTS audit_logs (

id INTEGER PRIMARY KEY AUTOINCREMENT,

username TEXT NOT NULL,

action TEXT NOT NULL,

entity TEXT NOT NULL,

entity_id TEXT,

tenant_id INTEGER,

details TEXT,

timestamp DATETIME DEFAULT CURRENT_TIMESTAMP

);

CREATE TABLE IF NOT EXISTS account_code (

id VARCHAR NOT NULL,

code VARCHAR,

short_text VARCHAR,

is_detail VARCHAR,

group_id VARCHAR,

is_general_ledger VARCHAR,

structure INT,

currency_id VARCHAR,

is_disabled VARCHAR,

tenant_id INTEGER NOT NULL DEFAULT 0,

PRIMARY KEY (id, tenant_id)

);

CREATE TABLE IF NOT EXISTS user_permissions (

id INTEGER PRIMARY KEY AUTOINCREMENT,

user_id INTEGER NOT NULL,

menu_id TEXT NOT NULL,

can_execute INTEGER DEFAULT 0,

can_view INTEGER DEFAULT 0,

can_add INTEGER DEFAULT 0,

can_edit INTEGER DEFAULT 0,

can_delete INTEGER DEFAULT 0,

can_post INTEGER DEFAULT 0,

UNIQUE (user_id, menu_id),

FOREIGN KEY (user_id) REFERENCES users(id)

);

-- doctype_id is not a FK: document_type uses a composite PK (id, tenant_id),

-- so a single-column REFERENCES document_type(id) is not enforceable.

CREATE TABLE IF NOT EXISTS document_type (

id VARCHAR NOT NULL,

code VARCHAR,

short_text VARCHAR,

tenant_id INTEGER NOT NULL,

num_count INTEGER,

auto_increase VARCHAR,

template VARCHAR,

num_of_copy INTEGER,

auto_print VARCHAR,

trans_account VARCHAR,

master_side VARCHAR,

trans_type VARCHAR,

created_by VARCHAR,

created_at DATETIME DEFAULT CURRENT_TIMESTAMP,

updated_by VARCHAR,

updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,

PRIMARY KEY (id, tenant_id)

);

CREATE TABLE IF NOT EXISTS currency (

id VARCHAR NOT NULL,

code VARCHAR NOT NULL,

short_text VARCHAR NOT NULL,

tenant_id INTEGER NOT NULL DEFAULT 0,

is_local VARCHAR NOT NULL DEFAULT 'N',

decimal_places INTEGER NOT NULL DEFAULT 2,

thousand_separator VARCHAR(1) NOT NULL DEFAULT '.',

decimal_separator VARCHAR(1) NOT NULL DEFAULT ',',

PRIMARY KEY (id, tenant_id)

);

CREATE TABLE IF NOT EXISTS item_group (

id VARCHAR NOT NULL,

code VARCHAR,

short_text VARCHAR,

tenant_id INTEGER NOT NULL DEFAULT 0,

account_id VARCHAR,

account_expense_id VARCHAR,

PRIMARY KEY (id, tenant_id)

);

CREATE TABLE IF NOT EXISTS measure_unit (

id VARCHAR NOT NULL,

code VARCHAR,

short_text VARCHAR,

tenant_id INTEGER NOT NULL,

PRIMARY KEY (id, tenant_id),

FOREIGN KEY (tenant_id) REFERENCES tenants(id)

);

CREATE TABLE IF NOT EXISTS doctype_account (

id INTEGER PRIMARY KEY AUTOINCREMENT,

doctype_id VARCHAR NOT NULL,

account_id VARCHAR NOT NULL,

account_side VARCHAR NOT NULL,

tenant_id INTEGER NOT NULL,

FOREIGN KEY (account_id, tenant_id) REFERENCES account_code(id, tenant_id),

FOREIGN KEY (tenant_id) REFERENCES tenants(id)

);

-- from_unit_id / to_unit_id logically reference measure_unit(id) but a

-- single-column FK is not enforceable because measure_unit uses a composite

-- PK (id, tenant_id). Application-level integrity is enforced via the picker.

CREATE TABLE IF NOT EXISTS unit_convert (

id VARCHAR NOT NULL,

from_unit_id VARCHAR NOT NULL,

to_unit_id VARCHAR NOT NULL,

factor NUMERIC NOT NULL,

tenant_id INTEGER NOT NULL,

PRIMARY KEY (id, tenant_id),

FOREIGN KEY (tenant_id) REFERENCES tenants(id),

CONSTRAINT unique_unit_convert UNIQUE (from_unit_id, to_unit_id, tenant_id)

);

-- account_code FK omitted: account_code uses composite PK (id, tenant_id),

-- so a single-column FK is not enforceable; store as plain VARCHAR.

CREATE TABLE IF NOT EXISTS expense_element (

id VARCHAR NOT NULL,

code VARCHAR,

short_text VARCHAR,

tenant_id INTEGER NOT NULL DEFAULT 0,

account_id VARCHAR,

PRIMARY KEY (id, tenant_id)

);

CREATE TABLE IF NOT EXISTS employee (

id VARCHAR NOT NULL,

code VARCHAR,

short_text VARCHAR,

tenant_id INTEGER NOT NULL DEFAULT 0,

address VARCHAR,

phone VARCHAR,

PRIMARY KEY (id, tenant_id)

);

CREATE TABLE IF NOT EXISTS asset_group (

id VARCHAR NOT NULL,

code VARCHAR,

short_text VARCHAR,

tenant_id INTEGER NOT NULL DEFAULT 0,

depreciation_method VARCHAR,

depreciation_rate REAL,

useful_life INTEGER,

residual_value REAL,

account_code VARCHAR,

PRIMARY KEY (id, tenant_id)

);

CREATE TABLE IF NOT EXISTS tax_category (

id VARCHAR NOT NULL,

code VARCHAR,

short_text VARCHAR,

tenant_id INTEGER NOT NULL DEFAULT 0,

tax_rate REAL,

direction VARCHAR,

tax_mode VARCHAR,

tax_account VARCHAR,

PRIMARY KEY (id, tenant_id)

);

CREATE TABLE IF NOT EXISTS partner_group (

id VARCHAR NOT NULL,

code VARCHAR,

short_text VARCHAR,

tenant_id INTEGER NOT NULL,

account_id VARCHAR,

PRIMARY KEY (id, tenant_id)

);

CREATE TABLE IF NOT EXISTS user_favorites (

id INTEGER PRIMARY KEY AUTOINCREMENT,

user_id INTEGER NOT NULL,

menu_id TEXT NOT NULL,

sort_order INTEGER NOT NULL DEFAULT 0,

UNIQUE (user_id, menu_id),

FOREIGN KEY (user_id) REFERENCES users(id)

);

-- currency_id intentionally has no FK: currency uses a composite PK (id, tenant_id)

-- so a single-column REFERENCES currency(id) is not enforceable with foreign_keys=on.

-- partner_group_id intentionally has no FK: partner_group uses a composite PK (id, tenant_id)

-- so a single-column REFERENCES partner_group(id) is not enforceable with foreign_keys=on.

CREATE TABLE IF NOT EXISTS business_partner (

id INTEGER PRIMARY KEY,

code VARCHAR,

short_text VARCHAR,

tenant_id INTEGER REFERENCES tenants(id),

partner_group_id VARCHAR,

tax_num VARCHAR,

currency_id VARCHAR,

address VARCHAR,

phone VARCHAR,

disabled VARCHAR,

bank_account VARCHAR

);

-- debit_account / credit_account logically reference account_code(id) but a

-- single-column DB FK is not enforceable because account_code uses a composite

-- PK (id, tenant_id). Application-level integrity is enforced via the popup lookup.

CREATE TABLE IF NOT EXISTS transaction_type (

id VARCHAR NOT NULL,

code VARCHAR,

short_text VARCHAR,

tenant_id INTEGER NOT NULL DEFAULT 0,

debit_account VARCHAR,

credit_account VARCHAR,

created_at DATETIME DEFAULT CURRENT_TIMESTAMP,

PRIMARY KEY (id, tenant_id)

);

CREATE TABLE IF NOT EXISTS cash_receipt (

id INTEGER PRIMARY KEY AUTOINCREMENT,

tenant_id INTEGER NOT NULL,

type_id VARCHAR,

doc_num VARCHAR,

doc_date VARCHAR,

currency_id VARCHAR,

exchange_rate REAL,

header_account VARCHAR,

remarks VARCHAR,

attachment VARCHAR,

partner_id VARCHAR,

partner_address VARCHAR,

sum_amount REAL,

foreign_sum_amt REAL,

created_by VARCHAR,

created_at VARCHAR,

updated_by VARCHAR,

updated_at VARCHAR,

processed VARCHAR DEFAULT 'N',

approved_by VARCHAR,

tran_type VARCHAR DEFAULT 'cash',

bank_account VARCHAR,

posted VARCHAR DEFAULT 'N',

rev REAL DEFAULT 0,

FOREIGN KEY (tenant_id) REFERENCES tenants(id)

);

CREATE TABLE IF NOT EXISTS cash_receipt_line (

id INTEGER PRIMARY KEY AUTOINCREMENT,

link_id INTEGER NOT NULL,

line_num INTEGER,

line_account VARCHAR,

partner_id VARCHAR,

partner_name VARCHAR,

line_amount REAL,

line_foreign_amt REAL,

FOREIGN KEY (link_id) REFERENCES cash_receipt(id) ON DELETE CASCADE

);

CREATE TABLE IF NOT EXISTS cash_payment (

id INTEGER PRIMARY KEY AUTOINCREMENT,

tenant_id INTEGER NOT NULL,

type_id VARCHAR,

doc_num VARCHAR,

doc_date VARCHAR,

currency_id VARCHAR,

exchange_rate REAL,

header_account VARCHAR,

remarks VARCHAR,

attachment VARCHAR,

partner_id VARCHAR,

partner_address VARCHAR,

partner_name VARCHAR,

sum_amount REAL,

foreign_sum_amt REAL,

created_by VARCHAR,

created_at VARCHAR,

updated_by VARCHAR,

updated_at VARCHAR,

processed VARCHAR DEFAULT 'N',

approved_by VARCHAR,

tran_type VARCHAR DEFAULT 'cash',

bank_account VARCHAR,

posted VARCHAR DEFAULT 'N',

rev REAL DEFAULT 0,

FOREIGN KEY (tenant_id) REFERENCES tenants(id)

);

CREATE TABLE IF NOT EXISTS cash_payment_line (

id INTEGER PRIMARY KEY AUTOINCREMENT,

link_id INTEGER NOT NULL,

line_num INTEGER,

line_account VARCHAR,

partner_id VARCHAR,

partner_name VARCHAR,

line_amount REAL,

line_foreign_amt REAL,

element_id VARCHAR,

FOREIGN KEY (link_id) REFERENCES cash_payment(id) ON DELETE CASCADE

);

CREATE TABLE IF NOT EXISTS ledger_doc (

id INTEGER PRIMARY KEY AUTOINCREMENT,

tenant_id INTEGER NOT NULL,

type_id VARCHAR,

doc_num VARCHAR,

doc_date VARCHAR,

posting_date VARCHAR,

remarks VARCHAR,

currency_id VARCHAR,

exchange_rate REAL,

sum_amount REAL,

foreign_sum_amt REAL,

processed VARCHAR DEFAULT 'N',

rev REAL DEFAULT 0,

created_by VARCHAR,

updated_by VARCHAR,

created_at VARCHAR,

updated_at VARCHAR,

FOREIGN KEY (tenant_id) REFERENCES tenants(id)

);

CREATE TABLE IF NOT EXISTS ledger_doc_line (

id INTEGER PRIMARY KEY AUTOINCREMENT,

link_id INTEGER NOT NULL,

line_num INTEGER,

debit_account VARCHAR,

credit_account VARCHAR,

debit_item VARCHAR,

credit_item VARCHAR,

account_side VARCHAR,

element_id VARCHAR,

amount REAL,

foreign_amt REAL DEFAULT 0,

contra_line_nums VARCHAR,

line_remarks VARCHAR,

FOREIGN KEY (link_id) REFERENCES ledger_doc(id) ON DELETE CASCADE

);

CREATE TABLE IF NOT EXISTS base_item (

id INTEGER PRIMARY KEY AUTOINCREMENT,

code VARCHAR,

short_text VARCHAR,

tenant_id INTEGER NOT NULL,

long_text VARCHAR,

unit VARCHAR,

item_type VARCHAR,

item_group VARCHAR,

disabled VARCHAR DEFAULT 'N',

FOREIGN KEY (tenant_id) REFERENCES tenants(id),

UNIQUE (code, tenant_id)

);

CREATE TABLE IF NOT EXISTS invoice (

id INTEGER PRIMARY KEY AUTOINCREMENT,

type_id VARCHAR,

invoice_num VARCHAR,

tenant_id INTEGER NOT NULL,

partner_id VARCHAR,

invoice_date VARCHAR,

currency_id VARCHAR,

total_amount REAL,

account_code VARCHAR,

header_note VARCHAR,

header_status VARCHAR DEFAULT 'DRAFT',

created_by VARCHAR,

created_at VARCHAR,

updated_by VARCHAR,

updated_at VARCHAR,

processed VARCHAR DEFAULT 'N',

approved_by VARCHAR,

posted VARCHAR DEFAULT 'N',

rev REAL DEFAULT 0,

FOREIGN KEY (tenant_id) REFERENCES tenants(id)

);

CREATE TABLE IF NOT EXISTS invoice_line (

id INTEGER PRIMARY KEY AUTOINCREMENT,

invoice_id INTEGER NOT NULL,

line_number INTEGER,

item_id VARCHAR,

account_code VARCHAR,

quantity REAL,

uom VARCHAR,

unit_price REAL,

uom2 VARCHAR,

quantity2 REAL,

tax_category VARCHAR,

tax_rate REAL,

tax_amount REAL,

line_total REAL,

line_note VARCHAR,

line_status VARCHAR DEFAULT 'DRAFT',

FOREIGN KEY (invoice_id) REFERENCES invoice(id) ON DELETE CASCADE

);

CREATE TABLE IF NOT EXISTS bill (

id INTEGER PRIMARY KEY AUTOINCREMENT,

tenant_id INTEGER NOT NULL,

type_id VARCHAR,

bill_num VARCHAR,

partner_id VARCHAR,

bill_date VARCHAR,

currency_id VARCHAR,

total_amount REAL,

account_code VARCHAR,

header_note VARCHAR,

header_status VARCHAR DEFAULT 'DRAFT',

created_by VARCHAR,

created_at VARCHAR,

updated_by VARCHAR,

updated_at VARCHAR,

processed VARCHAR DEFAULT 'N',

approved_by VARCHAR,

posted VARCHAR DEFAULT 'N',

rev REAL DEFAULT 0,

FOREIGN KEY (tenant_id) REFERENCES tenants(id)

);

CREATE TABLE IF NOT EXISTS bill_line (

id INTEGER PRIMARY KEY AUTOINCREMENT,

bill_id INTEGER NOT NULL,

line_number INTEGER,

item_id VARCHAR,

account_code VARCHAR,

quantity REAL,

uom VARCHAR,

unit_price REAL,

uom2 VARCHAR,

unit_price2 REAL,

line_amount REAL,

tax_category VARCHAR,

tax_rate REAL,

tax_amount REAL,

line_discount REAL DEFAULT 0,

line_total REAL,

line_note VARCHAR,

line_status VARCHAR DEFAULT 'DRAFT',

FOREIGN KEY (bill_id) REFERENCES bill(id) ON DELETE CASCADE

);

CREATE TABLE IF NOT EXISTS goods_receipt (

id INTEGER PRIMARY KEY AUTOINCREMENT,

type_id VARCHAR,

receipt_num VARCHAR,

tenant_id INTEGER NOT NULL,

partner_id VARCHAR,

receipt_date VARCHAR,

currency_id VARCHAR,

total_amount REAL,

account_code VARCHAR,

header_note VARCHAR,

header_status VARCHAR DEFAULT 'DRAFT',

created_by VARCHAR,

created_at VARCHAR,

updated_by VARCHAR,

updated_at VARCHAR,

processed VARCHAR DEFAULT 'N',

approved_by VARCHAR,

posted VARCHAR DEFAULT 'N',

rev REAL DEFAULT 0,

FOREIGN KEY (tenant_id) REFERENCES tenants(id)

);

CREATE TABLE IF NOT EXISTS goods_receipt_line (

id INTEGER PRIMARY KEY AUTOINCREMENT,

receipt_id INTEGER NOT NULL,

line_number INTEGER,

item_id VARCHAR,

account_code VARCHAR,

quantity REAL,

uom VARCHAR,

unit_price REAL,

uom2 VARCHAR,

quantity2 REAL,

tax_category VARCHAR,

tax_rate REAL,

tax_amount REAL,

line_total REAL,

line_note VARCHAR,

line_status VARCHAR DEFAULT 'DRAFT',

FOREIGN KEY (receipt_id) REFERENCES goods_receipt(id) ON DELETE CASCADE

);

CREATE TABLE IF NOT EXISTS goods_issue (

id INTEGER PRIMARY KEY AUTOINCREMENT,

type_id VARCHAR,

issue_num VARCHAR,

tenant_id INTEGER NOT NULL,

partner_id VARCHAR,

issue_date VARCHAR,

currency_id VARCHAR,

total_amount REAL,

account_code VARCHAR,

header_note VARCHAR,

header_status VARCHAR DEFAULT 'DRAFT',

created_by VARCHAR,

created_at VARCHAR,

updated_by VARCHAR,

updated_at VARCHAR,

processed VARCHAR DEFAULT 'N',

approved_by VARCHAR,

posted VARCHAR DEFAULT 'N',

rev REAL DEFAULT 0,

FOREIGN KEY (tenant_id) REFERENCES tenants(id)

);

CREATE TABLE IF NOT EXISTS goods_issue_line (

id INTEGER PRIMARY KEY AUTOINCREMENT,

issue_id INTEGER NOT NULL,

line_number INTEGER,

item_id VARCHAR,

account_code VARCHAR,

quantity REAL,

uom VARCHAR,

unit_cost REAL,

uom2 VARCHAR,

unit_cost2 REAL,

line_amount REAL,

line_note VARCHAR,

line_status VARCHAR DEFAULT 'DRAFT',

FOREIGN KEY (issue_id) REFERENCES goods_issue(id) ON DELETE CASCADE

);

CREATE TABLE IF NOT EXISTS journal (

id INTEGER PRIMARY KEY AUTOINCREMENT,

tenant_id INTEGER NOT NULL,

type_id VARCHAR,

doc_num VARCHAR,

doc_date VARCHAR,

posting_date VARCHAR,

remarks VARCHAR,

currency_id VARCHAR,

exchange_rate REAL,

sum_amount REAL,

foreign_sum_amt REAL,

processed VARCHAR DEFAULT 'N',

approved_by VARCHAR,

src_doc VARCHAR,

rev REAL DEFAULT 0,

created_by VARCHAR,

created_at VARCHAR,

updated_by VARCHAR,

updated_at VARCHAR,

FOREIGN KEY (tenant_id) REFERENCES tenants(id)

);

CREATE TABLE IF NOT EXISTS journal_line (

id INTEGER PRIMARY KEY AUTOINCREMENT,

link_id INTEGER NOT NULL,

line_num INTEGER NOT NULL,

debit_account VARCHAR NOT NULL,

credit_account VARCHAR NOT NULL,

debit_item VARCHAR,

credit_item VARCHAR,

element_id VARCHAR,

amount REAL,

foreign_amt REAL,

line_remarks VARCHAR,

FOREIGN KEY (link_id) REFERENCES journal(id) ON DELETE CASCADE

);

CREATE TABLE IF NOT EXISTS posting_period (

id INTEGER PRIMARY KEY AUTOINCREMENT,

code VARCHAR(6) NOT NULL,

short_text VARCHAR(255) NOT NULL,

from_date VARCHAR(8) NOT NULL,

to_date VARCHAR(8) NOT NULL,

tenant_id INTEGER NOT NULL,

is_current VARCHAR(1) DEFAULT 'N',

is_locked VARCHAR(1) DEFAULT 'N',

is_closed VARCHAR(1) DEFAULT 'N',

created_by VARCHAR(255) NOT NULL,

created_at DATETIME DEFAULT CURRENT_TIMESTAMP,

updated_by VARCHAR(255),

updated_at DATETIME,

FOREIGN KEY (tenant_id) REFERENCES tenants(id)

);

CREATE TABLE IF NOT EXISTS account_balance (

fiscal_year INTEGER NOT NULL,

fiscal_month INTEGER NOT NULL,

account_code VARCHAR(50) NOT NULL,

tenant_id INTEGER NOT NULL,

opening_balance REAL NOT NULL DEFAULT 0,

debit REAL NOT NULL DEFAULT 0,

credit REAL NOT NULL DEFAULT 0,

ending_balance REAL NOT NULL DEFAULT 0,

fc_opening_balance REAL NOT NULL DEFAULT 0,

fc_debit REAL NOT NULL DEFAULT 0,

fc_credit REAL NOT NULL DEFAULT 0,

fc_ending_balance REAL NOT NULL DEFAULT 0,

created_by VARCHAR(50) NOT NULL DEFAULT '',

created_at DATETIME DEFAULT CURRENT_TIMESTAMP,

updated_by VARCHAR(50) NOT NULL DEFAULT '',

updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,

PRIMARY KEY (fiscal_year, fiscal_month, account_code, tenant_id)

);