Data model · Ascenda Wiki

how to read it

Generated, not written

Source ascenda-backend/services/core-api/prisma/schema.prisma · generated 2026-08-20

Each model lists its scalar columns first — name, Prisma type, and the snake_case column it maps to — then its relations. A PK badge marks the primary key, UQ a unique column. Index counts are shown but not enumerated; the schema is the place to read those.

Doc comments come from the schema too. Where a model has none, it has none there either — that is a gap in the schema, not in this page.

Identity

Clerk owns the credential; these rows own everything the product knows about the person behind it.

User users · 15 cols · 2 idx

Columns

PK id String = dbgenerated("gen_random_uuid()") id
req authProviderId String auth_provider_id
UQ authSubject String auth_subject
UQ username String? username
UQ email String? email
null firstName String? first_name
null lastName String? last_name
null dateOfBirth DateTime? date_of_birth
null phoneNumber String? phone_number
null onboardingAt DateTime? onboarding_at
null lastSeenAt DateTime? last_seen_at
req isActive Boolean = true is_active
null deletedAt DateTime? deleted_at
req createdAt DateTime = now() created_at
req updatedAt DateTime updated_at

Relations

one authProvider AuthProvider
many roles UserRoleAssignment
many convertedOnboardings Onboarding
many transactions Transaction
many merchantCategories UserMerchantCategory
many merchantEssentiality EssentialityMerchantScore
many statementImports StatementImport
opt financialProfile FinancialProfile
many budgetCycles BudgetCycle
many goals Goal

UserRoleAssignment user_roles · 4 cols · 2 idx

Columns

PK id String = dbgenerated("gen_random_uuid()") id
req userId String user_id
req roleId String role_id
req createdAt DateTime = now() created_at

Relations

one role AppRole
one user User

AuthProvider auth_providers · 2 cols · 0 idx

Columns

PK id String = dbgenerated("gen_random_uuid()") id
UQ value String value

Relations

many users User

AppRole app_roles · 2 cols · 0 idx

Columns

PK id String = dbgenerated("gen_random_uuid()") id
UQ value String value

Relations

many userRoleAssignments UserRoleAssignment

FinancialProfile financial_profiles · 9 cols · 0 idx

Real-runtime source of net income and the user's overall savings goal. Snapshotted onto each budget cycle so a run stays reproducible.

Columns

PK id String = dbgenerated("gen_random_uuid()") id
UQ userId String user_id
null netMonthlyIncomeInCents BigInt? net_monthly_income_in_cents
null globalSavingsGoalInCents BigInt? global_savings_goal_in_cents
null estimatedMonthlyExpenseInCents BigInt? estimated_monthly_expense_in_cents
null currentSavingsInCents BigInt? current_savings_in_cents
req createdAt DateTime = now() created_at
req updatedAt DateTime updated_at
null deletedAt DateTime? deleted_at

Relations

one user User

Onboarding

A staged, resumable session that exists before an account does, and an audit row for every step it takes.

Onboarding onboardings · 27 cols · 5 idx

Columns

PK id String = dbgenerated("gen_random_uuid()") id
req statusId String status_id
req onboardingSecretHash String onboarding_secret_hash
UQ idempotencyKey String? idempotency_key
null currentStepId String? onboarding_steps_id
req startedAt DateTime = now() started_at
null completedAt DateTime? completed_at
null convertedAt DateTime? converted_at
null lastInteractedAt DateTime? last_interacted_at
null convertedUserId String? converted_user_id
null firstName String? first_name
null lastName String? last_name
null dateOfBirth DateTime? date_of_birth
null age Int? age
null intentTypeId String? intent_type_id
null goalTypeId String? goal_type_id
null goalTitle String? goal_title
null goalTargetAmountInCents BigInt? goal_target_amount_in_cents
null goalHorizonMonths Int? goal_horizon_months
null monthlyIncomeInCents BigInt? monthly_income_in_cents
null monthlySpendInCents BigInt? monthly_spend_in_cents
null savingsBalanceInCents BigInt? savings_balance_in_cents
null source String? source
null sessionId String? session_id
null deviceId String? device_id
req createdAt DateTime = now() created_at
req updatedAt DateTime updated_at

Relations

one status OnboardingStatus
opt intentType IntentType
opt goalType GoalType
opt convertedUser User
opt currentStep OnboardingStep
many events OnboardingEvent

OnboardingEvent onboarding_events · 6 cols · 2 idx

Columns

PK id String = dbgenerated("gen_random_uuid()") id
req onboardingId String onboarding_id
req eventTypeId String event_type_id
null stepId String? onboarding_steps_id
null metadata Json? metadata
req occurredAt DateTime = now() occurred_at

Relations

one eventType OnboardingEventType
one onboarding Onboarding
opt step OnboardingStep

OnboardingStep onboarding_steps · 2 cols · 0 idx

Columns

PK id String = dbgenerated("gen_random_uuid()") id
UQ description String description

Relations

many currentOnboardings Onboarding
many events OnboardingEvent

OnboardingStatus onboarding_statuses · 2 cols · 0 idx

Columns

PK id String = dbgenerated("gen_random_uuid()") id
UQ value String value

Relations

many onboardings Onboarding

OnboardingEventType onboarding_event_types · 2 cols · 0 idx

Columns

PK id String = dbgenerated("gen_random_uuid()") id
UQ value String value

Relations

many events OnboardingEvent

Taxonomy

Seeded lookup tables. Codes are English everywhere; only the display strings are Portuguese.

TransactionCategory transaction_categories · 8 cols · 1 idx

Columns

PK id String = dbgenerated("gen_random_uuid()") id
UQ value String value
req name String name
null icon String? icon
null color String? color
req displayOrder Int display_order
null kindId String? kind_id
null deletedAt DateTime? deleted_at

Relations

opt kind TransactionKind
many transactions Transaction
many allocations BudgetCategoryAllocation
many merchantPriors MerchantCategory
many userMerchantPriors UserMerchantCategory
many merchantEssentiality EssentialityMerchantScore

TransactionKind transaction_kinds · 3 cols · 0 idx

Columns

PK id String = dbgenerated("gen_random_uuid()") id
UQ value String value
req displayOrder Int display_order

Relations

many transactions Transaction
many categories TransactionCategory

PaymentMethod payment_methods · 4 cols · 0 idx

Columns

PK id String = dbgenerated("gen_random_uuid()") id
UQ value String value
req name String name
req displayOrder Int display_order

Relations

many transactions Transaction

GoalType goal_types · 3 cols · 0 idx

Columns

PK id String = dbgenerated("gen_random_uuid()") id
UQ value String value
req displayOrder Int display_order

Relations

many onboardings Onboarding
many goals Goal

IntentType intent_types · 3 cols · 0 idx

Columns

PK id String = dbgenerated("gen_random_uuid()") id
UQ value String value
req displayOrder Int display_order

Relations

many onboardings Onboarding
many goals Goal

Merchants

The dictionary the resolver walks - identity, then the category prior, then what you personally decided.

Merchant merchants · 8 cols · 1 idx

Global merchant registry — brands and generic concepts alike ("Uber", "Churrasco"). Categories live in MerchantCategory (cross-user prior) and UserMerchantCategory (the facts).

Columns

PK id String = dbgenerated("gen_random_uuid()") id
req displayName String display_name
UQ normalizedName String normalized_name
req source String = "seed" source
null confidence Decimal? confidence
req hitCount Int = 0 hit_count
req createdAt DateTime = now() created_at
req updatedAt DateTime updated_at

Relations

many aliases MerchantAlias
many categoryPriors MerchantCategory
many userCategories UserMerchantCategory
many transactions Transaction
many merchantEssentiality EssentialityMerchantScore

MerchantAlias merchant_aliases · 5 cols · 2 idx

One row per raw/normalized descriptor variant that resolves to a Merchant. Doubles as the AI response cache: a descriptor costs one AI call ever, then hits here. `matchMode: 'prefix'` carries the stem patterns the old regex rules used to cover.

Columns

PK id String = dbgenerated("gen_random_uuid()") id
req merchantId String merchant_id
UQ normalizedAlias String normalized_alias
req matchMode String = "exact" match_mode
req createdAt DateTime = now() created_at

Relations

one merchant Merchant
many transactions Transaction

MerchantCategory merchant_categories · 7 cols · 2 idx

Cross-user prior — a ROLLUP of UserMerchantCategory. Never written by a request path; read only on cold start, when the user has no row for this merchant yet.

Columns

PK id String = dbgenerated("gen_random_uuid()") id
req merchantId String merchant_id
req categoryId String category_id
req occurrences Int = 0 occurrences
req userCount Int = 0 user_count
req source String = "seed" source
req computedAt DateTime = now() computed_at

Relations

one merchant Merchant
one category TransactionCategory

UserMerchantCategory user_merchant_categories · 8 cols · 2 idx

The facts. One row per user per merchant per category — what this user actually decided, plus the evidence the essentiality algorithm reads.

Columns

PK id String = dbgenerated("gen_random_uuid()") id
req userId String user_id
req merchantId String merchant_id
req categoryId String category_id
req occurrences Int = 0 occurrences
req totalSpendInCents BigInt = 0 total_spend_in_cents
req firstSeenAt DateTime = now() first_seen_at
req lastSeenAt DateTime = now() last_seen_at

Relations

one user User
one merchant Merchant
one category TransactionCategory

EssentialityMerchantScore essentiality_merchant_scores · 7 cols · 2 idx

Essentiality of a merchant within a category, for one user, snapshotted per budget cycle. Not written yet — the algorithm lands later; the grain is fixed here so history accumulates. `userId` is denormalized (budgetCycleId already implies it) and deliberately not in the key.

Columns

PK id String = dbgenerated("gen_random_uuid()") id
req userId String user_id
req merchantId String merchant_id
req categoryId String category_id
req budgetCycleId String budget_cycle_id
req score Decimal score
req createdAt DateTime = now() created_at

Relations

one user User
one merchant Merchant
one category TransactionCategory
one budgetCycle BudgetCycle

Spending

Every transaction carries an owner. merchant_id is NOT NULL, which is why resolution must always end somewhere.

Transaction transactions · 28 cols · 5 idx

Columns

PK id String = dbgenerated("gen_random_uuid()") id
req userId String user_id
req categoryId String category_id
req kindId String kind_id
null paymentMethodId String? payment_method_id
req amountInCents BigInt amount_in_cents
req currencyCode String = "BRL" currency_code
null note String? note
req occurredAt DateTime occurred_at
UQ idempotencyKey String? idempotency_key
req merchantName String merchant_name
req merchantId String merchant_id
null merchantAliasId String? merchant_alias_id
req status String = "approved" status
null cardBrand String? card_brand
null cardLast4 String? card_last4
null locationLabel String? location_label
null locationLat Decimal? location_lat
null locationLng Decimal? location_lng
null suggestedCategoryId String? suggested_category_id
req categorySource String = "user" category_source
null categoryConfidence Decimal? category_confidence
null statementImportId String? statement_import_id
null externalId String? external_id
null fingerprint String? fingerprint
req createdAt DateTime = now() created_at
req updatedAt DateTime updated_at
null deletedAt DateTime? deleted_at

Relations

one user User
one category TransactionCategory
one kind TransactionKind
opt paymentMethod PaymentMethod
one merchant Merchant
opt merchantAlias MerchantAlias
opt statementImport StatementImport

StatementImport statement_imports · 13 cols · 1 idx

Columns

PK id String = dbgenerated("gen_random_uuid()") id
req userId String user_id
req status String = "pending" status
null source String? source
null fileName String? file_name
null fileHash String? file_hash
null rawPayload Json? raw_payload
null totalCount Int? total_count
null importedCount Int? imported_count
null duplicateCount Int? duplicate_count
null errorMessage String? error_message
req createdAt DateTime = now() created_at
req updatedAt DateTime updated_at

Relations

one user User
many transactions Transaction

Budget

One row per run of the budget algorithm, snapshotting its inputs so a past cycle stays reproducible.

BudgetCycle budget_cycles · 18 cols · 1 idx

One row per run of the Budget Algorithm for a user. Self-contained: snapshots the inputs it ran on, so it stays reproducible.

Columns

PK id String = dbgenerated("gen_random_uuid()") id
req userId String user_id
req cycleMonth DateTime cycle_month
req mode BudgetCycleMode mode
req netMonthlyIncomeInCents BigInt net_monthly_income_in_cents
req globalSavingsGoalInCents BigInt global_savings_goal_in_cents
req windowLengthDays Int window_length_days
req totalExpenseVolumeNormalizedInCents BigInt total_expense_volume_normalized_in_cents
req credibleSavingsInCents BigInt credible_savings_in_cents
req savingsGapInCents BigInt savings_gap_in_cents
req achievableCompressionInCents BigInt achievable_compression_in_cents
req plannedCycleSavingsGoalInCents BigInt planned_cycle_savings_goal_in_cents
null compressionCapPct Decimal? compression_cap_pct
req goalReachableAtCap Boolean goal_reachable_at_cap
req inputSnapshot Json input_snapshot
req createdAt DateTime = now() created_at
req updatedAt DateTime updated_at
null deletedAt DateTime? deleted_at

Relations

one user User
many allocations BudgetCategoryAllocation
many merchantEssentiality EssentialityMerchantScore

BudgetCategoryAllocation budget_category_allocations · 15 cols · 2 idx

One row per category per cycle. Essential categories included (locked at baseline), so a cycle's rows reconstruct the full budget.

Columns

PK id String = dbgenerated("gen_random_uuid()") id
req budgetCycleId String budget_cycle_id
req categoryId String category_id
req action CategoryBudgetAction action
req estimatedMonthlySpendInCents BigInt estimated_monthly_spend_in_cents
req categoryEssentiality Decimal category_essentiality
req spendWeight Decimal spend_weight
null opportunityScore Decimal? opportunity_score
null rank Int? rank
req reductionTargetInCents BigInt reduction_target_in_cents
req reductionAppliedInCents BigInt reduction_applied_in_cents
req budgetAmountInCents BigInt budget_amount_in_cents
req hadPartialReduction Boolean had_partial_reduction
null partialReductionReason PartialReductionReason? partial_reduction_reason
req createdAt DateTime = now() created_at

Relations

one budgetCycle BudgetCycle
one category TransactionCategory

Goal goals · 11 cols · 3 idx

Columns

PK id String = dbgenerated("gen_random_uuid()") id
req userId String user_id
null goalTypeId String? goal_type_id
null intentTypeId String? intent_type_id
null title String? title
null targetAmountInCents BigInt? target_amount_in_cents
null horizonMonths Int? horizon_months
req status String = "active" status
req createdAt DateTime = now() created_at
req updatedAt DateTime updated_at
null deletedAt DateTime? deleted_at

Relations

one user User
opt goalType GoalType
opt intentType IntentType

enums

Enums

Three true Postgres enums. Everything else that looks like an enum — categories, kinds, payment methods — is a seeded lookup table instead, because those change without a migration.

BudgetCycleMode2 values

enum COMPRESSION — savingsGap > 0 — compression required
enum INSIGHT — savingsGap <= 0 — on track; composition insights only

CategoryBudgetAction3 values

enum PROTECTED — essential — locked at baseline expense
enum COMPRESSED — discretionary — eligible for compression
enum PRESERVED — discretionary — skipped because the savings goal closed earlier in rank order

PartialReductionReason2 values

enum COMPRESSION_CAP — proportional target exceeded the category compression cap
enum GOAL_CLOSURE — closing category trimmed so cumulative cuts land on the savings gap