# Data Dictionary - Usage Guide

> Generated on 2026-02-25 05:30:47 UTC by `php artisan app:generate-data-dictionary`

## Overview

This data dictionary documents all tables across 3 MySQL databases used by the QuitSure Laravel application. It is auto-generated from the live database schema via `INFORMATION_SCHEMA` queries.

## File Structure

| File | Purpose |
|------|---------|
| `instructions.md` | This file - usage guide and conventions |
| `master_dictionary.md` | All tables overview with row counts, categories, and links |
| `sub_dictionary_tbl_{X}*.md` | Pattern templates for program-specific tables (use `{X}` as program number placeholder) |
| `sub_dictionary_<table>.md` | Detailed column-level documentation for each unique (non-pattern) table |

## Databases

| Connection | Database | Description |
|------------|----------|-------------|
| `mysql` | quitsure (default) | Main application database - programs, users, content, subscriptions |
| `qsuserinfo` | quitsureUsers | Separate database for sensitive user PII (email, phone, etc.) |
| `logs` | logs | API timings, SQL logs, cron logs, partner logs |

> **Note:** The `discourse` connection (PostgreSQL) is excluded as it is a third-party Discourse forum database not managed by this application.

## Column Naming Conventions

The codebase uses Hungarian-style prefixes for column names:

| Prefix | Type | Example |
|--------|------|---------|
| `i` | Integer | `iUserID`, `iProgramID` |
| `v` | Varchar/String | `vName`, `vEmail` |
| `b` | Boolean (tinyint) | `bActive`, `bDeleted` |
| `d` | Datetime | `dDateCreated`, `dLastLogin` |
| `dec` | Decimal | `decPrice`, `decAmount` |
| `t` | Text (long) | `tDescription`, `tContent` |
| `set` | Set/Comma-separated | `setOptions` |

Columns without these prefixes typically follow Laravel conventions (`id`, `created_at`, `updated_at`) or use descriptive names directly.

## Pattern Tables (Program-Specific)

The application manages **10 programs**. Each program has its own set of content and user-tracking tables that share identical structure, differentiated only by the program number in the table name.

- **`tbl_{X}Actions`** - Programs: 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 17
- **`tbl_{X}Chapters`** - Programs: 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17
- **`tbl_{X}Days`** - Programs: 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17
- **`tbl_{X}Exercises`** - Programs: 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17
- **`tbl_{X}Forms`** - Programs: 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17
- **`tbl_{X}Instructions`** - Programs: 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17
- **`tbl_{X}SubModules`** - Programs: 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17
- **`tbl_User{X}Actions`** - Programs: 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 17
- **`tbl_User{X}Activity`** - Programs: 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17
- **`tbl_User{X}Chapters`** - Programs: 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17
- **`tbl_User{X}Days`** - Programs: 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17
- **`tbl_User{X}Exercise`** - Programs: 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17
- **`tbl_User{X}SubmoduleFeedback`** - Programs: 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17


In pattern template files, `{X}` represents the program number (1-10). For example, `tbl_{X}Days` expands to `tbl_1Days`, `tbl_2Days`, ..., `tbl_10Days`.

The template files document the structure using program **1** as the representative example.

## Implicit Foreign Keys

This schema uses very few formal foreign key constraints (only ~4). Most relationships are **implicit** - enforced at the application level via Eloquent relationships. The sub-dictionary files document both:

1. **Formal FKs** - Actual database constraints from `INFORMATION_SCHEMA.KEY_COLUMN_USAGE`
2. **Implicit FKs** - Likely references based on column naming conventions (e.g., `iUserID` → `tbl_Users.iUserID`)

## Regenerating

To regenerate this dictionary after schema changes:

```bash
php artisan app:generate-data-dictionary
```

Output defaults to `data-dictionary/`. Use `--output=custom-path` to change.