Skip to content
Pillow Documentation

Data Schema

This page documents the current property data model used by Pillow’s Mill service.

  • The operational datastore is PostgreSQL with the PostGIS extension for spatial queries.
  • We use a single comprehensive schema with 300+ attributes covering the full property domain.

Properties

Section titled “Properties”

Comprehensive properties table

Section titled “Comprehensive properties table”

The comprehensive schema captures 300+ attributes across identification, address/location, dimensions, rooms/layout, construction/materials, interior, systems (HVAC, electrical, plumbing), smart home, security/safety, parking, outdoor and landscaping, views and waterfront, energy/green features, accessibility, luxury features, community/HOA, environmental, investment, media, listing/agent metadata, and data quality.

Key characteristics

  • Metric-first units: area in square meters (sqm), distances in kilometers, lengths in meters.
  • Flat, analytics-friendly layout designed for wide-table OLAP queries.
  • Backed by a formal model in code and a schema creation script used to provision the full database.

Representative DDL excerpt

CREATE TABLE properties (
    -- Identification
    id VARCHAR(50),
    property_uuid VARCHAR(50),
    mls_number VARCHAR(50),
    property_id_alt VARCHAR(50),


    -- Basic information
    property_name VARCHAR(200),
    property_type VARCHAR(50),
    property_subtype VARCHAR(50),
    property_style VARCHAR(50),
    property_use VARCHAR(50),
    title VARCHAR(500),
    description TEXT,


    -- Address & coordinates
    address_street VARCHAR(200),
    address_unit VARCHAR(50),
    address_city VARCHAR(100),
    address_state VARCHAR(50),
    address_county VARCHAR(100),
    address_country VARCHAR(50),
    address_postal_code VARCHAR(20),
    address_full TEXT,
    latitude DECIMAL(10, 8),
    longitude DECIMAL(11, 8),


    -- Location details
    neighborhood VARCHAR(100),
    subdivision VARCHAR(100),
    school_district VARCHAR(100),
    time_zone VARCHAR(50),
    elevation_meters INT,
    flood_zone VARCHAR(20),


    -- Dimensions (metric)
    square_meters_total INT,
    square_meters_living INT,
    lot_size_sqm INT,
    lot_size_hectares DECIMAL(10,4),
    frontage_meters DECIMAL(8,2),
    depth_meters DECIMAL(8,2),


    -- Rooms & layout
    bedrooms_total INT,
    bathrooms_total DECIMAL(3,1),
    stories DECIMAL(2,1),


    -- Construction & interior (selected)
    year_built INT,
    condition_rating VARCHAR(2),
    quality_rating VARCHAR(2),
    construction_type VARCHAR(50),
    roof_material VARCHAR(100),
    flooring_main VARCHAR(50),


    -- Systems (selected)
    hvac_type VARCHAR(100),
    heating_system VARCHAR(100),
    cooling_system VARCHAR(100),
    has_central_air BOOLEAN,


    -- Energy/green (selected)
    energy_star_certified BOOLEAN,
    has_solar_panels BOOLEAN,


    -- Parking/outdoor (selected)
    parking_total_spaces INT,
    has_pool BOOLEAN
    -- ... many additional columns omitted for brevity ...
);

Source of truth

  • Code model: mill/internal/models/property_comprehensive.go
  • Schema DDL: mill/scripts/create_comprehensive_properties_table.sql

JSON shape used by ingestion and API

  • The Mill API accepts and emits a JSON representation aligned with the comprehensive model (see OpenAPI schema PropertyComprehensive).
  • Refer to the Mill API docs for field-level contracts and examples.
{
  "title": "Basic 2 Bedroom Unit for Sale",
  "property_type": "residential",
  "property_subtype": "apartment",
  "status": "for_sale",
  "financial": { "price_current": 750000, "currency": "AUD" },
  "address": {
    "street": "45 High Street",
    "city": "Sydney",
    "state": "NSW",
    "country": "Australia",
    "postal_code": "2000"
  },
  "location": { "latitude": -33.8688, "longitude": 151.2093 },
  "dimensions": { "square_meters_total": 80 },
  "rooms": { "bedrooms_total": 2, "bathrooms_total": 1.0 }
}

Database Setup

Section titled “Database Setup”
  • Use the comprehensive schema script to create the properties table in fresh environments.
  • For a fresh start, drop the existing database and run mill/scripts/create_comprehensive_properties_table.sql.

Performance considerations

Section titled “Performance considerations”
  • Add appropriate indexes based on expected query patterns.
  • Use PostGIS spatial indexes for geographic queries.
  • Keep ingestion payloads aligned with the comprehensive model; sparse columns are acceptable.

Detailed Attribute Documentation

Section titled “Detailed Attribute Documentation”

For comprehensive documentation of all 300+ attributes, see the Data Schema Index.

The schema is organized into the following categories:

And many more categories covering all aspects of property data.

Next steps

Section titled “Next steps”