Skip to content

CONTRACT_STORAGE.md

Latest commit

 

History

History
131 lines (111 loc) · 4.04 KB

CONTRACT_STORAGE.md

File metadata and controls

131 lines (111 loc) · 4.04 KB

Contract Storage in Database

Database Table: public.contracts

Contracts are stored in the public.contracts table in your Supabase database.

Table Structure

CREATE TABLE public.contracts (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  user_id UUID NOT NULL REFERENCES public.users(id) ON DELETE CASCADE,
  property_id UUID REFERENCES public.properties(id) ON DELETE SET NULL,
  contract_type TEXT NOT NULL CHECK (contract_type IN ('property_management', 'maintenance', 'leasing')),
  status TEXT NOT NULL DEFAULT 'draft' CHECK (status IN ('draft', 'pending', 'signed', 'active', 'terminated')),
  start_date DATE,
  end_date DATE,
  monthly_fee DECIMAL(10, 2),
  fee_type TEXT CHECK (fee_type IN ('fixed', 'percentage')) DEFAULT 'fixed',
  terms JSONB,
  signed_at TIMESTAMPTZ,
  signature_data JSONB,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

Contract HTML Storage

The contract HTML (the rendered contract document) is stored in the terms JSONB column under the key contract_html:

{
  "owner": {
    "full_name": "John Doe",
    "email": "owner@example.com"
  },
  "property": {
    "address_1": "123 Main St",
    "city": "Oslo",
    "state": "NO",
    "zip_code": "0001"
  },
  "contract_html": "<html>...full contract HTML...</html>",
  "service_id": "uuid-of-service"
}

Signature Data Storage

Signature information is stored in the signature_data JSONB column:

{
  "owner": {
    "token": "unique-token-for-signing",
    "signed": true/false,
    "signature": "data:image/png;base64,..." or "text signature",
    "signed_at": "2026-01-25T10:30:00Z"
  },
  "company": {
    "signed": true,
    "signature": "Cozy Lift AS",
    "signed_at": "2026-01-25T10:00:00Z",
    "signed_by": "user-uuid"
  }
}

Accessing Contracts

  1. Via Supabase Dashboard:

    • Go to Supabase Dashboard → Table Editor → contracts table
    • View all contract records
    • Click on a row to see the full JSONB data

  2. Via SQL Query:

    -- View all contracts
SELECT * FROM public.contracts;

-- View contract HTML for a specific contract
SELECT 
  id,
  contract_type,
  status,
  terms->>'contract_html' as contract_html
FROM public.contracts
WHERE id = 'contract-uuid';

-- View signature data
SELECT 
  id,
  signature_data->'owner' as owner_signature,
  signature_data->'company' as company_signature
FROM public.contracts
WHERE id = 'contract-uuid';

  3. Via Application:

    • Admins can view contracts in /dashboard/contracts/[id]
    • Contract HTML is displayed in the ContractView component
    • The HTML is rendered using dangerouslySetInnerHTML

Key Fields

  • id: Unique contract identifier (UUID)
  • user_id: ID of the admin user who created the contract
  • property_id: Optional link to a property (can be null if manual address)
  • contract_type: Type of contract ('property_management', 'maintenance', 'leasing')
  • status: Current status ('draft', 'pending', 'signed', 'active', 'terminated')
  • terms: JSONB object containing:
    • owner: Owner details (name, email)
    • property: Property details (address, city, zip_code)
    • contract_html: The full rendered contract HTML
    • service_id: ID of the service used
    • company: Company details (name, email)
  • signature_data: JSONB object containing signature information for both owner and company
  • monthly_fee: The fee amount (decimal)
  • fee_type: 'fixed' or 'percentage'
  • start_date / end_date: Contract period dates
  • signed_at: Timestamp when contract was fully signed
  • created_at / updated_at: Timestamps for record tracking

Notes

  • The contract HTML is generated when the contract is created or updated
  • The HTML is stored as a string within the JSONB terms column
  • PDF versions are generated on-the-fly when sending emails (not stored in DB)
  • Contracts are linked to properties via property_id (nullable)
  • All contracts belong to an admin user via user_id