Landlord Expense Tracker

Project Blueprint: Landlord Expense Tracker

1. The Business Problem (Why build this?)

The real estate investment landscape, particularly for independent landlords and small-to-medium property managers, is fraught with administrative challenges. While the allure of passive income is strong, the reality involves meticulous record-keeping, compliance with tax regulations, and accurate financial reporting. The current state-of-the-art for many landlords often involves a patchwork of manual processes:

• Spreadsheet Overload: Countless hours spent manually entering income and expense data into Excel or Google Sheets, prone to human error and difficult to audit.
• Scattered Records: Physical receipts, digital invoices, and bank statements are often fragmented across email inboxes, shoeboxes, and cloud drives, making consolidation a nightmare, especially during tax season.
• Tax Deduction Blind Spots: Many landlords unknowingly miss out on legitimate tax deductions due due to poor categorization, lack of awareness of deductible items, or simply losing track of eligible expenses. This directly impacts profitability.
• Audit Risk: Inaccurate or incomplete records increase the risk of audits and can lead to penalties and stress.
• Lack of Real-time Insight: Without consolidated, easily accessible data, landlords struggle to gain a clear, real-time financial picture of their properties' performance, hindering informed decision-making regarding rent adjustments, maintenance budgeting, and future investments.
• Time Consumption: The sheer volume of manual data entry and reconciliation consumes valuable time that could otherwise be spent on property improvements, tenant relations, or portfolio expansion.

Existing solutions often fall short. Generic accounting software can be overly complex or lack property-specific features. Basic landlord tools may offer tracking but lack the intelligence for automated categorization or robust receipt processing. The core problem is the inefficiency and potential for significant financial loss (through missed deductions) stemming from the manual, error-prone management of rental property finances.

This project aims to directly address these pain points by providing a specialized, intelligent, and user-friendly platform designed to streamline financial management, maximize tax efficiency, and offer clear insights for landlords.

2. Solution Overview

The Landlord Expense Tracker is envisioned as an intuitive, cloud-based application that empowers landlords to effortlessly manage the financial aspects of their rental properties. It will centralize all income and expense data, automate tedious data entry through advanced AI, and provide actionable financial reports tailored for tax preparation.

Key Features:

1. Income & Expense Tracking:

   • Detailed Transaction Entry: Users can manually log income (rent payments, laundry fees, etc.) and expenses (mortgage, repairs, utilities, insurance, property taxes, management fees, advertising, etc.) with associated dates, amounts, descriptions, and property affiliations.
   • Categorization: A pre-defined, customizable list of tax-relevant categories (e.g., "Advertising," "Auto and Travel," "Cleaning and Maintenance," "Insurance," "Legal and Other Professional Fees," "Management Fees," "Mortgage Interest," "Repairs," "Supplies," "Taxes," "Utilities") to ensure proper classification for tax purposes.
   • Recurring Transactions: Ability to set up recurring income (e.g., monthly rent) or expenses (e.g., monthly mortgage, insurance premiums) to automate data entry.
   • Bank/Credit Card Feed (Future Scope): Integration with financial institutions for automatic transaction import and reconciliation, leveraging AI for initial categorization.

2. Tax-Deductible Identification:

   • Intelligent Categorization: Leveraging AI (Gemini) to suggest categories for manual entries and automatically categorize OCR-processed receipts.
   • Tax Summary Reports: Generate reports that aggregate expenses by IRS-defined deductible categories, providing a "tax-ready" summary for Schedule E (Form 1040).
   • Guidance & Tips: Contextual information on common deductions as users categorize transactions.

3. Property-Specific Reports:

   • Profit & Loss (P&L) Statement: Generate P&L reports for individual properties or the entire portfolio over custom date ranges.
   • Cash Flow Analysis: Track the actual cash inflows and outflows to understand liquidity.
   • Expense Breakdown: Visualizations (charts, graphs) of expense distribution by category or property.
   • Customizable Reports: Users can filter and sort data to create specific reports (e.g., all repair expenses for Property A in Q3).

4. Receipt Upload (OCR) with Gemini API:

   • Seamless Upload: Users can upload images or PDFs of receipts and invoices via web or mobile.
   • AI-Powered Data Extraction: The Gemini API will automatically extract key information: merchant name, transaction date, total amount, line items, currency, and suggest a transaction category.
   • Auto-Creation of Transactions: Extracted data is used to pre-populate and suggest new expense transactions, minimizing manual input.
   • Digital Archiving: Receipts are securely stored and linked to their respective transactions, eliminating the need for physical copies.

High-Level User Flow:

1. Onboarding: User signs up, creates an account, and sets up their first property.
2. Data Entry: User either manually enters income/expense transactions or uploads a receipt.
3. AI Processing (for receipts): Uploaded receipt is processed by Gemini, data is extracted and presented to the user for review.
4. Review & Confirm: User reviews AI-extracted data, makes any necessary corrections, and confirms the transaction.
5. Reporting: User generates various financial reports as needed.

3. Architecture & Tech Stack Justification

The architecture prioritizes scalability, security, cost-efficiency, and developer productivity, leveraging Google Cloud Platform (GCP) and a modern web development stack.

Core Tech Stack:

• Frontend & Backend (API Routes): Next.js (React)
• Styling: Tailwind CSS
• Database: PostgreSQL (via Google Cloud SQL)
• Object Storage: Google Cloud Storage (GCS)
• AI/ML: Gemini API (Google Cloud Vertex AI)
• Asynchronous Processing: Google Cloud Pub/Sub
• Serverless Compute: Google Cloud Functions / Cloud Run

Architectural Overview:

+----------------+       +-------------------+       +--------------------+
|                |       | Next.js App       |       | Google Cloud       |
|  User (Browser)| <---> | (Frontend & API)  | <---> | Storage (GCS)      |
|                |       |                   |       | (Receipts)         |
+----------------+       +-------------------+       +--------------------+
         ^                       |                           |
         | NextAuth.js           |                           |
         |                       v                           v
+-----------------+       +-------------------+       +--------------------+
|  Google Cloud   |       | Google Cloud      |       | Gemini API         |
|  SQL (Postgres) | <---> | Pub/Sub           | <---> | (Vertex AI)        |
|  (Data Storage) |       | (Message Queue)   |       | (OCR Processing)   |
+-----------------+       +-------------------+       +--------------------+
         ^                       |
         |                       v
         |               +--------------------+
         |               | Google Cloud       |
         +-------------< | Functions /        |
                         | Cloud Run Worker   |
                         | (OCR Processor)    |
                         +--------------------+

Tech Stack Justification:

1. Next.js (Frontend & Backend API Routes):

   • Full-stack Framework: Enables building both the React frontend and API endpoints within a single codebase, streamlining development and deployment.
   • Server-Side Rendering (SSR) / Static Site Generation (SSG): Provides flexibility for performance optimization, although much of this application will leverage Client-Side Rendering (CSR) for dynamic user interfaces and API Routes for backend logic.
   • API Routes: Perfect for handling authentication, database interactions, proxying requests to GCS for pre-signed URLs, and initiating asynchronous OCR tasks. This serves as our primary backend without requiring a separate server.
   • Developer Experience: Hot reloading, intuitive file-system based routing, and a large community contribute to high development velocity.

2. Tailwind CSS:

   • Utility-First Approach: Speeds up UI development by composing designs directly in HTML with low-level utility classes.
   • Consistency: Encourages consistent design patterns across the application.
   • Performance: Generates minimal CSS by only including utilities used, leading to smaller bundle sizes.

3. PostgreSQL (Google Cloud SQL):

   • Relational Database: Ideal for structured financial data (users, properties, transactions, categories, receipts) where ACID compliance and complex relationships are crucial.
   • Scalability & Reliability: Cloud SQL offers managed services, automatic backups, replication, and scaling, reducing operational overhead.
   • Robustness: A mature, open-source database with strong community support.

4. Google Cloud Storage (GCS):

   • Object Storage: Perfect for storing raw receipt images and PDFs.
   • Scalability & Durability: Designed for exabyte-scale data storage with high durability.
   • Security: Fine-grained access control (IAM), encryption at rest and in transit.
   • Pre-signed URLs: Allows secure, time-limited direct uploads from the client, offloading the application server.

5. Gemini API (Vertex AI):

   • Multimodal AI: Crucial for the receipt OCR feature. Gemini's advanced vision capabilities can not only extract text but also understand the semantic context of a document like a receipt, leading to more accurate structured data extraction and intelligent categorization.
   • Integration with GCP: Seamless integration with other Google Cloud services.
   • Scalability: Vertex AI provides a managed, scalable platform for interacting with Gemini models.

6. Google Cloud Pub/Sub:

   • Asynchronous Messaging: Decouples the frontend's receipt upload process from the computationally intensive OCR task. When a user uploads a receipt, the Next.js API route pushes a message to Pub/Sub.
   • Reliability & Durability: Guarantees message delivery, retries, and high throughput.
   • Scalability: Automatically scales to handle bursts of messages.

7. Google Cloud Functions / Cloud Run (for OCR Worker):

   • Serverless Compute: Automatically scales up and down based on demand, including scaling to zero when idle, offering cost efficiency.
   • Cloud Functions: Ideal for event-driven workloads like responding to Pub/Sub messages. A function can be triggered directly by a new message in the receipt-processing queue.
   • Cloud Run: A good alternative for the OCR worker if more control over the container environment or longer processing times are anticipated, still offering serverless benefits. It's suitable for running arbitrary containerized workloads.

Security Considerations:

• Authentication: NextAuth.js for robust session management and support for various authentication providers (e.g., Google, email/password).
• Authorization: Implement Row-Level Security (RLS) in PostgreSQL or application-level checks to ensure users can only access their own properties and transactions.
• Data Encryption: All data at rest in GCS and Cloud SQL is encrypted. Data in transit uses TLS.
• API Key Management: Store sensitive API keys (Gemini API, database credentials) securely using Google Secret Manager and access them via environment variables at runtime.
• Input Validation: Strict validation on all user inputs to prevent injection attacks and ensure data integrity.
• Least Privilege: Service accounts used by Cloud Functions/Run should only have the minimum necessary IAM permissions (e.g., read/write to specific GCS buckets, invoke Gemini API, write to Pub/Sub, write to specific Cloud SQL tables).

4. Core Feature Implementation Guide

A. User & Property Management

Database Schema (PostgreSQL):

-- Users Table
CREATE TABLE users (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    email VARCHAR(255) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- Properties Table
CREATE TABLE properties (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    name VARCHAR(255) NOT NULL,
    address TEXT,
    purchase_date DATE,
    purchase_price DECIMAL(15, 2),
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- Index for faster lookup by user_id
CREATE INDEX idx_properties_user_id ON properties(user_id);

Next.js API Routes (Conceptual):

• pages/api/auth/register.ts: Handles user registration, hashes password, saves to users table.
• pages/api/auth/login.ts: Authenticates user, creates session.
• pages/api/properties/index.ts:
  • GET: Fetches all properties for the authenticated user.
  • POST: Creates a new property for the authenticated user.
• pages/api/properties/[id].ts:
  • GET: Fetches a specific property by ID for the authenticated user.
  • PUT: Updates a specific property.
  • DELETE: Deletes a specific property and cascades to associated transactions/receipts.

B. Transaction Management (Income/Expense)

Database Schema (PostgreSQL):

-- Categories Table (IRS-like categories)
CREATE TABLE categories (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name VARCHAR(255) UNIQUE NOT NULL,
    type VARCHAR(10) NOT NULL CHECK (type IN ('INCOME', 'EXPENSE')),
    is_tax_deductible BOOLEAN DEFAULT TRUE, -- For expenses
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- Example initial categories (seeded data)
-- INSERT INTO categories (name, type, is_tax_deductible) VALUES
-- ('Rent Income', 'INCOME', FALSE), ('Laundry Fees', 'INCOME', FALSE),
-- ('Advertising', 'EXPENSE', TRUE), ('Repairs', 'EXPENSE', TRUE), ...

-- Transactions Table
CREATE TABLE transactions (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    property_id UUID NOT NULL REFERENCES properties(id) ON DELETE CASCADE,
    category_id UUID NOT NULL REFERENCES categories(id) ON DELETE RESTRICT,
    type VARCHAR(10) NOT NULL CHECK (type IN ('INCOME', 'EXPENSE')),
    amount DECIMAL(15, 2) NOT NULL,
    transaction_date DATE NOT NULL,
    description TEXT,
    is_recurring BOOLEAN DEFAULT FALSE,
    recurring_interval VARCHAR(50), -- e.g., 'MONTHLY', 'QUARTERLY'
    receipt_id UUID REFERENCES receipts(id) ON DELETE SET NULL, -- Link to an optional receipt
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- Indexes for performance
CREATE INDEX idx_transactions_user_id ON transactions(user_id);
CREATE INDEX idx_transactions_property_id ON transactions(property_id);
CREATE INDEX idx_transactions_transaction_date ON transactions(transaction_date);

Next.js API Routes:

• pages/api/transactions/index.ts:
  • GET: Fetches transactions for the authenticated user, with optional filters (property_id, date range, type).
  • POST: Creates a new transaction.
• pages/api/transactions/[id].ts: CRUD operations for a specific transaction.
• pages/api/categories/index.ts: Fetches predefined categories.

C. Receipt Upload & OCR Pipeline

This is the most complex and critical feature, involving client-side upload, serverless backend processing, and AI integration.

Database Schema (PostgreSQL):

CREATE TYPE RECEIPT_STATUS AS ENUM ('UPLOADED', 'PENDING_OCR', 'PROCESSED', 'OCR_FAILED', 'MANUAL_ENTRY');

CREATE TABLE receipts (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    property_id UUID NOT NULL REFERENCES properties(id) ON DELETE CASCADE,
    gcs_uri VARCHAR(512) NOT NULL, -- Path to the original image in GCS
    status RECEIPT_STATUS DEFAULT 'UPLOADED',
    original_filename VARCHAR(255),
    extracted_data JSONB, -- Store raw JSON output from Gemini
    extracted_merchant_name VARCHAR(255),
    extracted_transaction_date DATE,
    extracted_total_amount DECIMAL(15, 2),
    extracted_category_suggestion VARCHAR(255),
    transaction_id UUID REFERENCES transactions(id) ON DELETE SET NULL, -- Link to the created/linked transaction
    uploaded_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    processed_at TIMESTAMP WITH TIME ZONE
);

CREATE INDEX idx_receipts_user_id ON receipts(user_id);
CREATE INDEX idx_receipts_property_id ON receipts(property_id);
CREATE INDEX idx_receipts_status ON receipts(status);

Pipeline Design:

1. Client-Side (Next.js Frontend):

   • User selects a file (image/PDF) via an <input type="file">.
   • Client makes a POST request to pages/api/receipts/upload-init.
   • Upon receiving a pre-signed GCS upload URL from the server, the client directly uploads the file to GCS using fetch or XMLHttpRequest with PUT method.
   • Displays upload progress to the user.
   • Once GCS upload is complete, the client notifies the server (e.g., by sending a POST to pages/api/receipts/complete-upload with the GCS URI).

2. Next.js API Route (pages/api/receipts/upload-init.ts):

   • Authenticates user.
   • Validates request (file type, size).
   • Generates a unique filename for GCS.
   • Creates a pre-signed PUT URL for GCS bucket.
   • Records a new receipt entry in the database with status: 'UPLOADED', gcs_uri, user_id, property_id.
   • Returns the pre-signed URL and the receipt_id to the client.

3. Next.js API Route (pages/api/receipts/complete-upload.ts):

   • Authenticates user.
   • Receives receipt_id and confirmed gcs_uri from client.
   • Updates the receipt status to PENDING_OCR.
   • Publishes a message to a Google Cloud Pub/Sub topic (e.g., receipt-processing) with the receipt_id and gcs_uri.
   • Returns success to the client.

4. OCR Worker (Google Cloud Function / Cloud Run Service):

   • This service is subscribed to the receipt-processing Pub/Sub topic.
   • When a message arrives, it triggers the function/service.
   • Logic:
     • Extract receipt_id and gcs_uri from the Pub/Sub message.
     • Call the Gemini API (Vertex AI) with the gcs_uri of the receipt image.
     • Parse the Gemini API response.
     • Update the receipt entry in the database with status: 'PROCESSED', extracted_data, extracted_merchant_name, extracted_transaction_date, extracted_total_amount, extracted_category_suggestion, and processed_at.
     • Optional: If confidence is high, automatically create a transaction entry and link it. Otherwise, leave for user review.
     • Handle errors: If Gemini API fails, update receipt status to OCR_FAILED and log details.

OCR Worker Pseudo-code (Python, for Cloud Function):

import functions_framework
import base64
import json
from google.cloud import aiplatform, pubsub_v1
from google.cloud import storage
import datetime
import os
import pg8000.dbapi # Or an ORM like SQLAlchemy

# Initialize clients (globally for efficiency in Cloud Functions)
aiplatform.init(project=os.environ.get('GCP_PROJECT_ID'), location=os.environ.get('GCP_REGION'))
prediction_client = aiplatform.PredictionServiceClient()
pubsub_publisher = pubsub_v1.PublisherClient()
storage_client = storage.Client()

DB_HOST = os.environ.get('DB_HOST')
DB_USER = os.environ.get('DB_USER')
DB_PASS = os.environ.get('DB_PASS')
DB_NAME = os.environ.get('DB_NAME')
PROJECT_ID = os.environ.get('GCP_PROJECT_ID')
LOCATION_ID = os.environ.get('GCP_REGION') # e.g., 'us-central1'
GEMINI_MODEL_ID = os.environ.get('GEMINI_MODEL_ID', 'gemini-pro-vision') # For multimodal vision

@functions_framework.cloud_event
def process_receipt_ocr(cloud_event):
    """Triggered by a Pub/Sub message to process a receipt."""
    data = base64.b64decode(cloud_event.data["message"]["data"]).decode()
    message_data = json.loads(data)

    receipt_id = message_data.get('receipt_id')
    gcs_uri = message_data.get('gcs_uri')

    if not receipt_id or not gcs_uri:
        print(f"Error: Missing receipt_id or gcs_uri in message: {message_data}")
        return

    db_conn = None
    try:
        db_conn = pg8000.dbapi.connect(host=DB_HOST, user=DB_USER, password=DB_PASS, database=DB_NAME)
        cursor = db_conn.cursor()

        print(f"Processing receipt_id: {receipt_id} from GCS: {gcs_uri}")

        # Construct the prompt for Gemini Vision
        # This prompt is critical for extracting structured data.
        prompt_template = """
        Analyze this receipt image and extract the following information.
        Format the output strictly as a JSON object.
        If a field is not found or ambiguous, set its value to null.

        {
            "merchantName": "STRING (e.g., 'Home Depot', 'Starbucks')",
            "transactionDate": "YYYY-MM-DD (e.g., '2023-10-26')",
            "totalAmount": "DECIMAL (e.g., 123.45)",
            "currency": "STRING (e.g., 'USD', 'CAD')",
            "lineItems": [
                {
                    "description": "STRING",
                    "quantity": "INTEGER",
                    "unitPrice": "DECIMAL",
                    "total": "DECIMAL"
                }
            ],
            "suggestedCategory": "STRING (Suggest one of these: 'Advertising', 'Auto and Travel', 'Cleaning and Maintenance', 'Commissions', 'Insurance', 'Legal and Other Professional Fees', 'Management Fees', 'Mortgage Interest', 'Other Interest', 'Repairs', 'Supplies', 'Taxes', 'Utilities', 'Other Expenses')",
            "confidenceScore": "DECIMAL (Internal metric, not directly from Gemini, but useful for app logic)"
        }
        """

        # Prepare the request to Gemini API
        # The multimodal model expects content parts.
        # For an actual document parser, Google Cloud Document AI might be more specific.
        # Here we leverage Gemini's multimodal understanding.
        contents = [
            {"text": prompt_template},
            {"fileData": {"mimeType": "image/jpeg", "fileUri": gcs_uri}} # Assuming JPEG; handle other types
        ]

        instance = aiplatform.types.PredictInstance(
            struct_value={"contents": contents}
        )
        parameters = aiplatform.types.PredictParameters(
            temperature=0.2, # Lower temperature for more deterministic output
            max_output_tokens=1024
        )

        endpoint_id = f"projects/{PROJECT_ID}/locations/{LOCATION_ID}/endpoints/{GEMINI_MODEL_ID}"
        response = prediction_client.predict(
            endpoint=endpoint_id,
            instances=[instance],
            parameters=parameters,
        )

        gemini_output_text = response.predictions[0]['struct_value']['parts'][0]['text']
        print(f"Gemini raw output: {gemini_output_text}")

        # Attempt to parse Gemini's JSON output
        try:
            extracted_data = json.loads(gemini_output_text)
        except json.JSONDecodeError as e:
            raise ValueError(f"Failed to parse Gemini JSON output: {e} - Raw: {gemini_output_text}")

        # Extract specific fields for the transactions table
        merchant_name = extracted_data.get('merchantName')
        transaction_date_str = extracted_data.get('transactionDate')
        total_amount = extracted_data.get('totalAmount')
        suggested_category_name = extracted_data.get('suggestedCategory')

        transaction_date = None
        if transaction_date_str:
            try:
                transaction_date = datetime.datetime.strptime(transaction_date_str, '%Y-%m-%d').date()
            except ValueError:
                print(f"Warning: Could not parse transaction_date '{transaction_date_str}'")

        # Get category_id based on suggested_category_name
        category_id = None
        if suggested_category_name:
            cursor.execute("SELECT id FROM categories WHERE name = %s", (suggested_category_name,))
            cat_row = cursor.fetchone()
            if cat_row:
                category_id = cat_row[0]
            else:
                print(f"Warning: Suggested category '{suggested_category_name}' not found in DB.")

        # Update the receipt record with extracted data
        update_query = """
            UPDATE receipts
            SET status = 'PROCESSED',
                extracted_data = %s,
                extracted_merchant_name = %s,
                extracted_transaction_date = %s,
                extracted_total_amount = %s,
                extracted_category_suggestion = %s,
                processed_at = CURRENT_TIMESTAMP
            WHERE id = %s
        """
        cursor.execute(update_query, (
            json.dumps(extracted_data), # Store full Gemini output
            merchant_name,
            transaction_date,
            total_amount,
            suggested_category_name,
            receipt_id
        ))

        # Potentially create a transaction if confidence is high and data is complete
        if merchant_name and transaction_date and total_amount is not None and category_id:
            user_id, property_id = None, None
            cursor.execute("SELECT user_id, property_id FROM receipts WHERE id = %s", (receipt_id,))
            receipt_info = cursor.fetchone()
            if receipt_info:
                user_id, property_id = receipt_info

            if user_id and property_id:
                insert_transaction_query = """
                    INSERT INTO transactions (user_id, property_id, category_id, type, amount, transaction_date, description, receipt_id)
                    VALUES (%s, %s, %s, 'EXPENSE', %s, %s, %s, %s)
                    RETURNING id
                """
                description = f"OCR: {merchant_name}"
                cursor.execute(insert_transaction_query, (
                    user_id,
                    property_id,
                    category_id,
                    total_amount,
                    transaction_date,
                    description,
                    receipt_id
                ))
                new_transaction_id = cursor.fetchone()[0]
                # Link transaction back to receipt
                cursor.execute("UPDATE receipts SET transaction_id = %s WHERE id = %s", (new_transaction_id, receipt_id))
                print(f"Created new transaction {new_transaction_id} for receipt {receipt_id}")
            else:
                print(f"Warning: Could not determine user_id/property_id for transaction creation from receipt {receipt_id}")
        else:
            print(f"Not creating automatic transaction for {receipt_id} due to missing data.")


        db_conn.commit()
        print(f"Successfully processed receipt_id: {receipt_id}")

    except Exception as e:
        if db_conn:
            db_conn.rollback() # Rollback in case of error
            # Update receipt status to OCR_FAILED
            try:
                cursor = db_conn.cursor()
                cursor.execute("UPDATE receipts SET status = 'OCR_FAILED', processed_at = CURRENT_TIMESTAMP, extracted_data = %s WHERE id = %s", (json.dumps({"error": str(e)}), receipt_id))
                db_conn.commit()
            except Exception as update_e:
                print(f"Failed to update receipt status to OCR_FAILED for {receipt_id}: {update_e}")
        print(f"Error processing receipt {receipt_id}: {e}")
        # Consider publishing a message to a dead-letter queue for further investigation.
    finally:
        if db_conn:
            db_conn.close()

D. Reporting

• Next.js API Routes:

  • pages/api/reports/pnl.ts:
    • GET: Takes property_id, start_date, end_date as query parameters.
    • Performs SQL aggregations on transactions table, grouping by category_id, summing amount, and differentiating between INCOME and EXPENSE types.
    • Example SQL (simplified):

      SELECT c.name AS category, t.type, SUM(t.amount) AS total_amount
      FROM transactions t
      JOIN categories c ON t.category_id = c.id
      WHERE t.user_id = :user_id
      AND (:property_id IS NULL OR t.property_id = :property_id)
      AND t.transaction_date BETWEEN :start_date AND :end_date
      GROUP BY c.name, t.type
      ORDER BY t.type, c.name;
      

    • Returns structured JSON data for frontend visualization.
  • pages/api/reports/cash-flow.ts: Similar aggregation, but focusing on the temporal aspect of inflows/outflows.

• Frontend (React Components):

  • Utilize charting libraries (e.g., Chart.js, Recharts, Nivo) to render interactive bar charts, pie charts, and line graphs based on API data.
  • Display tabular data for detailed breakdowns.
  • Date pickers and property selectors for filtering reports.

5. Gemini Prompting Strategy

The effectiveness of the OCR pipeline hinges on precise and robust prompt engineering for the Gemini API. The goal is to maximize accuracy of data extraction and categorization from diverse receipt formats.

Core Principles:

1. Strict JSON Output: Always request a structured JSON response. This simplifies parsing and ensures data consistency. Specify the exact keys and expected data types.
2. Clear Instructions for Missing Data: Explicitly instruct Gemini what to do if information is not found (e.g., "If not found, use null"). This prevents unexpected string outputs.
3. Detailed Field Definitions: For each field, provide examples and specific formatting requirements (e.g., "YYYY-MM-DD" for dates, "DECIMAL" for amounts).
4. Constrained Categorization: Provide a fixed list of permissible categories for the suggestedCategory field. This guides Gemini towards relevant landlord-specific categories and simplifies downstream processing.
5. Multi-Modal Input: Leverage Gemini's vision capabilities by providing the actual image of the receipt, allowing it to "see" the document layout, fonts, and visual cues.

Refined Gemini Prompt Example (for process_receipt_ocr function):

[
  {
    "text": "You are an expert financial assistant specialized in real estate expense tracking. Your task is to accurately extract key financial data from the provided receipt image. Respond ONLY with a JSON object. Ensure all fields are present, using `null` if a value cannot be confidently identified. Prioritize precision and conciseness for each field. Do not include any additional text or explanation outside the JSON."
  },
  {
    "text": "Extract the following information:"
  },
  {
    "text": "1. `merchantName`: The name of the business from which the purchase was made. (e.g., 'The Home Depot', 'Verizon', 'Amazon.com')"
  },
  {
    "text": "2. `transactionDate`: The date of the transaction in 'YYYY-MM-DD' format. (e.g., '2023-11-05')"
  },
  {
    "text": "3. `totalAmount`: The grand total of the transaction as a floating-point number (e.g., 123.45). This should be the final amount paid."
  },
  {
    "text": "4. `currency`: The currency symbol or ISO code (e.g., 'USD', '$', 'EUR'). Default to 'USD' if ambiguous."
  },
  {
    "text": "5. `lineItems`: An array of objects, each representing a distinct item purchased. Each item object MUST have: `description` (STRING), `quantity` (INTEGER), `unitPrice` (DECIMAL), `total` (DECIMAL). If line items are not clearly itemized or available, provide an empty array `[]`."
  },
  {
    "text": "6. `suggestedCategory`: Based on the merchant and items, assign the most appropriate landlord expense category from this predefined list. If unsure, default to 'Other Expenses'.\nAllowed Categories: ['Advertising', 'Auto and Travel', 'Cleaning and Maintenance', 'Commissions', 'Insurance', 'Legal and Other Professional Fees', 'Management Fees', 'Mortgage Interest', 'Other Interest', 'Repairs', 'Supplies', 'Taxes', 'Utilities', 'Other Expenses']"
  },
  {
    "text": "Example of expected JSON format:"
  },
  {
    "text": "```json\n{\n  \"merchantName\": \"Walmart Supercenter\",\n  \"transactionDate\": \"2023-09-15\",\n  \"totalAmount\": 87.63,\n  \"currency\": \"USD\",\n  \"lineItems\": [\n    {\n      \"description\": \"Cleaning Wipes\",\n      \"quantity\": 2,\n      \"unitPrice\": 4.50,\n      \"total\": 9.00\n    },\n    {\n      \"description\": \"Light Bulbs (LED)\",\n      \"quantity\": 1,\n      \"unitPrice\": 25.99,\n      \"total\": 25.99\n    },\n    {\n      \"description\": \"Sales Tax\",\n      \"quantity\": null,\n      \"unitPrice\": null,\n      \"total\": 5.12\n    }\n  ],\n  \"suggestedCategory\": \"Cleaning and Maintenance\"\n}\n```"
  },
  {
    "text": "Now, process the following receipt image:"
  },
  {
    "fileData": {
      "mimeType": "image/jpeg",
      "fileUri": "gs://your-gcs-bucket/receipts/user-id/receipt-uuid.jpg"
    }
  }
]

Prompt Engineering Techniques Applied:

• Role-Playing: "You are an expert financial assistant..." sets the context.
• Explicit Output Format: "Respond ONLY with a JSON object. Ensure all fields are present, using null if a value cannot be confidently identified." is critical.
• Enumerated Instructions: Breaking down requirements into numbered points improves clarity.
• Allowed Values: Providing a Allowed Categories list for suggestedCategory makes the AI's output predictable and usable for database lookups.
• Few-Shot Example (Implicit): The Example of expected JSON format serves as a strong few-shot example, guiding the model on the desired structure and data types.
• Iterative Refinement: It's crucial to continuously monitor Gemini's output quality against real-world receipts and refine the prompt. This involves analyzing common errors (e.g., misinterpreting dates, incorrect totals, poor categorization) and adjusting instructions or providing more specific examples within the prompt. A/B testing different prompt variations will be part of ongoing optimization.

6. Deployment & Scaling

Leveraging Google Cloud Platform (GCP) offers a robust, scalable, and cost-effective environment for deploying the Landlord Expense Tracker.

A. Deployment Platform

1. Next.js Application (Frontend & API Routes):

   • Google Cloud Run: The primary deployment target. Cloud Run allows deploying containerized applications (Next.js builds to a Docker image) that are fully managed and scale automatically from zero to thousands of instances based on request traffic. It's ideal for Next.js API routes due to its rapid scaling and per-request billing.
   • CI/CD Integration: Cloud Build can automatically build the Docker image and deploy it to Cloud Run upon code commits.

2. Database (PostgreSQL):

   • Google Cloud SQL for PostgreSQL: A fully managed relational database service that handles patching, backups, replication, and scaling. It provides high availability and performance without the operational overhead of self-managing a database.

3. Object Storage (Receipts):

   • Google Cloud Storage (GCS): Receipts are stored in a dedicated GCS bucket. Access control is managed via IAM policies and pre-signed URLs.

4. OCR Worker (Cloud Function / Cloud Run):

   • Google Cloud Functions: For the Pub/Sub triggered OCR worker, Cloud Functions is a perfect fit. It's an event-driven serverless platform that automatically executes code in response to Pub/Sub messages, scaling instantly.
   • Alternative: Cloud Run Service: If the OCR processing is expected to be very long-running (exceeding Cloud Function limits) or requires a more complex environment, a dedicated Cloud Run service subscribed to Pub/Sub push messages could be used.

5. Message Queue:

   • Google Cloud Pub/Sub: Used for decoupling the receipt upload process from the OCR processing. It ensures reliable, asynchronous communication between services.

6. Authentication:

   • Firebase Authentication (Optional, but recommended for simplicity): Provides ready-to-use authentication UIs and backend services, supporting email/password, social logins, and more. Can be integrated with NextAuth.js.
   • NextAuth.js (as planned): Handles authentication logic within the Next.js app, offering flexibility and session management.

7. Secrets Management:

   • Google Secret Manager: Securely stores database credentials, API keys (Gemini, GCS), and other sensitive configurations, injecting them as environment variables into Cloud Run and Cloud Functions at runtime.

B. CI/CD Pipeline

A robust CI/CD pipeline ensures reliable and frequent deployments.

• Source Code Repository: GitHub, GitLab, or Google Cloud Source Repositories.
• Google Cloud Build:
  1. Trigger: Automatically starts a build upon git push to the main branch.
  2. Linting & Testing: Runs ESLint, Jest (unit/integration tests), Cypress (e2e tests) for Next.js app.
  3. Docker Build (for Next.js): Builds the Next.js application into a Docker image, leveraging multi-stage builds for optimal size.
  4. Artifact Registry: Pushes the built Docker image to Google Artifact Registry.
  5. Deployment (Cloud Run): Deploys the new image to the Cloud Run service, often with zero-downtime rolling updates.
  6. Cloud Functions Deployment: Deploys or updates the OCR worker Cloud Function.
  7. Database Migrations: Integrates database migration tools (e.g., Prisma Migrate or raw SQL scripts) to apply schema changes safely during deployment, ideally as a separate step or a pre-deployment hook.

C. Monitoring & Logging

• Google Cloud Monitoring:
  • Metrics: Collects performance metrics (CPU utilization, request latency, memory usage) for Cloud Run, Cloud Functions, and Cloud SQL.
  • Dashboards: Create custom dashboards to visualize key application and infrastructure health.
  • Alerting: Set up alerts for critical thresholds (e.g., high error rates, low database connections, increased Pub/Sub message backlog).
• Google Cloud Logging:
  • Structured Logging: All logs from Cloud Run, Cloud Functions, and Next.js (via console logs) are automatically ingested as structured logs, making them searchable and analyzable.
  • Error Reporting: Automatically captures and groups application errors, providing stack traces for quick debugging.
• Application Performance Management (APM): Consider integrating an APM tool (e.g., OpenTelemetry with Google Cloud Trace) for deeper insights into request flows, bottlenecks, and latency within the application.

D. Scaling Strategy

• Horizontal Scaling:

  • Cloud Run: Automatically scales the Next.js application horizontally based on incoming request load, configured with min/max instances and CPU utilization targets.
  • Cloud Functions: Designed for automatic scaling, handling concurrent invocations as Pub/Sub messages arrive.
  • Cloud SQL Read Replicas: For read-heavy workloads, Cloud SQL can provision read replicas, distributing read traffic and reducing load on the primary instance.

• Asynchronous Processing:

  • Cloud Pub/Sub: Acts as a buffer for the OCR workflow. Even if the OCR worker momentarily bottlenecks, messages queue up in Pub/Sub, ensuring no data loss and smooth processing as capacity becomes available.

• Caching:

  • Content Delivery Network (CDN): Google Cloud CDN for static assets (JavaScript bundles, CSS, images) served by Next.js, reducing latency and offloading the main application.
  • In-Memory Caching (Google Cloud Memorystore for Redis): For frequently accessed, non-critical data (e.g., user preferences, common report segments) to reduce database load.

• Database Optimization:

  • Indexing: Proper indexing on frequently queried columns (e.g., user_id, property_id, transaction_date) in PostgreSQL.
  • Connection Pooling: Efficient database connection management within the Next.js API routes and Cloud Functions.
  • Query Optimization: Regularly review and optimize slow SQL queries.

E. Security Best Practices

• Identity and Access Management (IAM): Strictly define roles and permissions for all GCP resources. Use service accounts with the principle of least privilege.
• Workload Identity: For Cloud Run and Cloud Functions to securely access other GCP services (Cloud SQL, GCS, Gemini) without managing API keys directly in code.
• VPC Service Controls: Establish security perimeters around sensitive data (Cloud SQL, GCS, Secret Manager) to prevent data exfiltration.
• Network Security: Utilize private IP for Cloud SQL connections from Cloud Run/Functions within a VPC to avoid exposing the database to the public internet.
• Web Application Firewall (WAF): Google Cloud Armor can protect the Next.js application from common web vulnerabilities (DDoS, SQL injection, XSS) at the edge.
• Regular Security Audits & Penetration Testing: Conduct periodic security assessments to identify and remediate vulnerabilities.
• Data Minimization: Only collect and store data absolutely necessary for the application's functionality.
• Data Retention Policies: Implement policies for retaining and deleting data in compliance with privacy regulations.