> ## Documentation Index
> Fetch the complete documentation index at: https://mintlify.com/azfar-imtiaz/PayPulse-Cloud/llms.txt
> Use this file to discover all available pages before exploring further.

# DynamoDB tables

> All DynamoDB tables, their schemas, and indexing strategies.

PayPulse Cloud uses 11 DynamoDB tables defined in `aws-infra-terraform/dynamodb.tf`. All tables have server-side encryption enabled.

## Rental invoices

### RentalInvoices

Stores parsed rental invoice data. This is the primary table for the rental invoice feature.

```hcl theme={null}
resource "aws_dynamodb_table" "rental_invoices" {
  name           = "RentalInvoices"
  billing_mode   = "PROVISIONED"
  read_capacity  = 5
  write_capacity = 5

  hash_key  = "UserID"
  range_key = "InvoiceID"

  global_secondary_index {
    name            = "due_date_year-due_date_month-index"
    hash_key        = "due_date_year"
    range_key       = "due_date_month"
    projection_type = "ALL"
    read_capacity   = 1
    write_capacity  = 1
  }

  stream_enabled   = true
  stream_view_type = "NEW_AND_OLD_IMAGES"

  server_side_encryption {
    enabled = true
  }
}
```

| Property      | Value                                                                                 |
| ------------- | ------------------------------------------------------------------------------------- |
| Billing mode  | `PROVISIONED` (5 RCU / 5 WCU base)                                                    |
| Partition key | `UserID` (String)                                                                     |
| Sort key      | `InvoiceID` (String)                                                                  |
| GSI           | `due_date_year-due_date_month-index` (hash: `due_date_year`, range: `due_date_month`) |
| Stream        | `NEW_AND_OLD_IMAGES`                                                                  |
| Encryption    | Server-side (AES-256)                                                                 |

The DynamoDB stream on this table triggers the `send_invoice_notification` Lambda whenever a new invoice record is inserted.

Autoscaling is configured separately — see [Autoscaling](#autoscaling) below.

## Users

### Users

Stores user account information and Gmail connection state.

```hcl theme={null}
resource "aws_dynamodb_table" "users" {
  name         = "Users"
  billing_mode = "PAY_PER_REQUEST"

  hash_key = "UserID"

  global_secondary_index {
    hash_key        = "Email"
    name            = "Email-index"
    projection_type = "ALL"
  }

  tags = {
    Environment = "production"
  }
}
```

| Property      | Value                         |
| ------------- | ----------------------------- |
| Billing mode  | `PAY_PER_REQUEST`             |
| Partition key | `UserID` (String)             |
| GSI           | `Email-index` (hash: `Email`) |

The `Email-index` GSI supports login lookups by email address without a full table scan. The table also stores a `last_retail_invoice_fetch` timestamp used for incremental retail invoice fetching.

## Retail invoices

### RetailInvoices

Base table for retail invoices. Contains summary metadata for every retail invoice across all categories.

```hcl theme={null}
resource "aws_dynamodb_table" "retail_invoices" {
  name         = "RetailInvoices"
  billing_mode = "PAY_PER_REQUEST"

  hash_key  = "UserID"
  range_key = "InvoiceID"

  # GSI-1: date-based queries per user
  global_secondary_index {
    name            = "invoice_date-index"
    hash_key        = "UserID"
    range_key       = "invoice_date"
    projection_type = "ALL"
  }

  # GSI-2: category + date queries
  global_secondary_index {
    name            = "sub_type-invoice_date-index"
    hash_key        = "UserID_SubType"
    range_key       = "invoice_date"
    projection_type = "ALL"
  }

  stream_enabled   = true
  stream_view_type = "NEW_AND_OLD_IMAGES"

  point_in_time_recovery {
    enabled = true
  }

  server_side_encryption {
    enabled = true
  }
}
```

| Property               | Value                                                                         |
| ---------------------- | ----------------------------------------------------------------------------- |
| Billing mode           | `PAY_PER_REQUEST`                                                             |
| Partition key          | `UserID` (String)                                                             |
| Sort key               | `InvoiceID` (String)                                                          |
| GSI-1                  | `invoice_date-index` (hash: `UserID`, range: `invoice_date`)                  |
| GSI-2                  | `sub_type-invoice_date-index` (hash: `UserID_SubType`, range: `invoice_date`) |
| Stream                 | `NEW_AND_OLD_IMAGES`                                                          |
| Point-in-time recovery | Enabled                                                                       |
| Encryption             | Server-side (AES-256)                                                         |

GSI-2 uses a composite key `UserID_SubType` (e.g., `user_abc123_food-delivery`) to efficiently retrieve all invoices for a user within a specific category.

Attributes stored per item include: `vendor_name`, `sub_type`, `total_amount`, `currency`, `invoice_date`, `s3_path`.

### Retail detail tables

Each retail category has a dedicated detail table keyed by `InvoiceID`. These hold category-specific line-item data parsed from the invoice HTML.

| Table name                     | Variable default               |
| ------------------------------ | ------------------------------ |
| `FoodDeliveryInvoices`         | `food_delivery_invoices_table` |
| `ClothingInvoices`             | `clothing_invoices_table`      |
| `TechnologyInvoices`           | `technology_invoices_table`    |
| `SubscriptionInvoices`         | `subscription_invoices_table`  |
| `GroceryInvoices`              | `grocery_invoices_table`       |
| `MiscellaneousUtilityInvoices` | `misc_utility_invoices_table`  |
| `MiscellaneousInvoices`        | `misc_invoices_table`          |
| `TravelInvoices`               | `travel_invoices_table`        |

All eight tables share the same schema:

```hcl theme={null}
resource "aws_dynamodb_table" "food_delivery_invoices" {
  name         = "FoodDeliveryInvoices"
  billing_mode = "PAY_PER_REQUEST"
  hash_key     = "InvoiceID"

  attribute {
    name = "InvoiceID"
    type = "S"
  }

  server_side_encryption {
    enabled = true
  }

  tags = {
    Environment = "production"
  }
}
```

| Property      | Value                 |
| ------------- | --------------------- |
| Billing mode  | `PAY_PER_REQUEST`     |
| Partition key | `InvoiceID` (String)  |
| Encryption    | Server-side (AES-256) |

## Vendor configuration

### VendorConfig

Drives automated retail invoice fetching. Each item represents one vendor with email patterns, subject keywords, and parser configuration.

```hcl theme={null}
resource "aws_dynamodb_table" "vendor_config" {
  name         = "VendorConfig"
  billing_mode = "PAY_PER_REQUEST"
  hash_key     = "vendor_id"

  server_side_encryption {
    enabled = true
  }
}
```

| Property      | Value                 |
| ------------- | --------------------- |
| Billing mode  | `PAY_PER_REQUEST`     |
| Partition key | `vendor_id` (String)  |
| Encryption    | Server-side (AES-256) |

Attributes per item include: `vendor_name`, `invoice_sub_type`, `email_patterns`, `subject_keywords`, `parser_type`, and an `active` status flag. Adding a new vendor to this table enables invoice fetching for that vendor without any code changes.

## Autoscaling

Read and write autoscaling is configured for the `RentalInvoices` table in `dynamodb_autoscaling.tf`.

```hcl theme={null}
resource "aws_appautoscaling_target" "dynamodb_read_target" {
  min_capacity       = 1
  max_capacity       = 10
  resource_id        = "table/RentalInvoices"
  scalable_dimension = "dynamodb:table:ReadCapacityUnits"
  service_namespace  = "dynamodb"
}

resource "aws_appautoscaling_policy" "dynamodb_read_policy" {
  name        = "DynamoDBReadAutoScalingPolicy"
  policy_type = "TargetTrackingScaling"

  target_tracking_scaling_policy_configuration {
    predefined_metric_specification {
      predefined_metric_type = "DynamoDBReadCapacityUtilization"
    }
    target_value       = 70.0
    scale_in_cooldown  = 60
    scale_out_cooldown = 60
  }
}
```

| Setting            | Value      |
| ------------------ | ---------- |
| Min capacity       | 1 unit     |
| Max capacity       | 10 units   |
| Target utilization | 70%        |
| Scale-in cooldown  | 60 seconds |
| Scale-out cooldown | 60 seconds |

Both read and write capacity are scaled independently using the same target utilization (70%). All other tables use `PAY_PER_REQUEST` billing and do not require autoscaling configuration.