Software Requirements

🛠️ 1. Technology Stack

🗄️ 2. Core Database Schema

-- Enable UUID generation (PostgreSQL <13 needs pgcrypto; 13+ has gen_random_uuid() built-in)
CREATE EXTENSION IF NOT EXISTS pgcrypto;

-- USERS (self-contained local auth — no external auth provider)
CREATE TABLE users (
  id            UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  full_name     TEXT NOT NULL,
  email         TEXT NOT NULL UNIQUE,
  password_hash TEXT NOT NULL,                  -- bcrypt (12 rounds)
  role          TEXT NOT NULL DEFAULT 'user',   -- 'admin'|'user'|'viewer'
  phone         TEXT,
  is_active     BOOLEAN NOT NULL DEFAULT TRUE,
  refresh_token_hash TEXT,                      -- SHA-256 of current refresh token
  created_at    TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at    TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE INDEX idx_users_email ON users(email);

-- FAMILY MEMBERS
CREATE TABLE family_members (
  id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  owner_id    UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  name        TEXT NOT NULL,
  relation    TEXT NOT NULL,  -- 'Self'|'Spouse'|'Father'|'Mother'|'Child'|'Other'
  dob         DATE,
  is_senior   BOOLEAN DEFAULT FALSE,
  created_at  TIMESTAMPTZ DEFAULT NOW()
);

-- PROPERTIES
CREATE TABLE properties (
  id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  owner_id        UUID REFERENCES users(id) ON DELETE CASCADE,
  name            TEXT NOT NULL,
  type            TEXT NOT NULL,  -- 'residential'|'commercial'|'land'|'plot'
  address         TEXT,
  latitude        DOUBLE PRECISION,
  longitude       DOUBLE PRECISION,
  purchase_price  NUMERIC(14,2),
  purchase_date   DATE,
  current_value   NUMERIC(14,2),
  notes           TEXT,
  is_rental       BOOLEAN DEFAULT FALSE,
  created_at      TIMESTAMPTZ DEFAULT NOW()
);

-- PROPERTY CONTACTS
CREATE TABLE property_contacts (
  id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  property_id UUID REFERENCES properties(id) ON DELETE CASCADE,
  name        TEXT NOT NULL,
  phone       TEXT NOT NULL,
  role        TEXT NOT NULL  -- 'Caretaker'|'Agent'|'Tenant'|'Neighbour'|'Other'
);

-- PROPERTY TAXES
CREATE TABLE property_taxes (
  id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  property_id UUID REFERENCES properties(id) ON DELETE CASCADE,
  tax_type    TEXT NOT NULL,  -- 'property_tax'|'water_tax'
  year        INTEGER NOT NULL,
  amount_due  NUMERIC(10,2) NOT NULL,
  due_date    DATE NOT NULL,
  paid_date   DATE,
  receipt_path TEXT,
  is_paid     BOOLEAN DEFAULT FALSE
);

-- PROPERTY EXPENSES
CREATE TABLE property_expenses (
  id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  property_id UUID REFERENCES properties(id) ON DELETE CASCADE,
  category    TEXT NOT NULL,  -- 'electricity'|'water'|'internet'|'maintenance'|'other'
  amount      NUMERIC(10,2) NOT NULL,
  expense_date DATE NOT NULL,
  description TEXT,
  receipt_path TEXT
);

-- VEHICLES
CREATE TABLE vehicles (
  id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  owner_id        UUID REFERENCES users(id) ON DELETE CASCADE,
  type            TEXT NOT NULL,  -- '2W'|'4W'
  make            TEXT NOT NULL,
  model           TEXT NOT NULL,
  year            INTEGER NOT NULL,
  reg_number      TEXT NOT NULL,
  chassis_no      TEXT,
  engine_no       TEXT,
  fuel_type       TEXT,
  colour          TEXT,
  purchase_price  NUMERIC(12,2),
  purchase_date   DATE,
  photo_path      TEXT,
  created_at      TIMESTAMPTZ DEFAULT NOW()
);

-- VEHICLE INSURANCE
CREATE TABLE vehicle_insurance (
  id            UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  vehicle_id    UUID REFERENCES vehicles(id) ON DELETE CASCADE,
  insurer       TEXT NOT NULL,
  policy_no     TEXT NOT NULL,
  type          TEXT NOT NULL,  -- 'comprehensive'|'third_party'
  premium       NUMERIC(10,2),
  idv           NUMERIC(12,2),
  start_date    DATE NOT NULL,
  expiry_date   DATE NOT NULL,
  document_path TEXT
);

-- VEHICLE SERVICE
CREATE TABLE vehicle_service (
  id            UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  vehicle_id    UUID REFERENCES vehicles(id) ON DELETE CASCADE,
  service_date  DATE NOT NULL,
  odometer      INTEGER,
  work_done     TEXT,
  cost          NUMERIC(10,2),
  service_centre TEXT,
  next_date     DATE,
  next_odometer INTEGER
);

-- VEHICLE RENEWALS (RC, PUC, Road Tax)
CREATE TABLE vehicle_renewals (
  id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  vehicle_id  UUID REFERENCES vehicles(id) ON DELETE CASCADE,
  type        TEXT NOT NULL,  -- 'rc'|'puc'|'road_tax'
  expiry_date DATE NOT NULL,
  document_path TEXT
);

-- INSURANCE POLICIES
CREATE TABLE insurance_policies (
  id            UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  owner_id      UUID REFERENCES users(id) ON DELETE CASCADE,
  type          TEXT NOT NULL,  -- 'health'|'life'|'term'|'personal_accident'|'home'
  insurer       TEXT NOT NULL,
  plan_name     TEXT NOT NULL,
  policy_no     TEXT NOT NULL,
  sum_insured   NUMERIC(14,2),
  premium       NUMERIC(10,2),
  frequency     TEXT DEFAULT 'annual',  -- 'annual'|'monthly'|'quarterly'
  start_date    DATE NOT NULL,
  renewal_date  DATE NOT NULL,
  nominee       TEXT,
  notes         TEXT,
  document_path TEXT,
  created_at    TIMESTAMPTZ DEFAULT NOW()
);

-- INSURANCE MEMBERS (covered under policy)
CREATE TABLE insurance_members (
  id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  policy_id   UUID REFERENCES insurance_policies(id) ON DELETE CASCADE,
  member_id   UUID REFERENCES family_members(id) ON DELETE CASCADE
);

-- DOCUMENTS
CREATE TABLE documents (
  id                UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  owner_id          UUID REFERENCES users(id) ON DELETE CASCADE,
  member_id         UUID REFERENCES family_members(id) ON DELETE SET NULL,
  property_id       UUID REFERENCES properties(id) ON DELETE SET NULL,
  vehicle_id        UUID REFERENCES vehicles(id) ON DELETE SET NULL,
  doc_type          TEXT NOT NULL,  -- 'Passport'|'Aadhaar'|'PAN'|'DL'|'Visa'|'Other'
  name              TEXT NOT NULL,
  doc_number        TEXT,
  issuing_authority TEXT,
  issue_date        DATE,
  expiry_date       DATE,
  is_non_expiring   BOOLEAN DEFAULT FALSE,
  status            TEXT DEFAULT 'pending',  -- 'valid'|'expiring_soon'|'expired'|'review_needed'
  file_path         TEXT NOT NULL,
  storage_type      TEXT NOT NULL DEFAULT 'local',
  ai_confidence     FLOAT,
  ai_raw_response   JSONB,
  notes             TEXT,
  created_at        TIMESTAMPTZ DEFAULT NOW(),
  updated_at        TIMESTAMPTZ DEFAULT NOW()
);

-- PASSPORT PHOTOGRAPHS
CREATE TABLE photographs (
  id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  owner_id    UUID REFERENCES users(id) ON DELETE CASCADE,
  member_id   UUID REFERENCES family_members(id) ON DELETE CASCADE,
  taken_date  DATE NOT NULL,
  file_path   TEXT NOT NULL,
  notes       TEXT,
  created_at  TIMESTAMPTZ DEFAULT NOW()
);

-- FAMILY EXPENSES
CREATE TABLE expenses (
  id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  owner_id    UUID REFERENCES users(id) ON DELETE CASCADE,
  category    TEXT NOT NULL,
  amount      NUMERIC(10,2) NOT NULL,
  expense_date DATE NOT NULL,
  description TEXT,
  receipt_path TEXT,
  created_at  TIMESTAMPTZ DEFAULT NOW()
);

-- INCOME
CREATE TABLE income (
  id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  owner_id    UUID REFERENCES users(id) ON DELETE CASCADE,
  source      TEXT NOT NULL,  -- 'Salary'|'Rental'|'Freelance'|'Dividend'|'Interest'|'Other'
  amount      NUMERIC(10,2) NOT NULL,
  income_date DATE NOT NULL,
  description TEXT
);

-- RENTAL RECORDS
CREATE TABLE rental_records (
  id            UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  property_id   UUID REFERENCES properties(id) ON DELETE CASCADE,
  tenant_name   TEXT NOT NULL,
  tenant_phone  TEXT,
  tenant_email  TEXT,
  move_in_date  DATE NOT NULL,
  move_out_date DATE,
  expected_rent NUMERIC(10,2) NOT NULL,
  security_deposit NUMERIC(10,2),
  agreement_path TEXT,
  agreement_end  DATE,
  is_active     BOOLEAN DEFAULT TRUE
);

-- RENT PAYMENTS
CREATE TABLE rent_payments (
  id            UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  rental_id     UUID REFERENCES rental_records(id) ON DELETE CASCADE,
  period_month  INTEGER NOT NULL,  -- 1-12
  period_year   INTEGER NOT NULL,
  expected      NUMERIC(10,2) NOT NULL,
  received      NUMERIC(10,2) NOT NULL DEFAULT 0,
  payment_date  DATE,
  payment_mode  TEXT,  -- 'cash'|'bank_transfer'|'upi'|'cheque'
  notes         TEXT
);

-- INVESTMENTS
CREATE TABLE investments (
  id            UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  owner_id      UUID REFERENCES users(id) ON DELETE CASCADE,
  type          TEXT NOT NULL,  -- 'stocks'|'mutual_fund'|'fd'|'ppf'|'nps'|'bonds'|'gold'|'other'
  name          TEXT NOT NULL,
  institution   TEXT,
  account_no    TEXT,
  invested      NUMERIC(14,2),
  current_value NUMERIC(14,2),
  start_date    DATE,
  maturity_date DATE,
  notes         TEXT,
  updated_at    TIMESTAMPTZ DEFAULT NOW()
);

-- LOANS
CREATE TABLE loans (
  id            UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  owner_id      UUID REFERENCES users(id) ON DELETE CASCADE,
  type          TEXT NOT NULL,  -- 'home'|'vehicle'|'personal'|'education'|'other'
  lender        TEXT NOT NULL,
  principal     NUMERIC(14,2) NOT NULL,
  interest_rate NUMERIC(5,2),
  emi           NUMERIC(10,2) NOT NULL,
  emi_due_day   INTEGER NOT NULL,  -- day of month (1-28)
  start_date    DATE NOT NULL,
  end_date      DATE NOT NULL,
  outstanding   NUMERIC(14,2)
);

-- ALERTS LOG
CREATE TABLE alert_logs (
  id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  alert_type  TEXT NOT NULL,  -- 'document_expiry'|'vehicle_insurance'|'property_tax'|etc.
  ref_id      UUID,           -- ID of the record that triggered alert
  sent_to     TEXT NOT NULL,
  days_before INTEGER,
  sent_at     TIMESTAMPTZ DEFAULT NOW(),
  status      TEXT NOT NULL   -- 'sent'|'failed'
);

-- AUDIT LOG (tracks all data changes)
CREATE TABLE audit_logs (
  id         UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id    UUID REFERENCES users(id) ON DELETE SET NULL,
  action     TEXT NOT NULL,     -- 'INSERT'|'UPDATE'|'DELETE'
  table_name TEXT NOT NULL,
  record_id  UUID,
  old_data   JSONB,
  new_data   JSONB,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- SYSTEM SETTINGS (fully self-contained — no external auth provider keys)
CREATE TABLE system_settings (
  key        TEXT PRIMARY KEY,
  value      TEXT NOT NULL,     -- AES-256-GCM encrypted for secrets
  is_secret  BOOLEAN NOT NULL DEFAULT FALSE,
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Seed default settings
INSERT INTO system_settings (key, value, is_secret) VALUES
  ('smtp_host',           'smtp.gmail.com',  FALSE),
  ('smtp_port',           '587',             FALSE),
  ('smtp_email',          '',                FALSE),
  ('smtp_password',       '',                TRUE),
  ('smtp_from_name',      'DocuWealth',      FALSE),
  ('docai_api_key',       '',                TRUE),
  ('docai_processor_id',  '',                FALSE),
  ('storage_path',        '/var/docuwealth/storage', FALSE),
  ('alert_schedule',      '0 8 * * *',       FALSE),
  ('global_alert_days',   '[90,30,7]',       FALSE),
  ('jwt_secret',          '',                TRUE),
  ('encryption_key',      '',                TRUE);

🗓️ 3. Development Phases & Tasks

📁 4. Project Folder Structure

docuwealth/
├── apps/
│   ├── web/                        # React 18 + TypeScript + Vite
│   │   ├── src/
│   │   │   ├── components/         # Reusable UI components
│   │   │   ├── pages/
│   │   │   │   ├── Dashboard/
│   │   │   │   ├── Properties/
│   │   │   │   ├── Vehicles/
│   │   │   │   ├── Insurance/
│   │   │   │   ├── Documents/
│   │   │   │   ├── Finances/
│   │   │   │   ├── Rental/
│   │   │   │   ├── Investments/
│   │   │   │   ├── Photographs/
│   │   │   │   ├── Alerts/
│   │   │   │   └── Settings/
│   │   │   ├── hooks/
│   │   │   ├── lib/                # api.ts, auth.ts, utils.ts
│   │   │   └── types/
│   │   └── public/
│   │
│   └── android/                    # React Native + Expo
│       └── src/
│           ├── screens/
│           ├── components/
│           ├── navigation/
│           └── services/
│
├── server/                         # Node.js + Express + TypeScript
│   └── src/
│       ├── routes/                 # properties, vehicles, insurance, docs, finances, rental, investments, photos, alerts, settings
│       ├── middleware/             # auth.ts, rateLimit.ts, upload.ts
│       └── services/
│           ├── documentAI.ts
│           ├── storage.ts          # local + S3 abstraction
│           ├── mailer.ts
│           ├── scheduler.ts        # unified cron job
│           └── geocoding.ts
│
├── db/
│   ├── schema/                     # Drizzle ORM schema definitions (TypeScript)
│   ├── migrations/                 # Generated SQL migration files (numbered)
│   ├── seed.ts                     # Seed script (default settings, first admin)
│   └── drizzle.config.ts
│
└── docs/                           # This requirements documentation
    ├── index.html
    ├── Business_Requirements.html
    ├── Stakeholder_Requirements.html
    ├── User_Requirements.html
    ├── System_Requirements.html
    ├── Software_Requirements.html
    └── Task-Done.html