feat: pivot table extra columns + mutations on belongsToMany

Problem

belongsToMany is currently Record<string, ModelName> (see packages/bun-query-builder/src/schema.ts:99), so:

1. There's no way to declare metadata columns on the pivot table — role, status, invited_at, etc.
2. The only pivot query helper is withPivot(relation, ...columns) at the SELECT layer (client.ts:913) — it just unhides pivot columns in results.
3. No mutation API: attach, detach, sync, updateExistingPivot, toggle are all absent.
4. No wherePivot predicate on related-collection queries.

Real-world many-to-many is rarely "just two FKs" — pivot rows nearly always carry metadata (team membership with role, follow relationships with created_at, tag assignments with weight, etc.).

Concrete use case

Building a TrainingPeaks-style coach app where one athlete can have:

• Exactly one primary coach
• N shared coaches (the primary delegates access)
• N assistant coaches (under a head coach's umbrella)

Per TrainingPeaks docs.

I want this on the model:

```ts // Coach.ts belongsToMany: { athletes: 'Athlete' // …with role, status, shared_by_coach_id, invited_at, accepted_at on the pivot } ```

Workaround today

Model the pivot as a first-class entity, skip belongsToMany:

```ts // CoachAthlete.ts defineModel({ name: 'CoachAthlete', table: 'coach_athletes', belongsTo: ['Coach', 'Athlete'], attributes: { coach_id, athlete_id, role, status, shared_by_coach_id, invited_at, accepted_at }, })

// Coach.ts hasMany: { athleteLinks: 'CoachAthlete' }

// Athlete.ts hasMany: { coachLinks: 'CoachAthlete' } ```

Works, but loses every convenience of a real belongsToMany (`coach.athletes`, `attach`, `detach`, `sync`, `wherePivot`).

Proposal — Option A: pivot config inline

```ts belongsToMany: { athletes: { model: 'Athlete', table: 'coach_athletes', // optional, default: snake_case alpha-sorted foreignKey: 'coach_id', // optional, default: snake(self_model_id) relatedKey: 'athlete_id', // optional, default: snake(target_model_id) pivot: { columns: { role: { default: 'shared', validation: { rule: schema.string().max(20) } }, status: { default: 'active', validation: { rule: schema.string().max(20) } }, shared_by_coach_id: { validation: { rule: schema.integer().optional() } }, invited_at: { validation: { rule: schema.string().optional() } }, accepted_at: { validation: { rule: schema.string().optional() } }, }, timestamps: true, uniques: [['coach_id', 'athlete_id']], }, }, } ```

Proposal — Option B: `through` a real model (preferred)

```ts belongsToMany: { athletes: { model: 'Athlete', through: 'CoachAthlete' }, } ```

`through` resolves to a model in the registry; bqb reads pivot columns from that model's attributes. Mirrors Laravel's `->using(PivotModel::class)` and avoids duplicating attribute definitions across two places. Pairs naturally with the first-class-pivot workaround above — gradual adoption path.

Query API

```ts await coach.athletes().wherePivot('role', 'primary').get() await coach.athletes().wherePivotIn('status', ['active', 'pending']).get()

const a = await coach.athletes().first() a.pivot.role a.pivot.invited_at

await coach.athletes().attach(athleteId, { role: 'shared', status: 'pending' }) await coach.athletes().detach(athleteId) await coach.athletes().sync([{ id: 1, role: 'primary' }, { id: 2, role: 'shared' }]) await coach.athletes().updateExistingPivot(athleteId, { role: 'primary' }) ```

Related: composite-unique gap

`CompositeIndex` is currently:

```ts // packages/bun-query-builder/src/schema.ts:81-84 export interface CompositeIndex { name: string columns: string[] } ```

Pivot tables almost always need composite uniques (`UNIQUE (coach_id, athlete_id)`), and the "exactly one primary per athlete" invariant is naturally a partial unique index. Both have to live in hand-written migrations today. Worth growing the type to:

```ts interface CompositeIndex { name: string columns: string[] unique?: boolean where?: string // partial index — Postgres + SQLite support this } ```

Why this matters

The first time a developer hits a real m2m use case (which is approximately every app past hello-world), they bounce off `belongsToMany` and either fork to the pivot-as-model workaround or pick a different ORM. `withPivot` exists at the SELECT layer but without declarative pivot metadata, mutation methods, or filter predicates, it's incomplete.

Happy to PR if there's directional buy-in on Option A vs B.