12. Data Models — Property Domain Schemas¶

CREATE TABLE land_parcels (
    parcel_id           UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    parcel_number       VARCHAR(30) UNIQUE NOT NULL, -- Official cadastral number
    parent_parcel_id    UUID REFERENCES land_parcels(parcel_id), -- Before subdivision
    geometry            GEOMETRY(MultiPolygon, 4326) NOT NULL,
    area_sqm            DECIMAL(14,2) NOT NULL,
    land_use            VARCHAR(50), -- residential, commercial, agricultural, industrial, mixed
    zoning_id           UUID REFERENCES zoning_districts(zone_id),
    governorate         VARCHAR(50) NOT NULL,
    district            VARCHAR(100),
    municipality        VARCHAR(100),
    neighborhood        VARCHAR(100),
    block_number        VARCHAR(20),
    survey_number       VARCHAR(30),
    elevation_m         DECIMAL(8,2),
    terrain_type        VARCHAR(30), -- flat, hilly, mountainous
    road_frontage_m     DECIMAL(8,2),
    is_encumbered       BOOLEAN DEFAULT FALSE,
    status              VARCHAR(20) DEFAULT 'active', -- active, merged, subdivided, disputed
    registered_at       TIMESTAMP,
    created_at          TIMESTAMP DEFAULT NOW(),
    updated_at          TIMESTAMP DEFAULT NOW()
);

CREATE INDEX idx_parcels_geom ON land_parcels USING GIST(geometry);
CREATE INDEX idx_parcels_governorate ON land_parcels(governorate, district);
CREATE INDEX idx_parcels_number ON land_parcels(parcel_number);

CREATE TABLE properties (
    property_id         UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    parcel_id           UUID NOT NULL REFERENCES land_parcels(parcel_id),
    property_type       VARCHAR(30) NOT NULL, -- house, apartment, villa, commercial_building, warehouse, farm
    property_number     VARCHAR(30) UNIQUE,
    building_footprint  GEOMETRY(Polygon, 4326),
    floor_count         INTEGER,
    unit_number         VARCHAR(20), -- For apartment units
    total_area_sqm      DECIMAL(10,2),
    built_area_sqm      DECIMAL(10,2),
    year_built          INTEGER,
    construction_type   VARCHAR(30), -- concrete, steel, brick, mixed
    condition           VARCHAR(20), -- excellent, good, fair, poor, ruin
    has_garage          BOOLEAN DEFAULT FALSE,
    has_garden          BOOLEAN DEFAULT FALSE,
    utility_water       BOOLEAN DEFAULT TRUE,
    utility_electricity BOOLEAN DEFAULT TRUE,
    utility_sewage      BOOLEAN DEFAULT FALSE,
    description_ckb     TEXT,
    description_en      TEXT,
    photos              JSONB, -- [{url, hash, type, uploaded_at}]
    status              VARCHAR(20) DEFAULT 'active', -- active, demolished, under_construction
    created_at          TIMESTAMP DEFAULT NOW(),
    updated_at          TIMESTAMP DEFAULT NOW()
);

CREATE INDEX idx_properties_parcel ON properties(parcel_id);
CREATE INDEX idx_properties_type ON properties(property_type, status);

CREATE TABLE ownership_records (
    ownership_id        UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    property_id         UUID NOT NULL REFERENCES properties(property_id),
    parcel_id           UUID REFERENCES land_parcels(parcel_id),
    owner_citizen_id    UUID NOT NULL, -- FK to KRDPASS citizen
    ownership_type      VARCHAR(30), -- freehold, leasehold, shared, government
    share_percentage    DECIMAL(5,2) DEFAULT 100.00,
    acquired_via        VARCHAR(30), -- purchase, inheritance, gift, court_order, government_grant
    acquisition_date    DATE NOT NULL,
    deed_number         VARCHAR(30),
    deed_document_id    UUID, -- Reference to document vault
    is_primary_owner    BOOLEAN DEFAULT TRUE,
    status              VARCHAR(20) DEFAULT 'current', -- current, transferred, deceased, disputed
    notes               TEXT,
    created_at          TIMESTAMP DEFAULT NOW(),
    updated_at          TIMESTAMP DEFAULT NOW()
);

CREATE INDEX idx_ownership_property ON ownership_records(property_id, status);
CREATE INDEX idx_ownership_citizen ON ownership_records(owner_citizen_id, status);

CREATE TABLE ownership_history (
    history_id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    property_id         UUID NOT NULL REFERENCES properties(property_id),
    from_citizen_id     UUID, -- NULL for first registration
    to_citizen_id       UUID NOT NULL,
    transfer_type       VARCHAR(30), -- sale, inheritance, gift, court_order, first_registration
    transfer_date       DATE NOT NULL,
    sale_price          DECIMAL(15,2),
    currency            VARCHAR(3) DEFAULT 'IQD',
    deed_number         VARCHAR(30),
    application_id      UUID REFERENCES service_applications(application_id),
    notary_name         VARCHAR(200),
    recorded_by         UUID, -- Staff user
    recorded_at         TIMESTAMP DEFAULT NOW()
);

CREATE INDEX idx_history_property ON ownership_history(property_id, transfer_date);

CREATE TABLE property_transactions (
    transaction_id      UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    application_id      UUID REFERENCES service_applications(application_id),
    property_id         UUID NOT NULL REFERENCES properties(property_id),
    transaction_type    VARCHAR(30) NOT NULL, -- sale, gift, inheritance, mortgage, release
    seller_citizen_id   UUID,
    buyer_citizen_id    UUID,
    agreed_price        DECIMAL(15,2),
    currency            VARCHAR(3) DEFAULT 'IQD',
    tax_amount          DECIMAL(15,2),
    fee_amount          DECIMAL(15,2),
    notary_id           UUID, -- Notary actor
    agent_id            UUID, -- Real estate agent actor
    status              VARCHAR(20) DEFAULT 'initiated',
    -- initiated → documents_submitted → under_review → approved → registered | rejected
    submitted_at        TIMESTAMP DEFAULT NOW(),
    reviewed_at         TIMESTAMP,
    reviewed_by         UUID,
    completed_at        TIMESTAMP,
    rejection_reason    TEXT
);

CREATE INDEX idx_transactions_property ON property_transactions(property_id, status);
CREATE INDEX idx_transactions_status ON property_transactions(status, submitted_at);

CREATE TABLE building_permits (
    permit_id           UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    application_id      UUID REFERENCES service_applications(application_id),
    parcel_id           UUID NOT NULL REFERENCES land_parcels(parcel_id),
    permit_type         VARCHAR(30), -- new_construction, renovation, demolition, extension, change_of_use
    applicant_id        UUID NOT NULL,
    architect_name      VARCHAR(200),
    architect_license   VARCHAR(50),
    planned_floors      INTEGER,
    planned_area_sqm    DECIMAL(10,2),
    construction_type   VARCHAR(30),
    estimated_cost      DECIMAL(15,2),
    site_plan_doc_id    UUID,  -- Document vault reference
    architectural_doc_id UUID,
    structural_doc_id   UUID,
    status              VARCHAR(20) DEFAULT 'submitted',
    -- submitted → technical_review → site_inspection → approved → construction → completion_cert | rejected
    issued_at           TIMESTAMP,
    expires_at          TIMESTAMP,
    inspection_dates    JSONB, -- [{date, inspector, result, notes}]
    created_at          TIMESTAMP DEFAULT NOW(),
    updated_at          TIMESTAMP DEFAULT NOW()
);

CREATE INDEX idx_permits_parcel ON building_permits(parcel_id);
CREATE INDEX idx_permits_status ON building_permits(status, created_at);

CREATE TABLE property_valuations (
    valuation_id        UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    property_id         UUID NOT NULL REFERENCES properties(property_id),
    parcel_id           UUID REFERENCES land_parcels(parcel_id),
    valuation_type      VARCHAR(30), -- market, tax_assessment, insurance, court_ordered
    assessed_value      DECIMAL(15,2) NOT NULL,
    currency            VARCHAR(3) DEFAULT 'IQD',
    value_per_sqm       DECIMAL(10,2),
    valuation_method    VARCHAR(30), -- comparable_sales, cost_approach, income_approach, ml_model
    comparable_ids      UUID[], -- Array of property_ids used as comparables
    assessor_id         UUID,
    assessor_notes      TEXT,
    ml_confidence       DECIMAL(5,4), -- If ML-based
    valid_from          DATE NOT NULL,
    valid_until         DATE,
    created_at          TIMESTAMP DEFAULT NOW()
);

CREATE INDEX idx_valuations_property ON property_valuations(property_id, valid_from);

CREATE TABLE encumbrances (
    encumbrance_id      UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    property_id         UUID NOT NULL REFERENCES properties(property_id),
    encumbrance_type    VARCHAR(30), -- mortgage, lien, easement, court_freeze, government_restriction
    holder_name         VARCHAR(200), -- Bank name, court, government entity
    holder_reference    VARCHAR(100), -- Loan number, court case, etc.
    amount              DECIMAL(15,2), -- For monetary encumbrances
    currency            VARCHAR(3) DEFAULT 'IQD',
    description         TEXT,
    registered_at       DATE NOT NULL,
    expires_at          DATE,
    released_at         DATE,
    status              VARCHAR(20) DEFAULT 'active', -- active, released, expired
    created_at          TIMESTAMP DEFAULT NOW()
);

CREATE INDEX idx_encumbrances_property ON encumbrances(property_id, status);

CREATE TABLE zoning_districts (
    zone_id             UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    zone_code           VARCHAR(20) UNIQUE NOT NULL, -- R1, C2, I1, AG, etc.
    zone_name_ckb       VARCHAR(200),
    zone_name_en        VARCHAR(200),
    zone_type           VARCHAR(30), -- residential, commercial, industrial, agricultural, mixed, protected
    geometry            GEOMETRY(MultiPolygon, 4326) NOT NULL,
    max_building_height_m DECIMAL(6,2),
    max_floor_count     INTEGER,
    max_coverage_pct    DECIMAL(5,2), -- Max % of lot that can be built on
    min_setback_m       DECIMAL(5,2),
    allowed_uses        TEXT[], -- Array of permitted land uses
    restricted_uses     TEXT[],
    governing_body      VARCHAR(100),
    effective_date      DATE NOT NULL,
    expires_at          DATE,
    status              VARCHAR(20) DEFAULT 'active',
    created_at          TIMESTAMP DEFAULT NOW()
);

CREATE INDEX idx_zoning_geom ON zoning_districts USING GIST(geometry);

CREATE TABLE rental_contracts (
    contract_id         UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    property_id         UUID NOT NULL REFERENCES properties(property_id),
    landlord_id         UUID NOT NULL, -- Citizen ID
    tenant_id           UUID NOT NULL, -- Citizen ID
    contract_type       VARCHAR(20), -- residential, commercial, agricultural
    monthly_rent        DECIMAL(12,2) NOT NULL,
    currency            VARCHAR(3) DEFAULT 'IQD',
    deposit_amount      DECIMAL(12,2),
    start_date          DATE NOT NULL,
    end_date            DATE NOT NULL,
    payment_day         INTEGER CHECK (payment_day BETWEEN 1 AND 28),
    auto_renew          BOOLEAN DEFAULT FALSE,
    contract_doc_id     UUID, -- Document vault
    status              VARCHAR(20) DEFAULT 'active', -- active, expired, terminated, disputed
    terminated_at       DATE,
    termination_reason  TEXT,
    created_at          TIMESTAMP DEFAULT NOW(),
    updated_at          TIMESTAMP DEFAULT NOW()
);

CREATE INDEX idx_rental_property ON rental_contracts(property_id, status);
CREATE INDEX idx_rental_landlord ON rental_contracts(landlord_id, status);
CREATE INDEX idx_rental_tenant ON rental_contracts(tenant_id, status);

CREATE TABLE addresses (
    address_id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    property_id         UUID REFERENCES properties(property_id),
    parcel_id           UUID REFERENCES land_parcels(parcel_id),
    address_ckb         TEXT,
    address_en          TEXT,
    street_name         VARCHAR(200),
    building_number     VARCHAR(20),
    postal_code         VARCHAR(10),
    governorate         VARCHAR(50) NOT NULL,
    district            VARCHAR(100),
    municipality        VARCHAR(100),
    neighborhood        VARCHAR(100),
    point               GEOMETRY(Point, 4326),
    is_verified         BOOLEAN DEFAULT FALSE,
    created_at          TIMESTAMP DEFAULT NOW()
);

CREATE INDEX idx_addresses_point ON addresses USING GIST(point);
CREATE INDEX idx_addresses_property ON addresses(property_id);

CREATE TABLE service_feedback (
    feedback_id         UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    application_id      UUID REFERENCES service_applications(application_id),
    citizen_id          UUID NOT NULL,
    service_id          UUID REFERENCES services(service_id),
    office_id           UUID REFERENCES offices(office_id),
    rating              INTEGER CHECK (rating BETWEEN 1 AND 5),
    comment             TEXT,
    sentiment           VARCHAR(20), -- positive, neutral, negative (AI-derived)
    channel             VARCHAR(20), -- app, web, kiosk, sms
    created_at          TIMESTAMP DEFAULT NOW()
);

CREATE INDEX idx_feedback_service ON service_feedback(service_id, created_at);
CREATE INDEX idx_feedback_office ON service_feedback(office_id, created_at);

CREATE TABLE cms_content (
    content_id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    content_type        VARCHAR(30), -- announcement, faq, guide, news
    title_ckb           VARCHAR(300),
    title_kmr           VARCHAR(300),
    title_en            VARCHAR(300),
    body_ckb            TEXT,
    body_kmr            TEXT,
    body_en             TEXT,
    category            VARCHAR(100), -- property, permits, zoning, municipal
    tags                TEXT[],
    featured_image_url  TEXT,
    published           BOOLEAN DEFAULT FALSE,
    published_at        TIMESTAMP,
    author_id           UUID,
    created_at          TIMESTAMP DEFAULT NOW(),
    updated_at          TIMESTAMP DEFAULT NOW()
);