System Name: ecommerce_full_stack
Description: A complete ecommerce backend system including authentication, product catalog, categories, cart, orders, payments, reviews, coupons, and notifications.
This system follows:
- Relational database design
- Normalized schema
- PostPipe-style relational form linking
- Dynamic form-driven architecture
- All entities must be normalized into separate tables
- Relationships must be defined using IDs (not duplicated data)
- UI display values must be separated from stored values
- All cross-entity linking must use relational fields
- System must support dynamic form generation
Definition: A "relationship field" is a field marked as globally discoverable, allowing other forms to reference it using relational data type.
-
When a field is marked: relationship_field = true
-
It becomes available globally as: form_name.field_name
Form: categories Field: name (relationship_field = true)
→ Available as: categories.name
Definition: A field that fetches and links data from another form using a relationship field.
Form: products Field:
- name: category
- type: relational_data
- source: categories.name
- UI shows dropdown of category names
- Stored value = category ID
- Display value = category name
When a relational field is used:
Fetch:
- id (primary key)
- display field (relationship field)
Return format: [ { "id": "cat_001", "name": "Electronics" } ]
Fields:
- id: uuid (primary key)
- name: string
- slug: string
- description: text
- category_id: relation(categories.id)
- price: decimal
- compare_price: decimal
- cost_price: decimal
- sku: string
- stock_quantity: int
- low_stock_threshold: int
- weight_kg: decimal
- dimensions: json
- material: string
- finish: string
- lead_time_days: int
- warranty_years: int
- is_active: boolean
- is_featured: boolean
- images: json
- tags: json
- rating_avg: decimal
- rating_count: int
- created_at: timestamp
- updated_at: timestamp
Fields:
- id: uuid
- name: string (relationship_field = true)
- slug: string
- parent_id: relation(categories.id)
- image_url: string
- sort_order: int
Fields:
- id: uuid
- user_id: relation(users.id)
- label: string
- full_name: string
- phone: string
- line1: string
- line2: string
- city: string
- state: string
- pincode: string
- country: string
- is_default: boolean
Fields:
- id: uuid
- user_id: relation(users.id)
- product_id: relation(products.id)
- quantity: int
- added_at: timestamp
Fields:
- id: uuid
- user_id: relation(users.id)
- product_id: relation(products.id)
- added_at: timestamp
Fields:
- id: uuid
- order_number: string
- user_id: relation(users.id)
- status: enum(pending, confirmed, processing, shipped, delivered, cancelled, refunded)
- address_id: relation(addresses.id)
- subtotal: decimal
- discount_amount: decimal
- shipping_amount: decimal
- tax_amount: decimal
- total_amount: decimal
- coupon_code: string
- notes: text
- estimated_delivery_date: date
- created_at: timestamp
Fields:
- id: uuid
- order_id: relation(orders.id)
- product_id: relation(products.id)
- product_name: string (snapshot)
- product_image: string (snapshot)
- unit_price: decimal
- quantity: int
- total_price: decimal
Fields:
- id: uuid
- order_id: relation(orders.id)
- user_id: relation(users.id)
- amount: decimal
- currency: string
- method: enum(upi, card, netbanking, cod, wallet)
- gateway: string
- gateway_payment_id: string
- gateway_order_id: string
- status: enum(pending, paid, failed, refunded)
- paid_at: timestamp
Fields:
- id: uuid
- product_id: relation(products.id)
- user_id: relation(users.id)
- order_id: relation(orders.id)
- rating: int
- title: string
- body: text
- is_approved: boolean
- created_at: timestamp
Fields:
- id: uuid
- code: string
- type: enum(percent, flat)
- value: decimal
- min_order_value: decimal
- max_uses: int
- used_count: int
- expires_at: timestamp
Fields:
- id: uuid
- user_id: relation(users.id)
- title: string
- body: text
- type: enum(order_update, promo, restock)
- is_read: boolean
- created_at: timestamp
-
users → addresses (1:N)
-
users → cart_items (1:N)
-
users → wishlist_items (1:N)
-
users → orders (1:N)
-
users → payments (1:N)
-
users → notifications (1:N)
-
categories → products (1:N)
-
categories → categories (self relation)
-
products → cart_items (1:N)
-
products → wishlist_items (1:N)
-
products → order_items (1:N)
-
products → reviews (1:N)
-
orders → order_items (1:N)
-
orders → payments (1:N)
-
addresses → orders (1:N)
- Every order must have at least one order_item
- order_items must store snapshot of product data
- stock_quantity must reduce after successful order
- cart_items are temporary and user-specific
- wishlist does not affect stock
- reviews require a valid order
- relational_data must always reference a relationship_field
- internal storage must always use IDs (not names)
Steps:
- Fetch cart_items by user_id
- Calculate subtotal
- Apply coupon if exists
- Create order
- Create order_items
- Process payment
- Reduce stock
- Clear cart
- relational_data → dropdown or searchable select
- large datasets → lazy loading
- display value shown to user
- ID stored internally
- auto-fetch on form load
When user says: "Build ecommerce system"
Piko must:
- Generate all tables
- Define relationships
- Create forms
- Link relational fields
- Define flows (cart → order → payment)
- Apply system rules
END OF TRAINING DATASET