Create bid_requests and bid_status_histories tables

[rvalenciano]

[Backend] Create bid_requests and bid_status_histories tables

Create tables to handle apartment bid requests and their status history, tracking the complete lifecycle of a tenant's bid on an apartment.

Problem

We need to:

• Track bid requests from tenants
• Maintain bid request status history
• Support the complete bid lifecycle
• Enable bid status transitions

Solution

Migration Files

Create new migration:

hasura migrate create create_bid_tables

up.sql:

-- Create bid_requests table
CREATE TABLE bid_requests (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    apartment_id UUID REFERENCES apartments(id) ON DELETE CASCADE,
    tenant_id TEXT REFERENCES users(id) ON DELETE CASCADE,
    current_status TEXT NOT NULL DEFAULT 'PENDING',
    proposed_price DECIMAL(10,2) NOT NULL CHECK (proposed_price > 0),
    desired_move_in TIMESTAMP WITH TIME ZONE NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    deleted_at TIMESTAMP WITH TIME ZONE,
    CONSTRAINT valid_status CHECK (
        current_status IN (
            'PENDING',
            'VIEWED',
            'APPROVED',
            'CONFIRMED',
            'ESCROW_FUNDED',
            'ESCROW_COMPLETED',
            'CANCELLED'
        )
    )
);

-- Create bid_status_histories table
CREATE TABLE bid_status_histories (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    bid_request_id UUID REFERENCES bid_requests(id) ON DELETE CASCADE,
    status TEXT NOT NULL,
    notes TEXT,
    changed_by TEXT REFERENCES users(id) ON DELETE SET NULL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Create function to record status changes
CREATE OR REPLACE FUNCTION record_bid_status_change()
RETURNS TRIGGER AS $$
BEGIN
    IF (TG_OP = 'INSERT') OR (OLD.current_status IS DISTINCT FROM NEW.current_status) THEN
        INSERT INTO bid_status_histories (
            bid_request_id,
            status,
            changed_by
        ) VALUES (
            NEW.id,
            NEW.current_status,
            current_setting('hasura.user', true)::text
        );
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Create trigger for status history
CREATE TRIGGER record_bid_status
    AFTER INSERT OR UPDATE ON bid_requests
    FOR EACH ROW
    EXECUTE FUNCTION record_bid_status_change();

-- Create indexes
CREATE INDEX idx_bid_requests_apartment 
    ON bid_requests(apartment_id);
CREATE INDEX idx_bid_requests_tenant 
    ON bid_requests(tenant_id);
CREATE INDEX idx_bid_requests_status 
    ON bid_requests(current_status);
CREATE INDEX idx_bid_requests_dates 
    ON bid_requests(created_at, desired_move_in);
CREATE INDEX idx_bid_histories_request 
    ON bid_status_histories(bid_request_id);
CREATE INDEX idx_bid_histories_dates 
    ON bid_status_histories(created_at);

-- Add trigger for updated_at
CREATE TRIGGER update_bid_requests_updated_at
    BEFORE UPDATE ON bid_requests
    FOR EACH ROW
    EXECUTE FUNCTION update_updated_at_column();

-- Create function to check for multiple active bids
CREATE OR REPLACE FUNCTION check_active_bids()
RETURNS TRIGGER AS $$
BEGIN
    IF NEW.current_status IN ('PENDING', 'VIEWED', 'APPROVED') THEN
        IF EXISTS (
            SELECT 1 FROM bid_requests
            WHERE tenant_id = NEW.tenant_id
            AND id != NEW.id
            AND current_status IN ('PENDING', 'VIEWED', 'APPROVED')
            AND deleted_at IS NULL
        ) THEN
            RAISE EXCEPTION 'Tenant already has an active bid';
        END IF;
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Create trigger for active bids check
CREATE TRIGGER check_tenant_active_bids
    BEFORE INSERT OR UPDATE ON bid_requests
    FOR EACH ROW
    EXECUTE FUNCTION check_active_bids();

down.sql:

-- Drop triggers
DROP TRIGGER IF EXISTS check_tenant_active_bids ON bid_requests;
DROP TRIGGER IF EXISTS record_bid_status ON bid_requests;
DROP TRIGGER IF EXISTS update_bid_requests_updated_at ON bid_requests;

-- Drop functions
DROP FUNCTION IF EXISTS check_active_bids();
DROP FUNCTION IF EXISTS record_bid_status_change();

-- Drop indexes
DROP INDEX IF EXISTS idx_bid_requests_apartment;
DROP INDEX IF EXISTS idx_bid_requests_tenant;
DROP INDEX IF EXISTS idx_bid_requests_status;
DROP INDEX IF EXISTS idx_bid_requests_dates;
DROP INDEX IF EXISTS idx_bid_histories_request;
DROP INDEX IF EXISTS idx_bid_histories_dates;

-- Drop tables
DROP TABLE IF EXISTS bid_status_histories;
DROP TABLE IF EXISTS bid_requests;

Status Flow Diagram

stateDiagram-v2
    [*] --> PENDING: Create Bid
    PENDING --> VIEWED: Owner Views
    VIEWED --> APPROVED: Owner Approves
    APPROVED --> CONFIRMED: Owner Confirms
    CONFIRMED --> ESCROW_FUNDED: Tenant Funds
    ESCROW_FUNDED --> ESCROW_COMPLETED: Owner Completes

    PENDING --> CANCELLED: Cancel
    VIEWED --> CANCELLED: Cancel
    APPROVED --> CANCELLED: Cancel

Permissions Configuration

Tenant Permissions

bid_requests:
  select:
    filter:
      tenant_id: X-Hasura-User-Id
  insert:
    check:
      tenant_id: X-Hasura-User-Id
  update:
    filter:
      tenant_id: X-Hasura-User-Id
    check:
      current_status:
        _in: ["CANCELLED"]

bid_status_histories:
  select:
    filter:
      bid_request:
        tenant_id: X-Hasura-User-Id

Landlord Permissions

bid_requests:
  select:
    filter:
      apartment:
        owner_id: X-Hasura-User-Id
  update:
    filter:
      apartment:
        owner_id: X-Hasura-User-Id
    check:
      current_status:
        _in: ["VIEWED", "APPROVED", "CONFIRMED"]

bid_status_histories:
  select:
    filter:
      bid_request:
        apartment:
          owner_id: X-Hasura-User-Id

Verification Steps

1. Apply migration:

   hasura migrate apply

2. Test status flow:

   
   -- Create bid
   INSERT INTO bid_requests (
   apartment_id,
   tenant_id,
   proposed_price,
   desired_move_in
   ) VALUES (
   'apartment-uuid',
   'tenant-id',
   1000.00,
   NOW() + interval '1 month'
   );

-- Update status UPDATE bid_requests SET current_status = 'VIEWED' WHERE id = 'bid-uuid';

-- Verify history SELECT * FROM bid_status_histories WHERE bid_request_id = 'bid-uuid' ORDER BY created_at;

## Prerequisites
- [x] Users table created
- [x] Apartments table created
- [ ] Permissions system configured

## Success Criteria
- [ ] Tables created with correct constraints
- [ ] Status history automatically recorded
- [ ] Multiple active bids prevented
- [ ] Indexes optimized for common queries
- [ ] Permissions properly configured
- [ ] Status flow working correctly

## Additional Context
This implementation:
- Enforces status flow rules
- Maintains complete status history
- Prevents multiple active bids
- Records who made each status change
- Supports soft deletion
- Includes proper indexing

## Related Issues
- safetrustcr/Backend#22 Firebase Auth Integration
- safetrustcr/Backend#10 Create Apartments Table

[jaykayudo]

Can i be assigned this?

[Michaelkingsdev]

Could I take on this issue?

[ShantelPeters]

May I try my hand at this?

[lauchaves]

Hey! I'm Lau Chaves, and I would like to contribute to this issue!

I have over 5 years of experience working with JavaScript, React, and TypeScript, ruby... Im also part of Dojo Coding Costa Rica!

This issue has the steps pretty clear so I make sure I follow them, I would start by creating the bid_requests and bid_status_histories tables to store all the bid data and track status changes. Then, I set up triggers to automatically log any status changes in the bid history, so we don't have to do it manually. I'd add indexes on key fields like apartment_id and status to make searches faster. For permissions, I’d use Hasura as mentioned above and finally, I’d run tests to make sure everything works!

Its been a while since I worked on DB so would like to take this one as a refresher and at the same time contribute to this project!

[martinvibes]

Can I take care of this issue? i'm a frontend dev and a blockchain dev please kindly assign :)

[Cbiux]

Hi My name is Sebastián Ceciliano I have experience developing and designing relational databases, as well as implementing backend workflows. I am confident that I can implement this functionality efficiently, including:

-Creating tables and migrations with appropriate constraints. -Setting up triggers and functions to automate records in history. -Defining state flows and validating transitions. -Setting permissions to ensure security and access control.

Also , I have knowledge in query optimization and schema design that supports scalable operations. As part of the Web3 community --DecentralU-- in Costa Rica, I am passionate about contributing to innovative projects, and I am committed to moving this forward no matter what the cost.

I look forward to the opportunity to collaborate on this!

[juandiegocv27]

Can I tackle this one?

I’m Juan Diego, part of the Dojo Coding community. I’d love to take on this issue as it aligns with my goal of transitioning from frontend development to learning and contributing more to backend workflows. I have experience working with APIs, including the Trustless API, and have previously worked on projects involving frontend integrations.

This issue looks like a great opportunity to deepen my understanding of backend development, particularly database management, migrations, and permissions configuration. I’d approach this by carefully following the guidelines, setting up the bid_requests and bid_status_histories tables, implementing triggers for automatic status history logging, and ensuring everything runs smoothly with proper testing.

I’m eager to learn more about the backend side of SafeTrust's ecosystem and would greatly appreciate the chance to contribute to this feature.

Hope to get assign and work in this amazing project that won in the EthPuraVida Hackathon. 🦦

[Amarjeet325]

Hi , I am a proficient MERN stack developer with experience in full-stack development. I believe I can contribute effectively to this project. Could you kindly assign this issue/task to me?

[bruhhgnik]

I'd love to work on this, confident with ETA to be under 48 hours

[rvalenciano]

@juandiegocv27 feel free to work on this one!

[juandiegocv27]

@juandiegocv27 feel free to work on this one!

thank you @rvalenciano, i'll let you know any update or block 🫡🔥