Database Schema Reference

Asset360 uses Cloudflare D1 (SQLite) as its primary database with a comprehensive schema supporting multi-tenant fund management, accounting, portfolio tracking, and investor operations. This reference documents all tables, relationships, and constraints.

Overview

The database schema follows Domain-Driven Design principles with clear separation between:

• Core entities: Organizations, funds, investors, users
• Portfolio data: Equity and bond holdings, transactions
• Accounting system: Double-entry bookkeeping with journals and accounts
• Operational data: Daily snapshots, market data, audit trails

Schema Organization

worker/db/schema/
├── core.ts           # Core entities (organizations, users, etc.)
├── funds.ts          # Fund-specific entities and relationships
├── investors.ts      # Investor management and holdings
├── portfolios.ts     # Equity and bond portfolio data
├── accounting.ts     # Double-entry bookkeeping system
├── banking.ts        # Bank accounts and cash management
├── fdr.ts            # Fixed deposit receipts
├── fund-snapshots.ts # Daily fund valuation snapshots
├── reporting.ts      # Reporting and analytics tables
└── index.ts          # Schema exports and relationships

Core Entities

Organizations

Multi-tenant foundation for fund management companies.

-- worker/db/schema/core.ts
CREATE TABLE organizations (
  id TEXT PRIMARY KEY,
  slug TEXT UNIQUE NOT NULL,
  name TEXT NOT NULL,
  description TEXT,
  settings TEXT, -- JSON settings
  created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- Indexes
CREATE UNIQUE INDEX idx_organizations_slug ON organizations(slug);
CREATE INDEX idx_organizations_created_at ON organizations(created_at);

Fields

Field	Type	Description
id	TEXT (UUID)	Primary key
slug	TEXT	URL-friendly unique identifier
name	TEXT	Organization display name
description	TEXT	Organization description
settings	TEXT	JSON configuration
created_at	TEXT	Creation timestamp (ISO 8601)
updated_at	TEXT	Last update timestamp (ISO 8601)

Relationships

• organizations → funds (one-to-many)
• organizations → investors (one-to-many)
• organizations → users (one-to-many)

Users

Application users with role-based access control.

CREATE TABLE users (
  id TEXT PRIMARY KEY,
  email TEXT UNIQUE NOT NULL,
  name TEXT NOT NULL,
  role TEXT NOT NULL DEFAULT 'user',
  organization_id TEXT NOT NULL,
  created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (organization_id) REFERENCES organizations(id) ON DELETE CASCADE
);

Fields

Field	Type	Description
id	TEXT (UUID)	Primary key
email	TEXT	Unique email address
name	TEXT	User display name
role	TEXT	User role (admin, user, etc.)
organization_id	TEXT	Foreign key to organizations
created_at	TEXT	Creation timestamp
updated_at	TEXT	Last update timestamp

Fund Management

Funds

Core fund entities with configuration and metadata.

-- worker/db/schema/funds.ts
CREATE TABLE funds (
  id TEXT PRIMARY KEY,
  organization_id TEXT NOT NULL,
  code TEXT NOT NULL,
  name TEXT NOT NULL,
  fund_type TEXT,
  base_currency TEXT NOT NULL DEFAULT 'BDT',
  description TEXT,
  status TEXT NOT NULL DEFAULT 'active',
  created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (organization_id) REFERENCES organizations(id) ON DELETE CASCADE,
  UNIQUE(organization_id, code)
);

Fields

Field	Type	Description
id	TEXT (UUID)	Primary key
organization_id	TEXT	Owning organization
code	TEXT	Unique fund code within org
name	TEXT	Fund display name
fund_type	TEXT	Fund type (equity, balanced, etc.)
base_currency	TEXT	Base currency (default: BDT)
description	TEXT	Fund description
status	TEXT	Fund status (active, inactive)
created_at	TEXT	Creation timestamp
updated_at	TEXT	Last update timestamp

Investor Management

Investors

Investor entities with contact information and status.

-- worker/db/schema/investors.ts
CREATE TABLE investors (
  id TEXT PRIMARY KEY,
  organization_id TEXT NOT NULL,
  name TEXT NOT NULL,
  email TEXT UNIQUE,
  phone TEXT,
  address TEXT,
  tax_id TEXT,
  status TEXT NOT NULL DEFAULT 'active',
  created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (organization_id) REFERENCES organizations(id) ON DELETE CASCADE
);

Fields

Field	Type	Description
id	TEXT (UUID)	Primary key
organization_id	TEXT	Owning organization
name	TEXT	Investor name
email	TEXT	Unique email address
phone	TEXT	Phone number
address	TEXT	Physical address
tax_id	TEXT	Tax identification number
status	TEXT	Investor status
created_at	TEXT	Creation timestamp
updated_at	TEXT	Last update timestamp

Investor Holdings

Current unit holdings for each investor-fund combination.

CREATE TABLE investor_holdings (
  id TEXT PRIMARY KEY,
  investor_id TEXT NOT NULL,
  fund_id TEXT NOT NULL,
  units INTEGER NOT NULL DEFAULT 0,
  average_cost INTEGER NOT NULL DEFAULT 0,
  total_cost INTEGER NOT NULL DEFAULT 0,
  updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (investor_id) REFERENCES investors(id) ON DELETE CASCADE,
  FOREIGN KEY (fund_id) REFERENCES funds(id) ON DELETE CASCADE,
  UNIQUE(investor_id, fund_id)
);

Fields

Field	Type	Description
id	TEXT (UUID)	Primary key
investor_id	TEXT	Foreign key to investors
fund_id	TEXT	Foreign key to funds
units	INTEGER	Current unit balance
average_cost	INTEGER	Average cost per unit (minor units)
total_cost	INTEGER	Total cost basis (minor units)
updated_at	TEXT	Last update timestamp

Unit Transactions

All unit purchase and surrender transactions.

CREATE TABLE unit_transactions (
  id TEXT PRIMARY KEY,
  investor_id TEXT NOT NULL,
  fund_id TEXT NOT NULL,
  transaction_type TEXT NOT NULL, -- 'PURCHASE' | 'SURRENDER'
  units INTEGER NOT NULL,
  unit_price INTEGER NOT NULL,
  total_amount INTEGER NOT NULL,
  status TEXT NOT NULL DEFAULT 'PENDING',
  transaction_date TEXT NOT NULL,
  settlement_date TEXT,
  reference TEXT,
  description TEXT,
  created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (investor_id) REFERENCES investors(id) ON DELETE CASCADE,
  FOREIGN KEY (fund_id) REFERENCES funds(id) ON DELETE CASCADE
);

Fields

Field	Type	Description
id	TEXT (UUID)	Primary key
investor_id	TEXT	Foreign key to investors
fund_id	TEXT	Foreign key to funds
transaction_type	TEXT	PURCHASE or SURRENDER
units	INTEGER	Number of units
unit_price	INTEGER	Price per unit (minor units)
total_amount	INTEGER	Total amount (minor units)
status	TEXT	Transaction status
transaction_date	TEXT	Transaction date (ISO 8601)
settlement_date	TEXT	Settlement date (ISO 8601)
reference	TEXT	Transaction reference
description	TEXT	Transaction description
created_at	TEXT	Creation timestamp
updated_at	TEXT	Last update timestamp

Portfolio Management

Equity Securities

Master table for equity securities (stocks, mutual funds, etc.).

-- worker/db/schema/portfolios.ts
CREATE TABLE equity_securities (
  id TEXT PRIMARY KEY,
  scrip_code TEXT UNIQUE NOT NULL,
  ticker TEXT UNIQUE NOT NULL,
  isin TEXT UNIQUE,
  name TEXT,
  sector TEXT,
  active INTEGER NOT NULL DEFAULT 1,
  api_updated_at TEXT,
  close_price INTEGER,
  last_price_update TEXT,
  created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
);

Equity Holdings

Current equity holdings by fund.

CREATE TABLE equity_holdings (
  id TEXT PRIMARY KEY,
  fund_id TEXT NOT NULL,
  security_id TEXT NOT NULL,
  snapshot_date TEXT NOT NULL,
  quantity INTEGER NOT NULL DEFAULT 0,
  quantity_settled INTEGER NOT NULL DEFAULT 0,
  cost_basis_minor_units INTEGER NOT NULL DEFAULT 0,
  market_value_minor_units INTEGER,
  unrealized_gl_minor_units INTEGER,
  previous_market_value_minor_units INTEGER,
  previous_cost_basis_minor_units INTEGER,
  created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (fund_id) REFERENCES funds(id) ON DELETE CASCADE,
  FOREIGN KEY (security_id) REFERENCES equity_securities(id) ON DELETE CASCADE,
  UNIQUE(fund_id, security_id, snapshot_date)
);

Equity Transactions

All equity purchase and sale transactions.

CREATE TABLE equity_transactions (
  id TEXT PRIMARY KEY,
  fund_id TEXT NOT NULL,
  security_id TEXT NOT NULL,
  transaction_type TEXT NOT NULL, -- 'BUY' | 'SELL'
  quantity INTEGER NOT NULL,
  price INTEGER NOT NULL,
  amount_minor_units INTEGER NOT NULL,
  trade_date TEXT NOT NULL,
  settlement_date TEXT,
  broker TEXT,
  reference TEXT,
  created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (fund_id) REFERENCES funds(id) ON DELETE CASCADE,
  FOREIGN KEY (security_id) REFERENCES equity_securities(id) ON DELETE CASCADE
);

Bond Securities

Master table for fixed-income securities.

CREATE TABLE bond_securities (
  id TEXT PRIMARY KEY,
  scrip_code TEXT UNIQUE NOT NULL,
  ticker TEXT UNIQUE NOT NULL,
  isin TEXT UNIQUE,
  coupon_rate_bps INTEGER NOT NULL DEFAULT 0,
  coupon_frequency TEXT NOT NULL DEFAULT 'ANNUAL',
  maturity_date TEXT,
  face_value_minor_units INTEGER NOT NULL DEFAULT 100000,
  day_count_convention TEXT NOT NULL DEFAULT 'ACT_365',
  api_updated_at TEXT,
  active INTEGER NOT NULL DEFAULT 1,
  close_price INTEGER,
  last_price_update TEXT,
  created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
);

Bond Holdings

Current bond holdings by fund with accrued interest.

CREATE TABLE bond_holdings (
  id TEXT PRIMARY KEY,
  fund_id TEXT NOT NULL,
  bond_id TEXT NOT NULL,
  snapshot_date TEXT NOT NULL,
  quantity_total INTEGER NOT NULL DEFAULT 0,
  quantity_saleable INTEGER NOT NULL DEFAULT 0,
  clean_cost_basis_minor_units INTEGER NOT NULL DEFAULT 0,
  accrued_interest_minor_units INTEGER NOT NULL DEFAULT 0,
  market_value_minor_units INTEGER,
  previous_market_value_minor_units INTEGER,
  previous_cost_basis_minor_units INTEGER,
  created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (fund_id) REFERENCES funds(id) ON DELETE CASCADE,
  FOREIGN KEY (bond_id) REFERENCES bond_securities(id) ON DELETE CASCADE,
  UNIQUE(fund_id, bond_id, snapshot_date)
);

Bond Transactions

All bond purchase and sale transactions with accrued interest.

CREATE TABLE bond_transactions (
  id TEXT PRIMARY KEY,
  fund_id TEXT NOT NULL,
  bond_id TEXT NOT NULL,
  transaction_type TEXT NOT NULL, -- 'BUY' | 'SELL'
  quantity INTEGER NOT NULL,
  clean_amount_minor_units INTEGER NOT NULL,
  accrued_interest_minor_units INTEGER DEFAULT 0,
  trade_date TEXT NOT NULL,
  settlement_date TEXT NOT NULL,
  reference TEXT,
  created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (fund_id) REFERENCES funds(id) ON DELETE CASCADE,
  FOREIGN KEY (bond_id) REFERENCES bond_securities(id) ON DELETE CASCADE
);

Accounting System

Chart of Accounts

Standard accounting accounts for double-entry bookkeeping.

-- worker/db/schema/accounting.ts
CREATE TABLE accounts (
  id TEXT PRIMARY KEY,
  fund_id TEXT NOT NULL,
  code TEXT NOT NULL,
  name TEXT NOT NULL,
  type TEXT NOT NULL, -- 'asset' | 'liability' | 'equity' | 'revenue' | 'expense'
  description TEXT,
  is_contra INTEGER NOT NULL DEFAULT 0,
  created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (fund_id) REFERENCES funds(id) ON DELETE CASCADE,
  UNIQUE(fund_id, code)
);

Journal Entries

All journal entries for the accounting system.

CREATE TABLE journal_entries (
  id TEXT PRIMARY KEY,
  fund_id TEXT NOT NULL,
  date TEXT NOT NULL,
  description TEXT,
  reference TEXT,
  status TEXT NOT NULL DEFAULT 'posted',
  created_by TEXT,
  created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (fund_id) REFERENCES funds(id) ON DELETE CASCADE
);

Journal Entry Lines

Individual debit and credit lines for journal entries.

CREATE TABLE journal_entry_lines (
  id TEXT PRIMARY KEY,
  journal_entry_id TEXT NOT NULL,
  account_id TEXT NOT NULL,
  amount_minor_units INTEGER NOT NULL,
  description TEXT,
  created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (journal_entry_id) REFERENCES journal_entries(id) ON DELETE CASCADE,
  FOREIGN KEY (account_id) REFERENCES accounts(id) ON DELETE CASCADE
);

Banking and Cash

Bank Accounts

Bank accounts for fund cash management.

-- worker/db/schema/banking.ts
CREATE TABLE bank_accounts (
  id TEXT PRIMARY KEY,
  fund_id TEXT NOT NULL,
  bank_name TEXT NOT NULL,
  account_number TEXT UNIQUE NOT NULL,
  account_type TEXT NOT NULL,
  currency TEXT NOT NULL DEFAULT 'BDT',
  balance_minor_units INTEGER NOT NULL DEFAULT 0,
  is_active INTEGER NOT NULL DEFAULT 1,
  created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (fund_id) REFERENCES funds(id) ON DELETE CASCADE
);

Bank Transactions

All bank transactions for cash flow tracking.

CREATE TABLE bank_transactions (
  id TEXT PRIMARY KEY,
  bank_account_id TEXT NOT NULL,
  transaction_type TEXT NOT NULL, -- 'DEPOSIT' | 'WITHDRAWAL'
  amount_minor_units INTEGER NOT NULL,
  description TEXT,
  reference TEXT,
  transaction_date TEXT NOT NULL,
  created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (bank_account_id) REFERENCES bank_accounts(id) ON DELETE CASCADE
);

Fixed Deposits

Fixed Deposit Receipts (FDRs)

Fixed deposit investments with interest calculations.

-- worker/db/schema/fdr.ts
CREATE TABLE fdrs (
  id TEXT PRIMARY KEY,
  fund_id TEXT NOT NULL,
  bank_name TEXT NOT NULL,
  principal_amount_minor_units INTEGER NOT NULL,
  interest_rate_bps INTEGER NOT NULL,
  start_date TEXT NOT NULL,
  maturity_date TEXT NOT NULL,
  maturity_amount_minor_units INTEGER,
  status TEXT NOT NULL DEFAULT 'active',
  reference TEXT,
  created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (fund_id) REFERENCES funds(id) ON DELETE CASCADE
);

FDR Interest Accruals

Daily interest accrual records for FDRs.

CREATE TABLE fdr_interest_accruals (
  id TEXT PRIMARY KEY,
  fdr_id TEXT NOT NULL,
  accrual_date TEXT NOT NULL,
  interest_amount_minor_units INTEGER NOT NULL,
  cumulative_interest_minor_units INTEGER NOT NULL,
  created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (fdr_id) REFERENCES fdrs(id) ON DELETE CASCADE,
  UNIQUE(fdr_id, accrual_date)
);

Fund Snapshots and Reporting

Fund Daily Snapshots

Daily net asset value and fund statistics.

-- worker/db/schema/fund-snapshots.ts
CREATE TABLE fund_daily_snapshots (
  id TEXT PRIMARY KEY,
  fund_id TEXT NOT NULL,
  snapshot_date TEXT NOT NULL,
  total_nav_minor_units INTEGER NOT NULL,
  total_units INTEGER NOT NULL DEFAULT 0,
  nav_per_unit INTEGER NOT NULL,
  total_assets_minor_units INTEGER NOT NULL,
  total_liabilities_minor_units INTEGER NOT NULL,
  created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (fund_id) REFERENCES funds(id) ON DELETE CASCADE,
  UNIQUE(fund_id, snapshot_date)
);

Market Data

Historical market price data for securities.

-- worker/db/schema/reporting.ts
CREATE TABLE market_prices (
  id TEXT PRIMARY KEY,
  symbol TEXT NOT NULL,
  date TEXT NOT NULL,
  open_price INTEGER,
  high_price INTEGER,
  low_price INTEGER,
  close_price INTEGER NOT NULL,
  volume INTEGER,
  created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE(symbol, date)
);

Audit Logs

Comprehensive audit trail for all operations.

CREATE TABLE audit_logs (
  id TEXT PRIMARY KEY,
  organization_id TEXT,
  user_id TEXT,
  entity_type TEXT NOT NULL,
  entity_id TEXT NOT NULL,
  action TEXT NOT NULL,
  old_values TEXT, -- JSON
  new_values TEXT, -- JSON
  ip_address TEXT,
  user_agent TEXT,
  created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (organization_id) REFERENCES organizations(id),
  FOREIGN KEY (user_id) REFERENCES users(id)
);

Data Types and Constraints

Primary Keys

All tables use TEXT UUIDs as primary keys for:

• Distributed system compatibility
• URL-friendly identifiers
• Easy integration with frontend systems

Timestamps

All timestamps use ISO 8601 string format:

• Example: "2025-01-15T10:30:00.000Z"
• Consistent timezone handling (UTC)
• Easy parsing in JavaScript/TypeScript

Monetary Values

All monetary values are stored in minor units (integer):

• BDT: 1 unit = 100 poisha
• Prevents floating-point precision issues
• Enables precise financial calculations
• Convert with value / 100 for display

Enums and Constraints

Common enumerated values across tables:

Status Fields

• active / inactive for entities
• pending / processing / completed / failed / cancelled for transactions
• posted / draft for accounting entries

Transaction Types

• PURCHASE / SURRENDER for unit transactions
• BUY / SELL for security transactions
• DEPOSIT / WITHDRAWAL for bank transactions

Indexes and Performance

Strategic Indexes

-- Organization-based queries
CREATE INDEX idx_funds_organization_id ON funds(organization_id);
CREATE INDEX idx_investors_organization_id ON investors(organization_id);
CREATE INDEX idx_users_organization_id ON users(organization_id);

-- Fund-based queries
CREATE INDEX idx_investor_holdings_fund_id ON investor_holdings(fund_id);
CREATE INDEX idx_unit_transactions_fund_id ON unit_transactions(fund_id);
CREATE INDEX idx_equity_holdings_fund_id ON equity_holdings(fund_id);
CREATE INDEX idx_bond_holdings_fund_id ON bond_holdings(fund_id);

-- Date-based queries
CREATE INDEX idx_unit_transactions_date ON unit_transactions(transaction_date);
CREATE INDEX idx_fund_snapshots_date ON fund_daily_snapshots(snapshot_date);
CREATE INDEX idx_journal_entries_date ON journal_entries(date);

-- Performance indexes
CREATE INDEX idx_equity_securities_active ON equity_securities(active);
CREATE INDEX idx_bond_securities_active ON bond_securities(active);

Query Optimization

1. Date Range Queries: Use composite indexes on (fund_id, date)
2. Organization Filtering: Always include organization_id in queries
3. Pagination: Use LIMIT and OFFSET with appropriate indexes
4. JSON Data: Store structured data as TEXT, query with JSON functions

Foreign Key Constraints

Cascade Rules

• CASCADE DELETE: Child records deleted when parent removed
• RESTRICT: Prevents deletion when child records exist
• SET NULL: Foreign keys set to NULL (rarely used)

Key Relationships

-- Core relationships
organizations 1→N funds
organizations 1→N investors
organizations 1→N users
funds 1→N investor_holdings
funds 1→N equity_holdings
funds 1→N bond_holdings
investors 1→N unit_transactions
investors 1→N investor_holdings

-- Portfolio relationships
funds 1→N equity_transactions
equity_securities 1→N equity_holdings
equity_securities 1→N equity_transactions
funds 1→N bond_transactions
bond_securities 1→N bond_holdings
bond_securities 1→N bond_transactions

-- Accounting relationships
funds 1→N accounts
funds 1→N journal_entries
journal_entries 1→N journal_entry_lines

Migration and Versioning

Schema Migrations

Database schema is managed through Drizzle migrations:

// Example migration file
export const migrateUp = drizzle`
  ALTER TABLE funds ADD COLUMN management_fee_bps INTEGER DEFAULT 0;
`;

export const migrateDown = drizzle`
  ALTER TABLE funds DROP COLUMN management_fee_bps;
`;

Migration Process

1. Schema Changes: Update TypeScript schema files
2. Generate Migration: pnpm drizzle-kit generate
3. Review Migration: Check generated SQL
4. Apply Migration: Automatic during deployment
5. Test: Verify data integrity

Data Integrity

Business Rules

1. Fund Codes: Unique within organization
2. Unit Balances: Never negative
3. Accounting: Every journal entry balances (debits = credits)
4. Settlement Dates: Trade date ≤ Settlement date
5. NAV Calculations: Consistent with holdings and market data

Validation Constraints

-- Example constraints
ALTER TABLE unit_transactions ADD CONSTRAINT chk_units_positive
  CHECK (units > 0);

ALTER TABLE funds ADD CONSTRAINT chk_base_currency
  CHECK (base_currency IN ('BDT', 'USD', 'EUR'));

ALTER TABLE accounts ADD CONSTRAINT chk_account_types
  CHECK (type IN ('asset', 'liability', 'equity', 'revenue', 'expense'));

Best Practices

Query Patterns

1. Organization Isolation: Always filter by organization_id
2. Date Ranges: Use inclusive date ranges with proper indexing
3. Pagination: Implement efficient pagination for large datasets
4. Transactions: Use database transactions for multi-table operations

Performance Considerations

1. Batch Operations: Use bulk inserts for large datasets
2. Connection Pooling: Leverage Cloudflare D1 connection pooling
3. Caching: Cache frequently accessed reference data
4. Monitoring: Monitor query performance and slow queries

---

Last Updated: 2025-01-15 - Complete database schema reference with all tables, relationships, and constraints for Asset360 v3.