# IC³ Navigation & Systems - ACTUAL Database Schema

## Bridge Table: ic3_system_nav ✓ EXISTS

**Purpose:** Many-to-Many junction between systems and layers

| Column | Type | Purpose |
|--------|------|---------|
| **id** | integer | Primary Key |
| **system_id** | varchar | FK to ic3_system_master |
| **layer_id** | integer | FK to ic3_nav_layer (P1-P6) |
| **status** | varchar | 'active', 'inactive' |
| **created_by** | varchar | Audit trail |
| **created_at** | timestamp | Creation time |
| **updated_at** | timestamp | Last update |

## Navigation Tables

### ic3_nav_layer (Global - 6 Pillars) ✓
- Shared across all systems
- layer_no: 1-999 (extensible)
- Contains P1-P6 + SYS-014 special layers

### ic3_nav_group ✓
- system_id + layer_id combination
- Multiple groups per system-layer pair
- Organizes menu items hierarchically

### ic3_nav_item ✓
- Leaf nodes under groups
- Contains routes, icons, labels
- Status: live, coming_soon, beta

### ic3_nav_role_access ✓
- Role-based access control
- Per menu item
- can_view, can_act flags

## Data Model

```
ic3_system_master (systems: SYS-001, SYS-002, etc.)
         ↓ (system_id)
  ic3_system_nav (Bridge - many-to-many)
         ↑ (layer_id)
  ic3_nav_layer (Global - P1-P6)
         ↓
  ic3_nav_group (Groups per system+layer)
         ↓
  ic3_nav_item (Menu items)
         ↓
  ic3_nav_role_access (Access control)
```

## Verified Structure

All 5 navigation tables exist and are properly connected!

✓ ic3_system_nav - Bridge table confirmed
✓ ic3_nav_layer - Global pillars confirmed  
✓ ic3_nav_group - Menu groups confirmed
✓ ic3_nav_item - Menu items confirmed
✓ ic3_nav_role_access - Role access confirmed