Feature 07 — Collections (Seasonal Groups)

Phase: 1 (Foundation — no dependencies, enables analytics) Priority: 🟡 Medium (unlocks Feature 03 analytics) Status: ⏳ Pending

Context

Collections are named seasonal groupings of products: "Summer 2026", "AW26 — Autumn/Winter", "Back to School". They are the primary organizational unit for apparel merchandising and the backbone of sell-through reporting.

Without collections:

Analytics cannot group sell-through by season
Buyers cannot plan markdowns by collection
Reports are limited to category/brand groupings

This is a simple master-detail structure: collection → collection_product.

No existing foundation — both tables are new.

Task Checklist

Database

Create migration: packages/backend/database/src/migrations/2026-03-XX-collections.mjs
Create collection table
Create collection_product table
Add indexes and unique constraints
Run pnpm run migration:local:push
Run pnpm run generate:types

Backend

PWA Frontend

Create apps/frontend-pwa/src/types/collection.ts
Create apps/frontend-pwa/src/services/collectionService.ts
Create apps/frontend-pwa/src/components/forms/collection/CollectionPage.tsx
Create apps/frontend-pwa/src/components/forms/collection/CollectionForm.tsx
Create apps/frontend-pwa/src/components/forms/collection/CollectionProductAssignment.tsx
Extend ProductPage.tsx — optional collection filter + collection column in list
Extend ProductForm — optional "Collection" multi-select
Register in apps/frontend-pwa/src/pages/MainPage.tsx

Verification

Migration applies cleanly
Types regenerated
Backend builds
POST /collections creates a collection
POST /collections/:id/products adds products to collection
GET /collections/:id/products returns product list
Collection selector appears in product filter
Analytics reports can filter by collectionId

Database Schema

// packages/backend/database/src/migrations/2026-03-XX-collections.mjs
import { Kysely, sql } from "kysely";

export async function up(db) {
  // 1. Collection master
  await db.schema
    .createTable("collection")
    .addColumn("id", "uuid", (col) => col.primaryKey().defaultTo(sql`gen_random_uuid()`))
    .addColumn("business_id", "uuid", (col) => col.notNull().references("business.id").onDelete("cascade"))
    .addColumn("name", "varchar", (col) => col.notNull())
    .addColumn("code", "varchar")
    // Short code: 'SS26', 'AW26', 'BTS26'
    .addColumn("season", "varchar")
    // 'spring_summer' | 'autumn_winter' | 'cruise' | 'resort' | 'all_year'
    .addColumn("year", "integer")
    .addColumn("description", "text")
    .addColumn("image_url", "text")
    .addColumn("valid_from", "timestamptz")
    .addColumn("valid_to", "timestamptz")
    .addColumn("is_active", "boolean", (col) => col.notNull().defaultTo(true))
    .addColumn("created_at", "timestamptz", (col) => col.notNull().defaultTo(sql`CURRENT_TIMESTAMP`))
    .addColumn("created_by", "uuid", (col) => col.notNull().references("user.id").onDelete("cascade"))
    .addColumn("updated_at", "timestamptz")
    .addColumn("updated_by", "uuid", (col) => col.references("user.id").onDelete("set null"))
    .execute();

  await db.schema.createIndex("idx_collection_business")
    .on("collection")
    .columns(["business_id", "is_active"])
    .execute();

  await db.schema.createIndex("idx_collection_code_unique")
    .on("collection")
    .columns(["business_id", "code"])
    .where(sql`code IS NOT NULL`)
    .unique()
    .execute();

  // 2. Product assignment (many-to-many)
  await db.schema
    .createTable("collection_product")
    .addColumn("id", "uuid", (col) => col.primaryKey().defaultTo(sql`gen_random_uuid()`))
    .addColumn("collection_id", "uuid", (col) => col.notNull().references("collection.id").onDelete("cascade"))
    .addColumn("product_id", "uuid", (col) => col.notNull().references("product.id").onDelete("cascade"))
    .addColumn("business_id", "uuid", (col) => col.notNull().references("business.id").onDelete("cascade"))
    .addColumn("sort_order", "integer", (col) => col.defaultTo(0))
    .addColumn("created_at", "timestamptz", (col) => col.notNull().defaultTo(sql`CURRENT_TIMESTAMP`))
    .addColumn("created_by", "uuid", (col) => col.notNull().references("user.id").onDelete("cascade"))
    .execute();

  await db.schema.createIndex("idx_collection_product_unique")
    .on("collection_product")
    .columns(["collection_id", "product_id"])
    .unique()
    .execute();

  await db.schema.createIndex("idx_collection_product_lookup")
    .on("collection_product")
    .columns(["business_id", "product_id"])
    .execute();
}

export async function down(db) {
  await db.schema.dropIndex("idx_collection_product_lookup").execute();
  await db.schema.dropIndex("idx_collection_product_unique").execute();
  await db.schema.dropIndex("idx_collection_code_unique").execute();
  await db.schema.dropIndex("idx_collection_business").execute();
  await db.schema.dropTable("collection_product").ifExists().execute();
  await db.schema.dropTable("collection").ifExists().execute();
}

Backend Implementation

Domain Interface

// domain/collections-repository.domain.ts
export interface Collection {
  id: string;
  businessId: string;
  name: string;
  code: string | null;
  season: string | null;
  year: number | null;
  description: string | null;
  imageUrl: string | null;
  validFrom: Date | null;
  validTo: Date | null;
  isActive: boolean;
  createdAt: Date;
  createdBy: string;
  updatedAt: Date | null;
  updatedBy: string | null;
  productCount?: number; // joined aggregate
}

export interface ICollectionsRepository {
  create(data: Insertable<Collection>): Promise<Collection>;
  findById(id: string, businessId: string): Promise<Collection | undefined>;
  findByBusiness(businessId: string, filters: CollectionFilters): Promise<IOffsetPagination<Collection>>;
  update(id: string, data: Partial<Collection>): Promise<Collection>;
  softDelete(id: string, businessId: string): Promise<void>;
  addProducts(collectionId: string, productIds: string[], createdBy: string): Promise<void>;
  removeProduct(collectionId: string, productId: string): Promise<void>;
  findProducts(collectionId: string, page: number, size: number): Promise<IOffsetPagination<Product>>;
}

Endpoints: `collections.controller.ts`

@Controller("collections")
export class CollectionsController {
  // Collection CRUD
  @Post() create(@Body() dto: CreateCollectionDTO)
  @Get() findAll(@Query() query: PaginateCollectionsQuery)
  @Get(":id") findOne(@Param("id") id: string, @Query("businessId") businessId: string)
  @Patch(":id") update(@Param("id") id: string, @Body() dto: UpdateCollectionDTO)
  @Delete(":id") remove(@Param("id") id: string, @Query("businessId") businessId: string)

  // Product assignment
  @Post(":id/products") addProducts(
    @Param("id") id: string,
    @Body() dto: AddCollectionProductsDTO
  )
  @Delete(":id/products/:productId") removeProduct(
    @Param("id") id: string,
    @Param("productId") productId: string,
  )
  @Get(":id/products") listProducts(
    @Param("id") id: string,
    @Query() query: PaginateCollectionProductsQuery,
  )
}

DTOs

export class CreateCollectionDTO {
  @IsUUID() businessId: string;
  @IsUUID() createdBy: string;
  @IsString() @IsNotEmpty() name: string;
  @IsOptional() @IsString() code?: string;
  @IsOptional() @IsString() season?: string;
  @IsOptional() @IsInt() year?: number;
  @IsOptional() @IsString() description?: string;
  @IsOptional() @IsUrl() imageUrl?: string;
  @IsOptional() @IsDateString() validFrom?: string;
  @IsOptional() @IsDateString() validTo?: string;
}

export class AddCollectionProductsDTO {
  @IsUUID() businessId: string;
  @IsUUID() createdBy: string;
  @IsArray() @IsUUID("4", { each: true }) productIds: string[];
}

PWA Frontend

Types: `types/collection.ts`

export interface Collection {
  id?: string;
  businessId: string;
  name: string;
  code?: string;
  season?: 'spring_summer' | 'autumn_winter' | 'cruise' | 'resort' | 'all_year';
  year?: number;
  description?: string;
  imageUrl?: string;
  validFrom?: string;
  validTo?: string;
  isActive: boolean;
  productCount?: number;
}

export interface CollectionFormData {
  id?: string;
  name: string;
  code?: string;
  season?: string;
  year?: number;
  description?: string;
  validFrom?: Date;
  validTo?: Date;
  isActive: boolean;
}

Service: `collectionService.ts`

export async function getCollections(
  token: string,
  businessId: string,
  filters?: { season?: string; year?: number; isActive?: boolean },
  page?: number,
  size?: number,
): Promise<IOffsetPagination<Collection>>

export async function createCollection(token: string, data: Partial<Collection>): Promise<Collection>
export async function updateCollection(token: string, id: string, data: Partial<Collection>): Promise<Collection>
export async function deleteCollection(token: string, id: string, businessId: string): Promise<void>

export async function addProductsToCollection(
  token: string,
  collectionId: string,
  productIds: string[],
  businessId: string,
  createdBy: string,
): Promise<void>

export async function removeProductFromCollection(
  token: string,
  collectionId: string,
  productId: string,
): Promise<void>

export async function getCollectionProducts(
  token: string,
  collectionId: string,
  page?: number,
  size?: number,
): Promise<IOffsetPagination<Product>>

Page Layout: `CollectionPage.tsx`

┌──────────────────────────────────────────────────────────┐
│  Collections                          [+ New Collection] │
├──────────────────────────────────────────────────────────┤
│  Season [All ▼]  Year [2026 ▼]  Status [Active ▼]       │
├──────────────────────────────────────────────────────────┤
│  Name          │ Code │ Season      │ Products │ Status  │
│  Summer 2026   │ SS26 │ Spring/Sum  │ 124      │ Active  │
│  AW 2026       │ AW26 │ Autumn/Win  │ 0        │ Pending │
└──────────────────────────────────────────────────────────┘

[When a collection is selected → expand to show:]
┌──────────────────────────────────────────────────────────┐
│  Summer 2026 — Product Assignment                        │
│  [Search products...]              [+ Add Products]      │
├──────────────────────────────────────────────────────────┤
│  ☑ Blue Linen Shirt   SKU: BLS-001  Category: Shirts    │
│  ☑ Floral Dress       SKU: FD-002   Category: Dresses   │
│  [Remove selected]                                       │
└──────────────────────────────────────────────────────────┘

Collection Form

Name:        [Summer 2026_______________________]
Code:        [SS26]
Season:      [Spring/Summer ▼]
Year:        [2026]
Valid from:  [2026-03-01]  to  [2026-08-31]
Description: [Summer coastal collection...]
[Save Collection]

Context​

Task Checklist​

Database​

Backend​

PWA Frontend​

Verification​

Database Schema​

Backend Implementation​

Domain Interface​

Endpoints: collections.controller.ts​

DTOs​

PWA Frontend​

Types: types/collection.ts​

Service: collectionService.ts​

Page Layout: CollectionPage.tsx​

Collection Form​

Context

Task Checklist

Database

Backend

PWA Frontend

Verification

Database Schema

Backend Implementation

Domain Interface

Endpoints: `collections.controller.ts`

DTOs

PWA Frontend

Types: `types/collection.ts`

Service: `collectionService.ts`

Page Layout: `CollectionPage.tsx`

Collection Form