Schema Summary

Module	Tables	Key Tables
Core Property	7	societies, buildings, wings, flats, parking_slots, assets, documents
Finance & Accounting	21	accounts, invoices, payments, receipts, ledgers, journal_entries, journal_lines, vendor_bills, gst_config, tds_config, tds_returns
Security & Gate	12	guards, visitors, gate_logs, vehicles, patrol_logs, emergency_sos, ev_charging_sessions, rfid_logs, vehicle_access_logs
Community & Social	17	society_posts, comments, polls, marketplace_listings, blood_donors, safety_alerts, discussion_threads, community_events, event_rsvps, society_notices
Facilities	6	facility_bookings, escrow_holds, maintenance_tasks, utility_meters, meter_readings, water_tankers
Governance	7	meetings, agendas, minutes, resolutions, elections, voting_ballots, anonymized_votes
Staff	7	employees, staff_attendance, shift_rosters, payroll_entries, daily_checklists, checklist_item_logs
Hyperlocal	3	local_merchants, merchant_deals, home_service_requests
Civic	2	civic_escalations, ward_polling_feedback
AI Infrastructure	4	ai_agent_logs, ai_agent_memories, ai_agent_tools, society_tasks
Auth & Security Fortress	27	users, user_sessions, mfa_secrets, audit_logs, security_events, role_definitions, permission_definitions, encryption_keys, api_keys, society_features

Suite 1: Core Property & Resident Management

1. societies

Multi-tenant master. Each society is a tenant.

Column	Type	Constraints
id	TEXT	PRIMARY KEY
name	TEXT	NOT NULL
registration_number	TEXT	UNIQUE
address_line1	TEXT	NOT NULL
city	TEXT	NOT NULL
state	TEXT	NOT NULL
pincode	TEXT	NOT NULL
lat / lng	REAL	Geographic coordinates
h3_index	TEXT	H3 hex index for hyperlocal queries
tier	TEXT	CHECK IN ('FREE','ESSENTIAL','PRO','ENTERPRISE')
status	TEXT	CHECK IN ('ACTIVE','SUSPENDED','PENDING_ONBOARDING')
total_flats	INTEGER	Default 0
logo_url	TEXT	R2 URL
gstin	TEXT	GST identification

2-4. buildings → wings → flats

societies
  └── buildings (society_id, name, code, total_floors, total_wings)
        └── wings (building_id, name, floors, flats_per_floor)
              └── flats (wing_id, flat_number, floor_number, flat_type, square_feet,
                         occupancy_status, primary_owner_id)

flat_type CHECK: 1BHK, 2BHK, 3BHK, 4BHK, PENTHOUSE, STUDIO, COMMERCIAL, SHOP

occupancy_status CHECK: OWNER_OCCUPIED, TENANT_OCCUPIED, VACANT, UNDER_RENOVATION

5-9. parking_slots, assets, vendors, employees, documents

parking_slots: slot_type (COVERED_FOUR_WHEELER, OPEN_FOUR_WHEELER, TWO_WHEELER, VISITOR, EV_CHARGING), is_ev_ready, allocated_flat_id

assets: category (ELEVATOR, GENERATOR, WATER_PUMP, SOLAR_PANEL, CCTV, etc.), warranty_expiry, amc_vendor_id

vendors: service_type (SECURITY, CLEANING, ELEVATOR_AMC, etc.), rating 1.0-5.0, payment_terms_days

employees: role (ESTATE_MANAGER, ACCOUNTANT, SECURITY_GUARD, etc.), monthly_salary, date_of_joining

documents: category (CONVEYANCE_DEED, BYELAWS, AUDIT_REPORT, etc.), is_confidential, R2 file_url

Suite 2: Financial Operations & Accounting (21 tables)

accounts (Chart of Accounts)
  ├── journal_entries → journal_lines (double-entry bookkeeping)
  ├── ledgers (account_id, debit_amount, credit_amount)
  └── budgets (financial_year, allocated_amount, utilized_amount)

invoices (maintenance billing)
  ├── payments (gateway, UPI, amount)
  └── receipts (receipt_number, PDF URL)

vendor_bills (procurement)
  ├── tds_deductees → tds_returns
  └── credit_debit_notes

statutory_taxes (GST, TDS, property tax)
gst_config (HSN/SAC codes, CGST/SGST/IGST rates)
tds_config (section codes, threshold, rate)

financial_years (April-March Indian FY)
bank_statements → bank_reconciliations (fuzzy match)
society_bank_accounts → payment_links → payment_reminders

Key business rules:

• Journal entries must balance: total_debit = total_credit
• invoice status: UNPAID → PARTIALLY_PAID → PAID / OVERDUE
• vendor_bills status: PENDING → APPROVED → PAID
• Financial year: Indian April-March cycle
• ON DELETE RESTRICT on accounts from ledgers/journal_lines (prevents accidental deletion)

Suite 3: Security & Smart Gate (12 tables)

guards (badge_number, gate_assigned, shift: MORNING/EVENING/NIGHT)
  └── patrol_logs (checkpoint_name, qr_code_scanned)

visitors → gate_logs (entry_gate, exit_gate, approval_status, pass_code, vehicle_number)
  approval_status: PRE_APPROVED | APPROVED_BY_RESIDENT | DENIED_BY_RESIDENT
                   AUTO_APPROVED_GUARD | PENDING

vehicles (license_plate, rfid_tag_id, is_blacklisted) → parking_slots
  └── rfid_logs (reader_id, status: ALLOWED/DENIED/UNKNOWN/BLACKLISTED)
  └── vehicle_access_logs (access_type: ENTRY/EXIT, barrier_triggered)

ev_charging_sessions (kwh_consumed, base_rate_per_kwh, tou_multiplier, idle_fee)
courier_parcels (status: AT_GATE/COLLECTED/RETURNED)
domestic_staff (work_type: MAID/COOK/DRIVER, police_verification_status)
emergency_sos (emergency_type: MEDICAL/FIRE/LIFT_STUCK/SECURITY_THREAT)
  status: ACTIVE → ACKNOWLEDGED → RESOLVED | FALSE_ALARM

Suite 4: Community & Social (17 tables)

Feeds & Posts:
  society_posts (category: GENERAL/ANNOUNCEMENT/BUY_SELL/RECOMMENDATION/LOST_FOUND/ALERT)
  comments (parent_comment_id for threaded nesting)
  feed_uploads (R2 storage, original_size, compressed_size, media_type)
  society_storage_quotas (tier-based GB limits)

Polls & Discussions:
  community_polls (poll_type: YES_NO/MULTIPLE_CHOICE/RANKING)
  poll_votes (one vote per user per poll)
  discussion_threads (category: GENERAL/BUY_SELL/GOVERNANCE/SAFETY/SOCIAL)
  discussion_replies

Events:
  community_events (event_type: SOCIAL/SPORTS/CULTURAL/MEETING)
  event_rsvps (GOING/MAYBE/NOT_GOING)

Hyperlocal:
  marketplace_listings (listing_type: SELL/RENT/DONATE, H3 geo-index)
  blood_donors (blood_group, is_available, last_donation_date)
  safety_alerts (alert_type: EMERGENCY/CRIME/WEATHER/FIRE/FLOOD)
  society_notices (notice_type: GENERAL/AGM/EMERGENCY/MAINTENANCE/POLICY/EVENT)

Suite 5: Facilities (6 tables)

facility_bookings (CLUBHOUSE/TENNIS_COURT/SWIMMING_POOL/GYM, booking_date, time slots)
  └── escrow_holds (security deposit, cleaner_checkout_status)

maintenance_tasks (priority: LOW→CRITICAL, assigned to employee/vendor)
utility_meters (PNG_GAS/WATER/ELECTRICITY/EV_CHARGING, multiplier)
  └── meter_readings (consumption = current - previous, GENERATED ALWAYS AS)

water_tankers (vendor_id, capacity_liters, cost, tds_level_ppm, guard verification)

Suite 6: Governance (7 tables)

meetings (AGM/EGM/MANAGING_COMMITTEE/EMERGENCY)
  ├── agendas (item_order, presenter_user_id)
  ├── resolutions (votes_for/against/abstained, PASSED/REJECTED/DEFERRED)
  ├── minutes (summary_notes, action_items_json, pdf_document_id)
  └── elections (nomination/voting periods, status workflow)

voting_ballots (XOR constraint: either election_id OR resolution_id, never both)
anonymized_votes (Pedersen Commitment: r_blinding, g/h/p/q, commitment_hash UNIQUE)

Suite 7: Staff & Payroll (5 tables)

employees (role, monthly_salary, date_of_joining)
  ├── staff_attendance (check_in/out, biometric flag, UNIQUE per date)
  ├── shift_rosters (MORNING/AFTERNOON/NIGHT/GENERAL)
  └── payroll_entries (basic, allowances, overtime, PF/ESI/tax deductions, net_salary)

daily_checklists → checklist_item_logs (photo_proof_url, numeric_value)

Auth & Security Fortress (27 tables)

Identity:
  users (email UNIQUE, phone UNIQUE, password_hash, system_role)
  user_flat_mappings (PRIMARY_OWNER/CO_OWNER/TENANT/FAMILY_MEMBER, occupancy)
  user_trust_scores (0-1000 scale)

Auth:
  user_sessions (device_fingerprint, refresh_token_hash, revocation)
  mfa_secrets (TOTP encrypted, backup_codes JSON, lockout_count)
  login_attempts (brute force tracking)
  nonce_store (single-use nonce, expiry)

RBAC:
  role_definitions (15 system + custom roles)
  permission_definitions (67 permissions across 11 categories)
  role_permissions (role_id + permission_key, grant/deny)
  user_role_assignments (per-society, with expiry)

Encryption:
  encryption_keys (per-society, AES-256-GCM, version, rotation)

Compliance:
  user_consents (14 purposes: ACCOUNT_OPERATION, BILLING, WHATSAPP, CCTV, etc.)
  data_requests (ACCESS/CORRECTION/ERASURE/PORTABILITY/OBJECTION/RESTRICTION)
  audit_logs (hash chain: prev_hash → record_hash, 7-year retention)
  security_events (severity: INFO→CRITICAL)

API & Config:
  api_keys (scopes, IP whitelist, rate_limit, expiry)
  whatsapp_config (Meta Cloud API tokens, message limits)
  whatsapp_message_log (delivery tracking: QUEUED→SENT→DELIVERED→READ)
  society_features (101 feature flags per society, KV-cached)
  account_suspensions (TEMPORARY/PERMANANENT/COOLDOWN)
  user_behavior_baselines (anomaly detection fingerprint)

Letters:
  letter_templates (NOC/PASSPORT/DOMICILE/NOMINATION/INCOME/ADDRESS/SOCIETY)
  letter_requests (PENDING→APPROVED→GENERATED→DOWNLOADED)

AI Infrastructure (4 tables)

ai_agent_logs (agent_name, trigger_source, tokens_used, execution_time_ms)
ai_agent_memories (memory_key, embedding_vector, ttl_expiry)
ai_agent_tools (tool_name, handler_endpoint, requires_approval)
society_tasks (assigned_to_type: AI_AGENT/COMMITTEE/EMPLOYEE/VENDOR)

Indexes

223 CREATE INDEX statements covering all foreign keys and common query patterns. Notable composite indexes:

• idx_journal_entries_date_range — date-range accounting queries
• idx_audit_logs_forensic — society + action + date + entity
• idx_payments_society_flat — payment history per flat
• idx_vehicles_rfid — fast RFID tag lookups
• idx_security_events_dashboard — society + severity + unresolved

Key Constraints

• XOR constraint: voting_ballots requires either election_id OR resolution_id (not both)
• Monotonicity: meter_readings.current_reading >= previous_reading
• RESTRICT deletes: accounts from ledgers/journal_lines/vendor_bills (prevents accidental financial data loss)
• Generated column: meter_readings.consumption = current - previous
• Hash chain: audit_logs.prev_hash → record_hash (tamper-evident)