Livestock Marketplace – Listing Service Spec

This document covers:

1. All DB tables required for animal listings
2. API endpoints – purpose, request, and response formats

---

1. Database Tables

Convention: Every table has created_at and updated_at (TIMESTAMP).

1.1 users (reference)

Minimal definition (assuming you already have auth elsewhere).

Column	Type	Constraints	Description
id	UUID	PK	User ID (seller/buyer)
name	VARCHAR	NOT NULL	Display name
phone	VARCHAR	UNIQUE, NOT NULL	Phone number
created_at	TIMESTAMP	NOT NULL	Row created time
updated_at	TIMESTAMP	NOT NULL	Row last updated time

Relationships

• 1 user → N listings
• 1 user → N locations (saved addresses only)

---

1.2 species

Column	Type	Constraints	Description
id	INT	PK	Species ID
name	VARCHAR	UNIQUE	e.g. Cattle, Buffalo, Goat
created_at	TIMESTAMP	NOT NULL
updated_at	TIMESTAMP	NOT NULL

Relationships

• 1 species → N breeds
• 1 species → N animals

---

1.3 breeds

Column	Type	Constraints	Description
id	INT	PK	Breed ID
species_id	INT	FK → species.id	Parent species
name	VARCHAR	NOT NULL	e.g. Gir, Murrah
description	TEXT	NULL	Optional notes
created_at	TIMESTAMP	NOT NULL
updated_at	TIMESTAMP	NOT NULL

Relationships

• 1 species → N breeds
• 1 breed → N animals

---

1.4 locations

Used both for:

• Captured locations (no user, not saved; user_id = NULL, is_saved_address = false)
• Saved addresses for a user (e.g. farm, home; user_id set, is_saved_address = true)

Column	Type	Constraints	Description
id	UUID	PK	Location ID
user_id	UUID	FK → users.id, NULLABLE	Owner if this is a saved address; NULL if just captured for a listing
is_saved_address	BOOLEAN	NOT NULL, default false	True if user chose to save it as an address
location_type	VARCHAR	NULL (enum suggestion)	e.g. farm, home, office, other
country	VARCHAR	NULL	Country
state	VARCHAR	NULL	State
district	VARCHAR	NULL	District
city_village	VARCHAR	NULL	City / village
pincode	VARCHAR	NULL	Postal code
lat	DECIMAL	NULL	Latitude
lng	DECIMAL	NULL	Longitude
source_type	VARCHAR	NOT NULL, default unknown	device_gps, manual, unknown
source_confidence	VARCHAR	NOT NULL, default medium	high, medium, low
created_at	TIMESTAMP	NOT NULL
updated_at	TIMESTAMP	NOT NULL

Interpretation

• Captured only: user_id = NULL, is_saved_address = false
• Captured + saved as user’s farm/home: set user_id, is_saved_address = true, location_type = 'farm' (or similar).

Relationships

• 1 user → N locations (saved addresses)
• 1 location → N animals (many animals can share same farm address)

---

1.5 animals

One animal per listing (enforced via unique constraint at listings.animal_id).

Column	Type	Constraints	Description
id	UUID	PK	Animal ID
species_id	INT	FK → species.id, NOT NULL	Species
breed_id	INT	FK → breeds.id, NULL	Breed (optional)
sex	VARCHAR	NOT NULL	M, F, Neutered
age_months	INT	NULL	Age in months
weight_kg	DECIMAL	NULL	Approx weight
color_markings	VARCHAR	NULL	Color / markings
quantity	INT	NOT NULL, default 1	Number of animals in this listing
purpose	VARCHAR	NOT NULL	dairy, meat, breeding, pet, work, etc
health_status	VARCHAR	NOT NULL	healthy, minor_issues, serious_issues
vaccinated	BOOLEAN	NOT NULL, default false
dewormed	BOOLEAN	NOT NULL, default false
previous_pregnancies_count	INT	NULL	For females, number of previous pregnancies
pregnancy_status	VARCHAR	NULL	not_pregnant, pregnant, recently_calved
milk_yield_litre_per_day	DECIMAL	NULL	Avg daily milk yield
ear_tag_no	VARCHAR	NULL	Tag / registration ID
description	TEXT	NULL	Detailed description
suggested_care	TEXT	NULL	Suggested food & accessories (free-text)
location_id	UUID	FK → locations.id, NULL	Location of animal (farm etc.). NULL if unknown
created_at	TIMESTAMP	NOT NULL
updated_at	TIMESTAMP	NOT NULL

Relationships

• 1 species → N animals
• 1 breed → N animals
• 1 location → N animals
• 1 animal ↔ 1 listing (via listings.animal_id UNIQUE)

---

1.6 listings

One listing per animal (1–1).

Column	Type	Constraints	Description
id	UUID	PK	Listing ID
seller_id	UUID	FK → users.id, NOT NULL	Seller
animal_id	UUID	FK → animals.id, UNIQUE, NOT NULL	The animal this listing is for
title	VARCHAR	NOT NULL	Listing title
price	DECIMAL	NOT NULL	Asking price
currency	VARCHAR	NOT NULL, e.g. INR	Currency code
is_negotiable	BOOLEAN	NOT NULL, default true	Price negotiable
listing_type	VARCHAR	NOT NULL	sale, stud_service, adoption
status	VARCHAR	NOT NULL, default active	active, sold, expired, hidden
views_count	INT	NOT NULL, default 0	Total views
bookmarks_count	INT	NOT NULL, default 0	Times bookmarked
enquiries_call_count	INT	NOT NULL, default 0	Phone enquiries
enquiries_whatsapp_count	INT	NOT NULL, default 0	WhatsApp enquiries
clicks_count	INT	NOT NULL, default 0	Other CTA clicks (e.g. “View number”)
created_at	TIMESTAMP	NOT NULL
updated_at	TIMESTAMP	NOT NULL

Relationships

• 1 user → N listings
• 1 animal ↔ 1 listing
• 1 listing → N listing_media

---

1.7 listing_media (images / videos)

Column	Type	Constraints	Description
id	UUID	PK	Media ID
listing_id	UUID	FK → listings.id	Parent listing
media_url	VARCHAR	NOT NULL	URL to image/video
media_type	VARCHAR	NOT NULL	image, video
is_primary	BOOLEAN	NOT NULL, default false	True if main display image/video
sort_order	INT	NOT NULL, default 0	For ordering media in gallery
created_at	TIMESTAMP	NOT NULL
updated_at	TIMESTAMP	NOT NULL

Relationships

• 1 listing → N listing_media

---

1.8 Relationship Summary

• 1–N

  • users → listings
  • users → locations (saved addresses)
  • species → breeds
  • species → animals
  • breeds → animals
  • locations → animals
  • listings → listing_media

• 1–1

  • animals ↔ listings (enforced via listings.animal_id UNIQUE)

• N–M

  • None currently; all many-to-many are avoided in this MVP schema.

---

2. API Endpoints

2.1 Create Listing (with Animal + optional Location)

POST /listings

Purpose

Create a new listing and its animal. Optionally:

• Use an existing location_id or
• Create a new captured/saved location in the same call.

Request (JSON)

{
	"seller_id": "UUID-of-seller",
	"title": "High-yield Gir cow for sale",
	"price": 55000,
	"currency": "INR",
	"is_negotiable": true,
	"listing_type": "sale",
	"animal": {
		"species_id": 1,
		"breed_id": 10,
		"sex": "F",
		"age_months": 36,
		"weight_kg": 450,
		"color_markings": "Brown with white patches",
		"quantity": 1,
		"purpose": "dairy",
		"health_status": "healthy",
		"vaccinated": true,
		"dewormed": true,
		"previous_pregnancies_count": 1,
		"pregnancy_status": "pregnant",
		"milk_yield_litre_per_day": 15,
		"ear_tag_no": "TAG-12345",
		"description": "Calm nature, easy to handle.",
		"suggested_care": "Green fodder, mineral mix, clean shed.",
		"location_id": "existing-location-uuid",
		"new_location": {
			"country": "India",
			"state": "Maharashtra",
			"district": "Pune",
			"city_village": "Baramati",
			"pincode": "413102",
			"lat": 18.15,
			"lng": 74.5833,
			"source_type": "device_gps",
			"source_confidence": "high",
			"save_as_address": true,
			"location_type": "farm"
		}
	},
	"media": [
		{
			"media_url": "https://cdn.app.com/listings/abc1.jpg",
			"media_type": "image",
			"is_primary": true,
			"sort_order": 1
		}
	]
}

Notes:

• Client can either:

  • Provide location_id, or
  • Provide new_location object. If save_as_address = true, backend should create a locations row with user_id = seller_id, is_saved_address = true.

• Media is optional in this first call; can also be added later via media APIs.

Response (201 Created)

{
	"listing": {
		"id": "listing-uuid",
		"seller_id": "UUID-of-seller",
		"animal_id": "animal-uuid",
		"title": "High-yield Gir cow for sale",
		"price": 55000,
		"currency": "INR",
		"is_negotiable": true,
		"listing_type": "sale",
		"status": "active",
		"views_count": 0,
		"bookmarks_count": 0,
		"enquiries_call_count": 0,
		"enquiries_whatsapp_count": 0,
		"clicks_count": 0,
		"created_at": "2025-11-22T10:00:00Z",
		"updated_at": "2025-11-22T10:00:00Z",
		"animal": {
			"id": "animal-uuid",
			"species_id": 1,
			"breed_id": 10,
			"sex": "F",
			"age_months": 36,
			"weight_kg": 450,
			"color_markings": "Brown with white patches",
			"quantity": 1,
			"purpose": "dairy",
			"health_status": "healthy",
			"vaccinated": true,
			"dewormed": true,
			"previous_pregnancies_count": 1,
			"pregnancy_status": "pregnant",
			"milk_yield_litre_per_day": 15,
			"ear_tag_no": "TAG-12345",
			"description": "Calm nature, easy to handle.",
			"suggested_care": "Green fodder, mineral mix, clean shed.",
			"location": {
				"id": "location-uuid",
				"user_id": "UUID-of-seller",
				"is_saved_address": true,
				"location_type": "farm",
				"country": "India",
				"state": "Maharashtra",
				"district": "Pune",
				"city_village": "Baramati",
				"pincode": "413102",
				"lat": 18.15,
				"lng": 74.5833,
				"source_type": "device_gps",
				"source_confidence": "high"
			}
		},
		"media": [
			{
				"id": "media-uuid",
				"media_url": "https://cdn.app.com/listings/abc1.jpg",
				"media_type": "image",
				"is_primary": true,
				"sort_order": 1
			}
		]
	}
}

---

2.2 List / Search Listings

GET /listings

Purpose

List active listings with optional filters (species, location, price, etc.).

Query parameters (examples)

• species_id (int, optional)
• breed_id (int, optional)
• state (string, optional)
• district (string, optional)
• min_price, max_price (optional)
• listing_type (string, optional)
• page, page_size (for pagination)

Request

GET /listings?species_id=1&state=Maharashtra&page=1&page_size=20

Response (200 OK)

{
	"items": [
		{
			"id": "listing-uuid",
			"title": "High-yield Gir cow for sale",
			"price": 55000,
			"currency": "INR",
			"is_negotiable": true,
			"listing_type": "sale",
			"status": "active",
			"species_id": 1,
			"breed_id": 10,
			"animal_id": "animal-uuid",
			"thumbnail_url": "https://cdn.app.com/listings/abc1.jpg",
			"location_summary": {
				"state": "Maharashtra",
				"district": "Pune",
				"city_village": "Baramati"
			},
			"created_at": "2025-11-22T10:00:00Z"
		}
	],
	"page": 1,
	"page_size": 20,
	"total": 1
}

---

2.3 Get Listing Detail

GET /listings/{listing_id}

Purpose

Get full details of a single listing (including animal, location, media).

Response (200 OK)

{
	"id": "listing-uuid",
	"seller_id": "UUID-of-seller",
	"animal_id": "animal-uuid",
	"title": "High-yield Gir cow for sale",
	"price": 55000,
	"currency": "INR",
	"is_negotiable": true,
	"listing_type": "sale",
	"status": "active",
	"views_count": 120,
	"bookmarks_count": 10,
	"enquiries_call_count": 5,
	"enquiries_whatsapp_count": 8,
	"clicks_count": 14,
	"created_at": "2025-11-22T10:00:00Z",
	"updated_at": "2025-11-22T11:00:00Z",
	"animal": {
		"...": "full animal object as above"
	},
	"media": [
		{
			"id": "media-uuid",
			"media_url": "https://cdn.app.com/listings/abc1.jpg",
			"media_type": "image",
			"is_primary": true,
			"sort_order": 1
		}
	]
}

---

2.4 Update Listing (and Animal)

PUT /listings/{listing_id}

Purpose

Edit listing fields and animal details (e.g. price, status, description, suggested care).

Request (JSON)

Only fields to update need to be sent (PATCH style with PUT semantics).

{
	"title": "Gir cow – price reduced",
	"price": 52000,
	"status": "active",
	"animal": {
		"description": "Price reduced, urgent sale.",
		"suggested_care": "Green fodder, clean water, regular deworming."
	}
}

Response (200 OK)

Returns updated listing object (same shape as GET /listings/{id}).

---

2.5 Create / Capture Location

POST /locations

Purpose

Create a new location. Used for:

• Saved address for a user (farm/home)
• Captured location for an animal/listing (not necessarily saved)

Request (JSON)

{
	"user_id": "UUID-of-user-or-null",
	"is_saved_address": true,
	"location_type": "farm",
	"country": "India",
	"state": "Maharashtra",
	"district": "Pune",
	"city_village": "Baramati",
	"pincode": "413102",
	"lat": 18.15,
	"lng": 74.5833,
	"source_type": "device_gps",
	"source_confidence": "high"
}

• For captured-only (not saved): set user_id = null, is_saved_address = false.

Response (201 Created)

{
	"id": "location-uuid",
	"user_id": "UUID-of-user-or-null",
	"is_saved_address": true,
	"location_type": "farm",
	"country": "India",
	"state": "Maharashtra",
	"district": "Pune",
	"city_village": "Baramati",
	"pincode": "413102",
	"lat": 18.15,
	"lng": 74.5833,
	"source_type": "device_gps",
	"source_confidence": "high",
	"created_at": "2025-11-22T10:05:00Z",
	"updated_at": "2025-11-22T10:05:00Z"
}

---

2.6 Update Location (PUT for Locations)

PUT /locations/{location_id}

Purpose

Update location details OR convert a captured location into a saved address for a user (e.g. mark as farm).

Request (JSON)

{
	"user_id": "UUID-of-user",
	"is_saved_address": true,
	"location_type": "farm",
	"city_village": "New Village Name",
	"pincode": "413103"
}

Response (200 OK)

{
	"id": "location-uuid",
	"user_id": "UUID-of-user",
	"is_saved_address": true,
	"location_type": "farm",
	"country": "India",
	"state": "Maharashtra",
	"district": "Pune",
	"city_village": "New Village Name",
	"pincode": "413103",
	"lat": 18.15,
	"lng": 74.5833,
	"source_type": "device_gps",
	"source_confidence": "high",
	"created_at": "2025-11-22T10:05:00Z",
	"updated_at": "2025-11-22T11:00:00Z"
}

---

2.7 Get Saved Locations for a User

GET /users/{user_id}/locations

Purpose

Fetch all saved addresses for a given user (farm, home, etc.).

Response (200 OK)

{
	"items": [
		{
			"id": "location-uuid-1",
			"user_id": "UUID-of-user",
			"is_saved_address": true,
			"location_type": "farm",
			"country": "India",
			"state": "Maharashtra",
			"district": "Pune",
			"city_village": "Baramati",
			"pincode": "413102",
			"lat": 18.15,
			"lng": 74.5833,
			"source_type": "device_gps",
			"source_confidence": "high",
			"created_at": "2025-11-22T10:00:00Z",
			"updated_at": "2025-11-22T10:00:00Z"
		}
	]
}

---

2.8 Add Media to Listing

POST /listings/{listing_id}/media

Purpose

Attach new images/videos to a listing after creation.

Request (JSON)

{
	"items": [
		{
			"media_url": "https://cdn.app.com/listings/abc2.jpg",
			"media_type": "image",
			"is_primary": false,
			"sort_order": 2
		},
		{
			"media_url": "https://cdn.app.com/listings/abc3.mp4",
			"media_type": "video",
			"is_primary": false,
			"sort_order": 3
		}
	]
}

Response (201 Created)

{
	"media": [
		{
			"id": "media-uuid-2",
			"listing_id": "listing-uuid",
			"media_url": "https://cdn.app.com/listings/abc2.jpg",
			"media_type": "image",
			"is_primary": false,
			"sort_order": 2
		},
		{
			"id": "media-uuid-3",
			"listing_id": "listing-uuid",
			"media_url": "https://cdn.app.com/listings/abc3.mp4",
			"media_type": "video",
			"is_primary": false,
			"sort_order": 3
		}
	]
}

---

2.9 Update Media (PUT for Images/Media)

PUT /listing-media/{media_id}

Purpose

Update a single media item (e.g. mark as primary, change sort order, fix URL).

Request (JSON)

{
	"is_primary": true,
	"sort_order": 1
}

Response (200 OK)

{
	"id": "media-uuid-2",
	"listing_id": "listing-uuid",
	"media_url": "https://cdn.app.com/listings/abc2.jpg",
	"media_type": "image",
	"is_primary": true,
	"sort_order": 1,
	"created_at": "2025-11-22T10:10:00Z",
	"updated_at": "2025-11-22T10:20:00Z"
}

---

2.10 Create a Custom Requirement

POST /requirements

Purpose

Custom Requirements allow buyers to express needs such as "Looking for a cow giving more than 10 litres of milk per day." These relate to an animal_id and are visible to sellers while listing animals.

Request Body

{
	"buyer_id": 12,
	"animal_id": 201,
	"title": "Cow giving 10+ litres milk",
	"description": "Healthy cow with high milk output",
	"min_price": 30000,
	"max_price": 60000,
	"location": "Pune"
}

Response

{
	"requirement_id": 501,
	"message": "Custom requirement created successfully"
}

---

2.11 Update an Existing Requirement

PUT /requirements/{requirement_id}

Request Body

{
	"title": "Cow giving 12+ litres",
	"max_price": 65000
}

---

2.12 Delete a Requirement

DELETE /requirements/{requirement_id}

---

2.13 Get All Requirements for a Buyer

GET /requirements/buyer/{buyer_id}

Purpose

Retrieves all active and past requirements of a buyer.

---

2.14 Get Matching Requirements for an Animal (For Sellers)

GET /requirements/matching?animal_id={animal_id}

Purpose

Used when a seller lists an animal so the system can show matching requirements.