Data model reference (Database & subgraph schema)

{
  id: string; // Primary key (UUID)
  name: string; // User display name
  email: string; // Unique email address
  emailVerified: boolean; // Email verification status
  image: string | null; // Profile image URL
  createdAt: Date; // Account creation timestamp
  updatedAt: Date; // Last update timestamp
  role: UserRole | null; // 'admin' | 'user'
  banned: boolean | null; // Ban status
  banReason: string | null; // Reason for ban
  banExpires: Date | null; // Ban expiration date
  wallet: Address | null; // Connected wallet address
  lastLoginAt: Date | null; // Last login timestamp

  // Multi-factor authentication
  pincodeEnabled: boolean; // PIN code MFA enabled
  pincodeVerificationId: string | null;
  twoFactorEnabled: boolean; // TOTP 2FA enabled
  twoFactorVerificationId: string | null;
  secretCodesConfirmed: boolean; // Backup codes confirmed
  secretCodeVerificationId: string | null;
}

{
  id: string; // Primary key
  expiresAt: Date; // Session expiration
  token: string; // Unique session token
  createdAt: Date; // Creation timestamp
  updatedAt: Date; // Last activity timestamp
  ipAddress: string | null; // Client IP address
  userAgent: string | null; // Browser/device info
  userId: string; // FK to user.id
  impersonatedBy: string | null; // Admin impersonation tracking
}

{
  id: string; // Primary key
  accountId: string; // Provider-specific ID
  providerId: string; // Provider name (google, github, etc.)
  userId: string; // FK to user.id
  accessToken: string | null; // OAuth access token
  refreshToken: string | null; // OAuth refresh token
  idToken: string | null; // OAuth ID token
  accessTokenExpiresAt: Date | null;
  refreshTokenExpiresAt: Date | null;
  scope: string | null; // OAuth scopes
  password: string | null; // Hashed password for credentials provider
  createdAt: Date; // Account link timestamp
  updatedAt: Date; // Last token refresh
}

{
  id: string; // Primary key
  identifier: string; // Email or phone number
  value: string; // Verification code/token
  expiresAt: Date; // Expiration timestamp
  createdAt: Date; // Creation timestamp
  updatedAt: Date; // Last update timestamp
}

{
  id: string; // Primary key
  name: string | null; // Human-readable name
  start: string | null; // Visible key prefix
  prefix: string | null; // Key prefix for identification
  key: string; // Hashed API key
  userId: string; // FK to user.id

  // Rate limiting
  refillInterval: number | null; // Token bucket refill interval (ms)
  refillAmount: number | null; // Tokens added per interval
  lastRefillAt: Date | null; // Last bucket refill time
  rateLimitEnabled: boolean; // Rate limiting active
  rateLimitTimeWindow: number; // Window size (ms, default 60000)
  rateLimitMax: number; // Max requests per window (default 60)
  requestCount: number | null; // Current request count
  remaining: number | null; // Remaining tokens
  lastRequest: Date | null; // Last request timestamp

  // Lifecycle
  enabled: boolean; // Key active status
  expiresAt: Date | null; // Expiration date
  createdAt: Date; // Creation timestamp
  updatedAt: Date; // Last update timestamp

  // Metadata
  permissions: string | null; // JSON permissions array
  metadata: string | null; // JSON metadata object
}

{
  id: string; // Primary key
  name: string | null; // User-assigned name
  publicKey: string; // Public key for verification
  userId: string; // FK to user.id
  credentialID: string; // Unique credential identifier
  counter: number; // Signature counter
  deviceType: string; // 'platform' | 'cross-platform'
  backedUp: boolean; // Credential backup status
  transports: string | null; // Supported transports (JSON array)
  createdAt: Date | null; // Registration timestamp
  aaguid: string | null; // Authenticator GUID
}

{
  id: string; // Primary key
  userId: string; // FK to user.id (unique)
  firstName: string; // Legal first name
  lastName: string; // Legal last name
  dob: Date; // Date of birth
  country: string; // ISO country code
  residencyStatus: ResidencyStatus; // 'citizen' | 'permanent_resident' | 'temporary_resident'
  nationalId: string; // National ID number
  createdAt: Date; // Profile creation timestamp
  updatedAt: Date; // Last update timestamp
}

{
  code: string; // Primary key (3-char ISO code)
  name: string; // Full currency name
  decimals: string; // Decimal places (0-8, default 2)
}

{
  baseCode: string; // FK to currencies.code
  quoteCode: string; // FK to currencies.code
  provider: string; // Rate provider ('ECB', 'er-api', 'manual')
  effectiveAt: Date; // When rate became effective
  rate: string; // Decimal(38,18) - high precision
  createdAt: Date; // Record creation time
}

{
  baseCode: string; // FK to currencies.code
  quoteCode: string; // FK to currencies.code
  provider: string; // Rate provider
  rate: string; // Current rate (Decimal 38,18)
  effectiveAt: Date; // When rate became effective
  updatedAt: Date; // Last update timestamp
}

{
  key: string; // Primary key
  value: string; // Setting value (JSON if complex)
  lastUpdated: Date; // Last modification timestamp
}

type Account @entity {
  id: Bytes! # Ethereum address
  isContract: Boolean! # Smart contract vs EOA
  contractName: String # Name if known contract
  balances: [TokenBalance!]! # Token holdings
  stats: AccountStatsState # Aggregated metrics
  systemStats: [AccountSystemStatsState!]!
  tokenFactoryStats: [AccountTokenFactoryStatsState!]!
  identities: [Identity!]! # Linked identities
  registeredIdentities: [RegisteredIdentity!]!
}

type Event @entity(immutable: true) {
  id: Bytes! # txHash + logIndex
  eventName: String! # Event signature name
  txIndex: BigInt! # Log index in transaction
  blockNumber: BigInt! # Block height
  blockTimestamp: BigInt! # Unix timestamp
  transactionHash: Bytes! # Transaction hash
  emitter: Account! # Contract that emitted event
  sender: Account! # Transaction sender
  involved: [Account!]! # All accounts in event
  values: [EventValue!]! # Event parameters
}

type System @entity {
  id: Bytes! # System contract address
  deployedInTransaction: Bytes! # Deployment tx hash
  account: Account # Account representation
  compliance: Compliance # Compliance engine
  identityRegistryStorage: IdentityRegistryStorage
  identityFactory: IdentityFactory
  identityRegistry: IdentityRegistry
  trustedIssuersRegistry: TrustedIssuersRegistry
  topicSchemeRegistry: TopicSchemeRegistry
  tokenFactoryRegistry: TokenFactoryRegistry
  complianceModuleRegistry: ComplianceModuleRegistry
  systemAddonRegistry: SystemAddonRegistry
  systemAccessManager: SystemAccessManager
  organisationIdentity: Identity # Issuer identity
  systemStats: SystemStatsState # Aggregated metrics
  tokenTypeStats: [TokenTypeStatsState!]
  identityStats: IdentityStatsState
}

type Token @entity {
  id: Bytes! # Token contract address
  name: String! # Token name
  symbol: String! # Token symbol
  decimals: Int! # Decimal places
  tokenType: String! # 'bond' | 'equity' | 'fund' | 'stablecoin' | 'deposit'
  totalSupply: BigDecimal! # Current total supply
  cap: BigDecimal # Supply cap (if capped)
  paused: Boolean! # Pause status
  compliance: Compliance! # Compliance contract
  identityRegistry: IdentityRegistry! # Identity registry
  balances: [TokenBalance!]! # All holder balances
  transfers: [Transfer!]! # Transfer history
  mints: [Mint!]! # Mint events
  burns: [Burn!]! # Burn events
  complianceModules: [TokenComplianceModule!]!

  # Bond-specific fields
  maturityDate: BigInt # Unix timestamp
  faceValue: BigDecimal # Per-unit face value
  denominationAsset: Token # Denomination currency
  isMatured: Boolean # Maturity status
  # Statistics
  holderCount: BigInt! # Number of unique holders
  transferCount: BigInt! # Total transfers
  mintCount: BigInt! # Total mints
  burnCount: BigInt! # Total burns
}

type TokenBalance @entity {
  id: Bytes! # token.id + account.id
  token: Token! # Token reference
  account: Account! # Holder reference
  balance: BigDecimal! # Current balance
  frozenBalance: BigDecimal! # Frozen amount
  isFrozen: Boolean! # Full freeze status
  lastUpdated: BigInt! # Last change timestamp
}

type Transfer @entity(immutable: true) {
  id: Bytes! # txHash + logIndex
  token: Token! # Token transferred
  from: Account! # Sender
  to: Account! # Recipient
  value: BigDecimal! # Amount transferred
  blockNumber: BigInt! # Block height
  blockTimestamp: BigInt! # Unix timestamp
  transactionHash: Bytes! # Transaction hash
}

type Compliance @entity {
  id: Bytes! # Compliance contract address
  token: Token! # Associated token
  modules: [ComplianceModule!]! # Active modules
  requiredClaimTopics: [BigInt!]! # Required claim topics
}

type ComplianceModule @entity {
  id: Bytes! # Module contract address
  name: String! # Module type name
  moduleType: String! # Classification
  isGlobal: Boolean! # System-wide vs token-specific
  tokens: [TokenComplianceModule!]! # Tokens using module
}

type TokenComplianceModule @entity {
  id: Bytes! # Composite ID
  token: Token! # Token reference
  module: ComplianceModule! # Module reference
  params: Bytes! # ABI-encoded parameters
  addedAt: BigInt! # Addition timestamp
  removedAt: BigInt # Removal timestamp (null if active)
  isActive: Boolean! # Currently active
}

type Identity @entity {
  id: Bytes! # Identity contract address
  account: Account! # Linked account
  keys: [IdentityKey!]! # Management keys
  claims: [IdentityClaim!]! # Attached claims
  isVerified: Boolean! # Verification status
  country: Int # Country code (if registered)
}

type IdentityClaim @entity {
  id: Bytes! # Claim ID
  identity: Identity! # Identity owning claim
  topic: BigInt! # Claim topic
  scheme: BigInt! # Signature scheme
  issuer: Account! # Claim issuer
  signature: Bytes! # Claim signature
  data: Bytes! # Claim data
  uri: String! # Claim URI
  addedAt: BigInt! # Addition timestamp
  removedAt: BigInt # Removal timestamp
  isActive: Boolean! # Currently valid
}

type RegisteredIdentity @entity {
  id: Bytes! # Registry address + account
  account: Account! # Registered account
  identity: Identity! # Linked identity contract
  country: Int! # Country code
  registeredAt: BigInt! # Registration timestamp
  updatedAt: BigInt! # Last update
  isVerified: Boolean! # Verification status
}

type AccessControl @entity {
  id: Bytes! # Access control address
  system: System! # Owning system
  roleAdmins: [AccessControlRoleAdmin!]!

  # Core roles
  admin: [Account!]!

  # People roles
  systemManager: [Account!]!
  identityManager: [Account!]!
  tokenManager: [Account!]!
  complianceManager: [Account!]!
  addonManager: [Account!]!
  claimPolicyManager: [Account!]!
  claimIssuer: [Account!]!
  auditor: [Account!]!
  organisationIdentityManager: [Account!]!

  # System roles
  systemModule: [Account!]!
  identityRegistryModule: [Account!]!
  tokenFactoryRegistryModule: [Account!]!
  tokenFactoryModule: [Account!]!
  addonRegistryModule: [Account!]!
  addonModule: [Account!]!

  # Asset roles
  governance: [Account!]!
  supplyManagement: [Account!]!
  custodian: [Account!]!
  emergency: [Account!]!

  # Addon roles
  fundsManager: [Account!]!
  saleAdmin: [Account!]!
}

type SystemStatsState @entity {
  id: Bytes! # System address
  system: System! # System reference
  totalTokens: BigInt! # Total tokens deployed
  totalHolders: BigInt! # Unique token holders
  totalTransfers: BigInt! # All-time transfers
  totalVolume: BigDecimal! # All-time transfer volume
  totalIdentities: BigInt! # Registered identities
  lastUpdated: BigInt! # Last metric update
}

type TokenTypeStatsState @entity {
  id: Bytes! # system.id + tokenType
  system: System! # System reference
  tokenType: String! # 'bond' | 'equity' | etc.
  count: BigInt! # Number of this type
  totalSupply: BigDecimal! # Combined supply
  transferCount: BigInt! # Total transfers
  volume: BigDecimal! # Transfer volume
}

import { db } from "@/lib/db";
import { user, kycProfiles } from "@/lib/db/schemas";
import { eq } from "drizzle-orm";

// Get user with KYC profile
const userWithKyc = await db
  .select()
  .from(user)
  .leftJoin(kycProfiles, eq(kycProfiles.userId, user.id))
  .where(eq(user.id, userId))
  .get();

// Get latest exchange rate
import { fxRatesLatest } from "@/lib/db/schemas/exchange-rates";

const rate = await db
  .select()
  .from(fxRatesLatest)
  .where(
    and(
      eq(fxRatesLatest.baseCode, "USD"),
      eq(fxRatesLatest.quoteCode, "EUR"),
      eq(fxRatesLatest.provider, "ECB")
    )
  )
  .get();

# Get token with holders
query GetToken($id: Bytes!) {
  token(id: $id) {
    id
    name
    symbol
    decimals
    totalSupply
    holderCount
    balances(first: 10, orderBy: balance, orderDirection: desc) {
      account {
        id
      }
      balance
    }
  }
}

# Get account's portfolio
query GetPortfolio($account: Bytes!) {
  account(id: $account) {
    balances(where: { balance_gt: "0" }) {
      token {
        id
        name
        symbol
        decimals
      }
      balance
      frozenBalance
    }
  }
}

# Get recent transfers
query GetRecentTransfers($token: Bytes!) {
  transfers(
    where: { token: $token }
    first: 20
    orderBy: blockTimestamp
    orderDirection: desc
  ) {
    id
    from {
      id
    }
    to {
      id
    }
    value
    blockTimestamp
  }
}