Certifications Export

Overview

The exported_certifications topic provides a row per affordable certification (AffordableQualification) across all properties in an organization. It covers move-in, recertification, interim, correction, and termination events and includes the data needed for compliance reporting, recertification workflows, and downstream analytics.

Each row is scoped to one certification record. Related household, unit, tenant, and program details are resolved and flattened onto each row for easy consumption.

SFTP Path

{organizationId}/public/exported_certifications/

Full-load and CDC files follow the standard export conventions described in the exports overview and CDC guide.

Fields

Identity

Field	Type	Description
`id`	UUID	Primary key. Stable identifier for the certification record.
`createdAt`	timestamp	For `MOVE_IN` certifications, sourced from the associated application’s `createdAt`. All other types use the certification’s own `createdAt`.
`updatedAt`	timestamp	Last time the certification record was modified.
`deletedAt`	timestamp	Set when the certification is soft-deleted. Rows with a non-null `deletedAt` are excluded from the export.

Organization & Property

Field	Type	Description
`organizationId`	UUID	Organization that owns the property.
`propertyId`	UUID	Property where the household resides.
`householdId`	UUID	Household the certification belongs to.

Unit

Field	Type	Description
`unitId`	UUID	ID of the unit associated with the certification’s tenant event. Null when no current tenant event unit record exists.
`unitNumber`	varchar	Human-readable unit number. Resolved from the latest non-deleted `TenantEventUnit` record; falls back to the `Unit` table number when the event-level number is absent.

Tenant

Field	Type	Description
`tenantName`	varchar	Full name of the head of household (`firstName lastName`). Derived from the most recently updated non-deleted `TenantEventPerson` record where `isHeadOfHousehold = true`. May be blank if no head-of-household record exists.

Certification Type & Status

Field	Type	Description
`type`	enum	Raw certification type from the source record. See Certification Types for possible values.
`certificationType`	varchar	Normalized short code. `MOVE_IN` → `MI`; values starting with `ANNUAL` → `AR`; values starting with `INTERIM` → `IR`; all others pass through as-is.
`reviewStatus`	varchar	Compliance approval status normalized to upper snake case. See Review Status Values.
`currentStatus`	varchar	Identical normalization of compliance approval status. Provided as a convenience alias aligned to the Fulson field contract.

Dates

Field	Type	Description
`effectiveDate`	date	Voucher effective date (`voucherEffectiveDate`).
`dueDate`	date	Recertification due date (`recertificationDate`).
`anticipatedDate`	date	Anticipated voucher date (`anticipatedVoucherDate`).
`nextRecertificationDate`	date	Alias of `dueDate` (`recertificationDate`).
`completionDate`	timestamp	Date the certification was marked complete (`certificationCompletionDate`).
`ownerSignatureDate`	date	Date the owner signed the certification.
`customerSignatureDate`	date	Date the head of household signed the certification (`headOfHouseholdSignatureDate`).
`customerSignatureSuccess`	boolean	`true` when both `ownerSignatureDate` and `customerSignatureDate` are non-null.

Program & Subsidy

Field	Type	Description
`program`	varchar	Normalized affordable program name derived from the set-aside program. See Program Mapping.
`contractNumber`	varchar	HUD contract number from the floor plan’s affordable program association.
`subsidyType`	varchar	Name of the primary rental assistance source.
`secondarySubsidyType`	varchar	Name of the active secondary HUD subsidy type at the property level, if any.

Financial

Field	Type	Description
`tenantPaymentAmount`	decimal	Resident’s portion of rent (`residentPayment`).
`assistancePaymentAmount`	decimal	HAP/assistance payment (`hapPayment`).
`utilityAllowanceAmount`	decimal	Utility allowance applied to the certification.
`marketRentAmount`	real	Market rent for the unit. Resolved from the tenant event unit’s `baseMarketRent`, falling back to the certification calculations snapshot (`marketRent`, then floor plan `baseMarketRentAmount`).
`grossRentAmount`	decimal	HUD gross rent from the tenant event unit.
`contractRentAmount`	real	`grossRentAmount - utilityAllowanceAmount`. Null when either component is null.
`householdIncomeAmount`	real	Total household annual income. Resolved from the certification calculations snapshot (`incomeDetails.totalAnnualIncome`, then 50058 `line8a`, then `annualIncome`), falling back to the qualification’s `hhAnnualIncome`.
`totalIncomeAmount`	decimal	Total household annual income (`hhAnnualIncome`).
`adjustedIncomeAmount`	real	Adjusted annual income from the certification calculations snapshot.
`totalDeductionsAmount`	real	Total household deductions from the certification calculations snapshot.

Household

Field	Type	Description
`eligibleHouseholdSize`	integer	Household size at certification time (`hhSize`).

Application

Field	Type	Description
`applicationId`	UUID	Populated only for `MOVE_IN` certifications. References the originating application.

Certification Types

Possible values for the type field (raw enum):

Value	Description
`MOVE_IN`	Initial move-in certification
`INITIAL`	Initial certification (non-move-in)
`RECERT`	Annual recertification
`INTERIM`	Interim certification
`CORRECTION`	Correction to a prior certification
`TERMINATION`	Termination event
`MOVE_OUT`	Move-out certification
`ASSIGN_RA`	Assign rental assistance (RD)
`CO_TENANT_TO_TENANT`	Co-tenant to tenant conversion (RD)
`EVICTION`	Eviction (RD)
`INITIATE_ABSENCE`	Initiate absence (RD)
`MODIFY`	Modification (RD)
`REMOVE_RA`	Remove rental assistance (RD)
`TERMINATE_ABSENCE`	Terminate absence (RD)

The certificationType field normalizes this to MI, AR, or IR where applicable.

Review Status Values

Value	Description
`APPROVED`	Certification approved
`DENIED`	Certification denied
`APPROVED_PENDING_SIGNATURES`	Approved but awaiting signatures
`HOUSEHOLD_ON_NOTICE_HOLD`	Household on notice or hold
`PENDING`	Pending review
`CORRECTION_NEEDED`	Corrections required
`CORRECTION_NEEDED_FINAL_PACKET`	Corrections needed on final packet
`PENDING_FINAL_APPROVAL`	Awaiting final approval

Program Mapping

The program field is normalized from the set-aside program (SetAsideProgram) using the following rules applied to affordableProgramName and programName:

Match condition	Output
Contains `HUD`	`HUD`
Contains `LIHTC` or `LITHC`	`Tax Credit`
Contains `RURAL` or `RD`	`Rural Development`
No match	Source program name passed through as-is

Soft-Delete Behavior

Only active (non-deleted) records are included. The export filters out soft-deleted rows at every join:

Certification rows with deletedAt IS NOT NULL are excluded.
Household and Property must be non-deleted (inner joins).
All other joined tables (TenantEvent, TenantEventUnit, Unit, TenantEventPerson, SetAsideProgram, Application, ComplianceApprovalStatus, RentalAssistanceSource, and subsidy lookups) are guarded with deletedAt IS NULL.