# Nexus Autoparts — SaaS + Aftermarket Design

**Date:** 2026-03-15
**Status:** Approved

## Overview

Two features for Nexus Autoparts:
1. **SaaS user system** — auth, roles (ADMIN, OWNER, TALLER, BODEGA), warehouse inventory uploads with flexible column mapping, availability/pricing visible to authenticated talleres.
2. **Aftermarket parts cleanup** — migrate 357K AFT- prefixed parts from `parts` table into `aftermarket_parts`, link to OEM parts, fix import pipeline.

Architecture: **Monolith approach** — everything in the existing PostgreSQL DB and Flask backend.

---

## Section 1: Authentication & Users

### Tables

**`roles`** (existing, update names):

| id_rol | name_rol |
|--------|----------|
| 1 | ADMIN |
| 2 | OWNER |
| 3 | TALLER |
| 4 | BODEGA |

**`users`** (existing, extend):
```
id_user (PK)
name_user (VARCHAR 200)
email (VARCHAR 200, UNIQUE)
pass (VARCHAR 200, bcrypt hash)
id_rol (FK → roles)
business_name (VARCHAR 200)
phone (VARCHAR 50)
address (TEXT)
is_active (BOOLEAN DEFAULT false)
created_at (TIMESTAMP DEFAULT now())
last_login (TIMESTAMP)
```

**`sessions`** (new):
```
id_session (PK)
user_id (FK → users)
refresh_token (VARCHAR 500, UNIQUE)
expires_at (TIMESTAMP)
created_at (TIMESTAMP DEFAULT now())
```

### Auth Flow
- Login: `POST /api/auth/login` → JWT access token (15 min) + refresh token (30 days)
- Refresh: `POST /api/auth/refresh` → new access token
- JWT payload: `user_id`, `role`, `business_name`
- Public endpoints: catalog, landing, search, aftermarket list
- Protected endpoints: pricing, inventory, admin, POS
- Flask middleware validates JWT on protected routes

### Registration
- Bodegas and talleres register via form
- ADMIN approves accounts (is_active = false by default)
- ADMIN can create accounts directly

---

## Section 2: Warehouse Inventory

### Tables

**`warehouse_inventory`** (new):
```
id_inventory (PK, BIGINT)
user_id (FK → users)
part_id (FK → parts)
price (NUMERIC 12,2)
stock_quantity (INTEGER)
min_order_quantity (INTEGER DEFAULT 1)
warehouse_location (VARCHAR 100)
updated_at (TIMESTAMP DEFAULT now())
UNIQUE (user_id, part_id, warehouse_location)
```

**`inventory_uploads`** (new):
```
id_upload (PK)
user_id (FK → users)
filename (VARCHAR 200)
status (VARCHAR 20: pending, processing, completed, failed)
rows_total (INTEGER)
rows_imported (INTEGER)
rows_errors (INTEGER)
error_log (TEXT)
created_at (TIMESTAMP DEFAULT now())
completed_at (TIMESTAMP)
```

**`inventory_column_mappings`** (new):
```
id_mapping (PK)
user_id (FK → users, UNIQUE)
mapping (JSONB)
```

Example JSONB mapping:
```json
{
  "part_number": "COLUMNA_A",
  "price": "PRECIO_VENTA",
  "stock": "EXISTENCIAS",
  "location": "SUCURSAL"
}
```

### Upload Flow
1. Bodega uploads CSV/Excel → `POST /api/inventory/upload`
2. Backend reads file, applies JSONB mapping for that bodega
3. Matches part numbers against `parts.oem_part_number`
4. UPSERT into `warehouse_inventory`
5. Records result in `inventory_uploads`

### Catalog Display (authenticated TALLER)
```
Disponibilidad en bodegas:
  BODEGA CENTRAL MX     | $450.00 | 12 en stock | Guadalajara
  REFACCIONES DEL NORTE  | $485.00 | 3 en stock  | Monterrey
```
- Only authenticated talleres see prices and stock
- Public users see catalog without prices

---

## Section 3: Aftermarket Parts Migration

### Current Problem
- 357,360 parts with `AFT-` prefix in `parts` table treated as OEM
- Format: `AFT-{articleNo}-{supplierName}` (e.g., `AFT-AC191-PARTQUIP`)
- Description: `"Aftermarket PARTQUIP"`
- Have vehicle_parts linked
- `aftermarket_parts` table exists but has only 1 record

### Migration Steps

**Step 1: Parse AFT- prefix**
```
AFT-AC191-PARTQUIP → part_number: AC191, manufacturer: PARTQUIP
AFT-10-0058-Airstal → part_number: 10-0058, manufacturer: Airstal
```
Logic: last segment after last `-` that matches a `manufacturers.name_manufacture` is the manufacturer. The rest (without `AFT-`) is the part number.

**Step 2: Find corresponding OEM part**
- Search `part_cross_references` where `cross_reference_number` = articleNo and `source_ref` = supplierName
- That gives us the `part_id` of the real OEM part
- If no cross-reference, search via `vehicle_parts` — OEM parts linked to same vehicles in same category

**Step 3: Populate `aftermarket_parts`**
```
oem_part_id → the OEM part found
manufacturer_id → FK to manufacturer (PARTQUIP, Airstal, etc.)
part_number → AC191 (clean, no prefix)
name_aftermarket_parts → original name_part
```

**Step 4: Migrate vehicle_parts**
- vehicle_parts pointing to AFT- part get re-linked to the real OEM part
- Or deleted if OEM already has that link (ON CONFLICT DO NOTHING)

**Step 5: Delete AFT- parts from `parts`**
- Once migrated to `aftermarket_parts` and re-linked, remove from `parts`

### Import Pipeline Changes
- `import_live.py` and `import_tecdoc_parts.py` stop creating `AFT-` parts in `parts`
- Instead insert directly into `aftermarket_parts` with clean manufacturer and part number
- `vehicle_parts` only link to real OEM parts

### Catalog Display
```
Alternativas aftermarket:
  PARTQUIP AC191         | Ver disponibilidad →
  BOSCH    0 986 AB2 854 | Ver disponibilidad →
  KAWE     6497 10       | Ver disponibilidad →
```

---

## Section 4: API Endpoints & Pages

### New Endpoints

**Auth:**
| Method | Route | Access | Description |
|--------|-------|--------|-------------|
| POST | `/api/auth/register` | Public | Register taller/bodega |
| POST | `/api/auth/login` | Public | Login → JWT + refresh |
| POST | `/api/auth/refresh` | Authenticated | Renew access token |
| GET | `/api/auth/me` | Authenticated | User profile |

**Inventory (BODEGA):**
| Method | Route | Access | Description |
|--------|-------|--------|-------------|
| POST | `/api/inventory/upload` | BODEGA | Upload CSV/Excel |
| GET | `/api/inventory/uploads` | BODEGA | Upload history |
| GET | `/api/inventory/mapping` | BODEGA | View column mapping |
| PUT | `/api/inventory/mapping` | BODEGA | Configure mapping |
| GET | `/api/inventory/items` | BODEGA | View own inventory |
| DELETE | `/api/inventory/items` | BODEGA | Clear inventory |

**Availability (TALLER):**
| Method | Route | Access | Description |
|--------|-------|--------|-------------|
| GET | `/api/parts/{id}/availability` | TALLER | Prices/stock from all bodegas |
| GET | `/api/parts/{id}/aftermarket` | Public | Aftermarket alternatives list |

**Admin:**
| Method | Route | Access | Description |
|--------|-------|--------|-------------|
| GET | `/api/admin/users` | ADMIN | List users |
| PUT | `/api/admin/users/{id}/activate` | ADMIN | Approve/deactivate account |

### New Pages

**`login.html`** — Login/registration form. Redirects by role after login.

**`bodega.html`** — Warehouse panel:
- Configure column mapping
- Upload CSV/Excel
- View upload history with status
- View current inventory with search

### Modified Pages
- **`index.html` / `demo.html`** — Add "Disponibilidad en bodegas" section in part detail (TALLER only). Add "Alternativas aftermarket" section (public).
- **`admin.html`** — Add "Usuarios" tab for account management.
- **`nav.js`** — Add login/logout button, show username.

### Auth Middleware
```
Public:        catalog, search, landing, login, aftermarket list
TALLER:        prices, availability, history
BODEGA:        upload, mapping, own inventory
ADMIN/OWNER:   all above + user management + admin panel
```