Database Architecture

Our system uses a multi-database approach, combining PostgreSQL for relational data and Firestore for real-time and document-based data needs.

Primary Database: PostgreSQL

We use Google Cloud SQL for PostgreSQL as our primary database, handling all critical transactional data.

Key Characteristics

Core Data Models

// User Management
model User {
  id            String    @id
  email         String    @unique
  hashedPassword String?
  role          UserRole
  name          String?
  createdAt     DateTime  @default(now())
  updatedAt     DateTime  @updatedAt
  apiKey        ApiKey?
  agentProfile  AgentProfile?
}

// Agent Profiles
model AgentProfile {
  id                 String   @id
  userId             String   @unique
  companyName        String?
  webhookUrl         String?
  webhookSecret      String?
  devices            Device[]
  transactions       Transaction[]
  payouts            Payout[]
  restrictedFeatures String[]
}

// Device Management
model Device {
  id               String    @id
  name             String
  deviceIdentifier String    @unique
  agentProfileId   String
  apiKey           String?
  status           String
  lastSeen         DateTime?
  transactions     Transaction[]
  payouts          Payout[]
}

// Transactions
model Transaction {
  id                  String            @id
  type                TransactionType
  status              TransactionStatus
  amount              Decimal
  currency            String
  bankTransactionId   String?
  serviceFee          Decimal?
  netAmount           Decimal?
  metadata            Json?
  webhookStatus       String?
  webhookAttempts     Int
}

Database Design Principles

1. Normalization

   • Properly normalized tables
   • Appropriate foreign key relationships
   • Indexes for query optimization

2. Data Integrity

   • Constraints enforcement
   • Referential integrity
   • Check constraints for business rules

3. Performance

   • Strategic indexing
   • Materialized views where needed
   • Query optimization

Secondary Database: Firestore

We use Firestore for specific use cases requiring real-time updates or document-based storage.

Use Cases

1. Device Status Tracking

interface DeviceStatus {
  deviceId: string;
  status: 'ONLINE' | 'OFFLINE' | 'ERROR';
  lastHeartbeat: Timestamp;
  currentError?: string;
}

2. Real-time Updates

interface TransactionUpdate {
  transactionId: string;
  status: string;
  timestamp: Timestamp;
  metadata: Record<string, any>;
}

Firestore Collections

• device_status: Real-time device status
• transaction_updates: Live transaction feeds
• temporary_session_data: Short-lived session info

Data Access Patterns

Service-Specific Access

Each microservice has specific data access patterns:

1. User Service

   • User CRUD operations
   • Profile management
   • API key operations

2. Transaction Service

   • Transaction creation and updates
   • Status tracking
   • Fee calculations

3. Device Service

   • Device registration
   • Status updates
   • Credential management

Data Access Layer

We use Prisma ORM for PostgreSQL access:

// Example service data access
export class UserRepository {
  constructor(private prisma: PrismaClient) {}

  async createUser(data: CreateUserDTO): Promise<User> {
    return this.prisma.user.create({
      data: {
        ...data,
        apiKey: {
          create: {
            key: generateApiKey(),
          },
        },
      },
      include: {
        apiKey: true,
      },
    });
  }
}

Data Migration Strategy

1. Schema Changes

   • Use Prisma migrations
   • Version control for schemas
   • Backward compatibility

2. Data Migration

   • Staged migrations
   • Data validation
   • Rollback procedures

Backup and Recovery

PostgreSQL Backup

• Automated daily backups
• Point-in-time recovery
• Geographic replication

Firestore Backup

• Regular exports
• Backup retention policy
• Recovery procedures

Performance Optimization

1. Indexing Strategy

   • Carefully planned indexes
   • Regular index maintenance
   • Query performance monitoring

2. Caching Layer

   • Redis for frequent queries
   • Cache invalidation strategy
   • Cache hit ratio monitoring