A stock condition survey is only as useful as the data it leaves behind. ClearView AMS emits every accepted survey as eight flat tables with stable string keys, so the same record reconciles across the PDF report and the structured export — and loads cleanly into a warehouse or BI tool with no manual interpretation.
One source, two outputs
The PDF report and this export are generated from the same accepted record. Every issue ID in the report matches its export row — there is no second, divergent copy of the truth.
The eight tables
| Table | Grain / primary key | Representative fields | Joins on |
|---|---|---|---|
| property_survey | One row per survey · surveyId | uprn, propertyReference, surveyorId, schemaVersion, questionSetVersion, status, startedAt, submittedAt, intelligence summary | surveyId |
| access_attempt | One row per access attempt | surveyId, outcome (gained / no_access), reasonCode, attemptedAt, surveyor | surveyId |
| component_observation | One row per component instance · componentRecordId | surveyId, componentType, instanceLabel, conditionRating, remainingLifeYears, renewalYear, unableToAssessReason | surveyId, componentRecordId |
| issue_record | One row per issue · issueId | surveyId, uprn, propertyReference, issueType, hazardCategory (Cat 1/2), location, action, priority, reasonCode, componentRecordId, questionId | surveyId, issueId, componentRecordId |
| photo_evidence | One row per photo · photoId | parentEntityType, parentEntityId, capturedAt, uploadedAt, user, device, caption | photoId, parentEntityId |
| qa_event | One row per QA decision | surveyId, decision (accept / reject), comment, reviewer, decidedAt | surveyId |
| sync_event | One row per sync event | surveyId, type (response / photo / partial / failed / conflict), occurredAt | surveyId |
| lookup_version | One row per controlled vocabulary version | lookupVersionId, schemaVersion, questionSetVersion, lookups | lookupVersionId |
Each table is keyed by a stable string ID and joins on the keys below.
Why stable keys matter
Stable string identifiers mean a component, issue or photo can be traced from capture, through QA, into the report and the export, and onward into a warehouse — without re-keying or fuzzy matching. It is the difference between a data dump and a feed you can build dashboards on.
- issue_record.componentRecordId links each defect to the exact component it was found on.
- issue_record.questionId links a defect back to the question that raised it (e.g. a missing CO alarm).
- photo_evidence.parentEntityId binds every photo to its question, component, issue or access event — never a loose gallery.
- lookup_version pins the controlled vocabularies that were valid at survey time, so historic records stay interpretable.
Power BI / warehouse ready
Exported as an XLSX workbook (a sheet per table) and per-table CSV. Referential joins are preserved, so it lands in a data warehouse without manual interpretation.
Sources & further reading
- 1. Stock condition data and asset management good practice — Chartered Institute of Housing
- 2. Regulatory reporting and data quality expectations — Regulator of Social Housing (GOV.UK)