[Backend] Create Apartments Table

[sotoJ24]

Set up the apartments table in the database to store property details with the updated schema to enable advanced querying features and robust data handling.

---

Problem

We need to:

• Store detailed apartment-related data using the specified schema.
• Enable location-based search and filtering features.
• Support advanced queries for tenants and landlords.

---

Solution

1. Create Migration Files

Create a new migration:

hasura migrate create create_apartments_table  

up.sql

-- Create apartments table  
CREATE TABLE apartments (  
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),  
    owner_id TEXT REFERENCES users(id) ON DELETE CASCADE,  
    name TEXT NOT NULL,  
    description TEXT,  
    price DECIMAL(10, 2) NOT NULL CHECK (price > 0),  
    warranty_deposit DECIMAL(10, 2) NOT NULL CHECK (warranty_deposit >= 0),  
    coordinates GEOGRAPHY(Point, 4326) NOT NULL,  
    location_area GEOMETRY(Polygon, 4326),  
    address JSONB NOT NULL,  
    is_available BOOLEAN DEFAULT TRUE,  
    available_from TIMESTAMP WITH TIME ZONE,  
    available_until TIMESTAMP WITH TIME ZONE,  
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),  
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),  
    deleted_at TIMESTAMP WITH TIME ZONE  
);  

-- Create indexes for efficient queries  
CREATE INDEX idx_apartments_owner ON apartments(owner_id);  
CREATE INDEX idx_apartments_price ON apartments(price);  
CREATE INDEX idx_apartments_coordinates ON apartments USING GIST (coordinates);  
CREATE INDEX idx_apartments_location_area ON apartments USING GIST (location_area);  
CREATE INDEX idx_apartments_availability ON apartments(is_available);  
CREATE INDEX idx_apartments_dates ON apartments(available_from, available_until);  

-- Trigger to auto-update updated_at column  
CREATE OR REPLACE FUNCTION update_apartments_updated_at()  
RETURNS TRIGGER AS $$  
BEGIN  
    NEW.updated_at = NOW();  
    RETURN NEW;  
END;  
$$ LANGUAGE plpgsql;  

CREATE TRIGGER update_apartments_updated_at_trigger  
BEFORE UPDATE ON apartments  
FOR EACH ROW EXECUTE FUNCTION update_apartments_updated_at();  

down.sql

-- Drop trigger  
DROP TRIGGER IF EXISTS update_apartments_updated_at_trigger ON apartments;  

-- Drop function  
DROP FUNCTION IF EXISTS update_apartments_updated_at();  

-- Drop indexes  
DROP INDEX IF EXISTS idx_apartments_owner;  
DROP INDEX IF EXISTS idx_apartments_price;  
DROP INDEX IF EXISTS idx_apartments_coordinates;  
DROP INDEX IF EXISTS idx_apartments_location_area;  
DROP INDEX IF EXISTS idx_apartments_availability;  
DROP INDEX IF EXISTS idx_apartments_dates;  

-- Drop table  
DROP TABLE IF EXISTS apartments;  

---

2. Permissions Configuration

Tenant Permissions

apartments:  
  select:  
    filter: { is_available: { _eq: true } }  
  update:  
    filter: false  
  delete:  
    filter: false  

Landlord Permissions

apartments:  
  select:  
    filter: { owner_id: { _eq": "X-Hasura-User-Id" } }  
  insert:  
    check: { owner_id: { _eq": "X-Hasura-User-Id" } }  
  update:  
    filter: { owner_id: { _eq": "X-Hasura-User-Id" } }  
  delete:  
    filter: { owner_id: { _eq": "X-Hasura-User-Id" } }  

---

Prerequisites

• [ ] Users table created with owner relationships.
• [ ] Database migrations set up in Hasura project.
• [ ] PostGIS extension installed in the database.

---

Success Criteria

• [ ] apartments table created successfully.
• [ ] Indexes added for owner, price, location, and availability.
• [ ] Permissions configured for tenants and landlords.
• [ ] Proximity and area-based queries supported via PostGIS.
• [ ] Testing completed for CRUD operations.

---

Verification Steps

1. Apply Migration:

   hasura migrate apply  

2. Test Proximity Query:

   -- Example: Find apartments within 5 km of a point  
   SELECT * FROM apartments  
   WHERE ST_DWithin(coordinates, ST_SetSRID(ST_MakePoint(-80.191788, 25.761681), 4326), 5000);  

3. Test Role-Based Permissions:

   • As a tenant, test queries for available apartments.
   • As a landlord, test CRUD operations for owned apartments.

---

Additional Context

This setup will:

• Enable robust apartment data management.
• Provide support for location-based search and area constraints.
• Ensure proper indexing and role-based access control for scalability.

---

[derianrddev]

Hi, my name is Derian Rodriguez. I'm a passionate software engineer with more than a year of experience, actively contributing to open source projects through OnlyDust. My experience includes full-stack development with focus on backend, frontend, and web3 development using Starknet and Cairo. In addition, I am part of the Dojo Coding community. I would follow all guidelines and instructions to achieve exactly what you want. I would love to contribute and contribute to the development of this project.

[CtpN3m01]

Hi! My name is Saymon Porras Briones, a Software Developer and a member of the Dojo Coding community. I am eager to contribute to this issue and implement the required apartments table schema with advanced querying features.

I can create the migration files to set up the table, configure spatial indexes for efficient location-based searches, and ensure robust data handling through triggers and constraints. Additionally, I will carefully implement the role-based permissions for tenants and landlords as described, ensuring proper access control and compliance with the requirements.

I will follow the provided guidelines, test proximity queries and CRUD operations thoroughly, and ensure the migration is seamless and reversible. I am excited to contribute to this project and deliver a reliable and scalable solution.

[pablomadrigal]

I'm Pablo Madrigal.

I'm a Fullstack Developer and also part of Dojo Coding 🔥

Problem

Create Apartments Table

Solution

I would create the apartments table with owner relationships I'm also going to create the necessary Indexes added for owner, price, location, and availability, configure all the permissions for tenants and landlords, and the custom proximity and area-based filters.

Finally, all the CRUD operations are going to be tested before delivering

Estimated time

I'm going to have everything ready in less than 48 hours from the bug assignation

[0xLemonn]

i am a python dev, a newcomer here, and willing to contribute.

[TheSQLguy]

gm gm, i am new to opensource and would like to start my journey in od through here. I will design a PostgreSQL apartments table with PostGIS for advanced geospatial queries. Key fields like location, price, and availability will be efficiently indexed, and triggers will auto-update timestamps. Permissions will ensure secure access for tenants and landlords, and CRUD operations with geospatial features will be thoroughly tested for smooth apartment management.

[suhas-sensei]

gm @machuwey, Suhas here, having worked in the last od, where I’ve worked on integrating on-chain mechanics into games and have contributed to optimizing user experience with tools like SPV wallets and Web3 integrations.

To solve this issue, I will create a modular migration framework for the apartments table with PostGIS-enabled geospatial fields for location-based queries and optimized spatial indexes. Beyond standard indexing, I’ll implement a hierarchical caching mechanism to accelerate proximity queries for frequently searched areas. Permissions will be fine-tuned with dynamic validation logic to enforce stricter compliance while maintaining flexibility for future role expansions. All changes will undergo rigorous testing with automated scripts to validate CRUD operations and geospatial features in diverse real-world scenarios.

Draft pr : 24hours

[rvalenciano]

Closing this, as it is a duplicate of https://github.com/safetrustcr/Backend/issues/17