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.

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 below.

Users

Users

Stores user account information and Gmail connection state.

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.

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:

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.

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.

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.