# FILY Project Database Structure

## Project Overview

- **Project:** FILY
- **Framework:** Laravel 12
- **Architecture:** Controller -> Service -> Repository -> Model -> Database
- **Auth:** JWT for Provider and Customer
- **Admin:** Laravel Blade + Session Auth
- **Database:** MySQL
- **Notifications:** FCM + WhatsApp
- **Payment:** Razorpay
- **Logging:** Custom Log Channels
- **Soft Delete:** Enabled
- **Activity Log:** Enabled
- **Account Tables:**
  - Admin uses `users`
  - Provider uses `provider_profiles`
  - Customer uses `customer_profiles`

> Note: Admin, Provider, and Customer accounts are stored in separate tables. The `users` table is for admin users only. Do not use a `role` column for account separation. Use `user_type` wherever a generic table needs to reference admin/provider/customer.

**Reference rule:**

- `admin_id` references `users.id`
- `provider_id` references `provider_profiles.id`
- `customer_id` references `customer_profiles.id`
- Generic logs and notifications use `user_type + user_id`

**JWT policy:** Provider and customer JWT access tokens expire after 30 days. Refresh endpoints are intentionally not exposed. After expiry, the mobile user authenticates again using OTP.

## Table: users

Admin accounts only.

- id
- name
- email
- mobile
- password
- status
- email_verified_at
- mobile_verified_at
- last_login_at
- remember_token
- created_at
- updated_at
- deleted_at

**status:**

- active
- inactive
- blocked

## Table: user_devices

Stores one active login record per account device. The `device_token` column stores the JWT and must use the `TEXT` type because generated JWT values can exceed 255 characters. The `fcm_token` belongs here rather than in `provider_profiles` or `customer_profiles`, because an account may use multiple devices.

- id
- user_type
- user_id
- device_name
- device_type
- platform
- device_token (text, nullable; stores the active JWT token)
- fcm_token
- is_active
- last_used_at
- created_at
- updated_at

**user_type:**

- admin
- provider
- customer

**platform:**

- android
- ios
- web

## Table: otp_verifications

Shared OTP verification records for provider and customer mobile authentication flows. OTP values must be stored as hashes, never as plain text. No foreign key is used for `user_id`, because registration OTPs may be created before a provider or customer profile exists.

- id
- user_type
- user_id
- mobile
- purpose
- otp_hash
- attempts
- expires_at
- verified_at
- consumed_at
- created_at
- updated_at

**user_type:**

- provider
- customer

**purpose:**

- login
- registration
- mobile_verification
- password_reset

## Table: app_versions

Shared mobile app release policy used by provider and customer APIs. Mobile apps send the `X-App-Version` header. A client below `minimum_version` receives HTTP `426 Upgrade Required`.

- id
- platform
- latest_version
- minimum_version
- update_message
- created_at
- updated_at

**platform:**

- all
- android
- ios

## Table: provider_profiles

Provider profiles support OTP-first mobile onboarding. The `name`, `email`, and `password` columns are nullable until the provider completes their profile after OTP login.
The admin provider directory uses server-side pagination, filtering, and sorting. In addition to unique email and mobile indexes, the schema includes indexes for `name`, `business_name`, `created_at`, and the combined `deleted_at + status + created_at` admin query path.

- id
- name
- email
- mobile
- password
- business_name
- contact_number
- whatsapp_number
- city
- state
- address
- profile_photo
- gst_number
- website
- bio
- status
- email_verified_at
- mobile_verified_at
- last_login_at
- remember_token
- created_at
- updated_at
- deleted_at

**status:**

- active
- inactive
- blocked

**profile_photo storage:** Stores a relative public-disk path such as `providers/profile-photos/file.png`. API responses may convert this path to a full public URL.

## Table: customer_profiles

Customer profiles support OTP-first mobile onboarding. The `name`, `email`, and `password` columns are nullable until the customer completes their profile after OTP login.
The admin customer directory uses server-side pagination, filtering, and sorting. In addition to unique email and mobile indexes, the schema includes indexes for `name`, `created_at`, and the combined `deleted_at + status + created_at` admin query path.

- id
- name
- email
- mobile
- password
- city
- state
- address
- profile_photo
- status
- email_verified_at
- mobile_verified_at
- last_login_at
- remember_token
- created_at
- updated_at
- deleted_at

**status:**

- active
- inactive
- blocked

**profile_photo storage:** Stores a relative public-disk path such as `customers/profile-photos/file.png`. API responses may convert this path to a full public URL.

## Table: categories

- id
- name
- slug
- description
- status
- created_at
- updated_at
- deleted_at

**Default Data:**

- Job
- Rent
- Hostel
- Service

## Table: subscription_plans

- id
- category_id
- duration_months
- price
- gst_percentage
- gst_included
- status
- created_at
- updated_at
- deleted_at

## Table: provider_subscriptions

- id
- provider_id
- category_id
- plan_id
- payment_id
- start_date
- end_date
- status
- created_at
- updated_at
- deleted_at

**status:**

- active
- expired
- cancelled

## Table: payments

- id
- provider_id
- plan_id
- amount
- discount_amount
- final_amount
- currency
- razorpay_order_id
- razorpay_payment_id
- razorpay_signature
- payment_method
- status
- paid_at
- created_at
- updated_at

**status:**

- pending
- success
- failed
- refunded

## Table: promo_codes

- id
- code
- discount_type
- discount_value
- max_usage
- used_count
- valid_from
- valid_to
- status
- created_at
- updated_at
- deleted_at

## Table: job_listings

- id
- provider_id
- company_name
- job_title
- job_category
- job_description
- department
- role_category
- employment_type
- experience_required
- education_required
- post_role
- gender_preference
- job_posted_by
- number_of_openings
- monthly_salary
- job_shift
- work_type
- candidate_min_education
- skills_required
- location
- city
- state
- pincode
- status
- admin_note
- views_count
- approved_at
- created_at
- updated_at
- deleted_at

## Table: rent_listings

- id
- provider_id
- property_kind
- property_type
- address
- city
- state
- pincode
- room_details
- willing_to_rent_to
- rent_per_month
- contact_number
- whatsapp_number
- status
- admin_note
- views_count
- approved_at
- created_at
- updated_at
- deleted_at

## Table: hostel_listings

- id
- provider_id
- hostel_type
- owner_name
- contact_number
- address
- city
- area_landmark
- total_rooms
- bed_sharing_type
- total_bed_capacity
- bathroom_type
- monthly_rent
- security_deposit
- electricity_charges
- wifi_available
- food_mess
- drinking_water
- power_backup
- study_room
- cctv
- security_guard
- entry_exit_timing
- visitor_policy
- status
- admin_note
- views_count
- approved_at
- created_at
- updated_at
- deleted_at

## Table: service_listings

- id
- provider_id
- service_type
- experience_years
- service_area
- service_radius
- working_days
- inspection_charge
- starting_price
- hourly_rate
- price_type
- terms_accepted
- status
- admin_note
- views_count
- approved_at
- created_at
- updated_at
- deleted_at

## Table: listing_media

- id
- listing_type
- listing_id
- file_type
- media_label
- file_path
- created_at
- updated_at

**listing_type:**

- job
- rent
- hostel
- service

## Table: saved_listings

- id
- customer_id
- listing_type
- listing_id
- created_at
- updated_at

## Table: reviews

- id
- customer_id
- provider_id
- listing_type
- listing_id
- rating
- review
- status
- created_at
- updated_at
- deleted_at

## Table: notifications

- id
- user_type
- user_id
- title
- message
- notification_type
- reference_type
- reference_id
- is_read
- sent_at
- created_at
- updated_at

**user_type:**

- admin
- provider
- customer

**notification_type:**

- subscription
- listing
- payment
- system

## Table: banners

- id
- title
- image
- redirect_url
- target_app
- status
- created_at
- updated_at
- deleted_at

**target_app:**

- provider
- customer
- both

## Table: moderation_logs

- id
- admin_id
- listing_type
- listing_id
- action
- note
- created_at
- updated_at

**action:**

- approved
- rejected
- deactivated

## Table: activity_logs

- id
- user_type
- user_id
- module
- action
- description
- old_data
- new_data
- ip_address
- user_agent
- created_at

**user_type:**

- admin
- provider
- customer

**Current implementation:** Admin customer/provider create, update, and delete actions write activity log records with module/action, old/new payload snapshots, IP address, and user agent.

## Table: whatsapp_logs

- id
- user_type
- user_id
- mobile
- template_name
- message
- status
- response
- sent_at
- created_at
- updated_at

**user_type:**

- admin
- provider
- customer

## Table: fcm_logs

Stores push-notification delivery history. It is not used for authentication and does not replace `user_devices.fcm_token`.

- id
- user_type
- user_id
- title
- message
- fcm_token
- status
- response
- sent_at
- created_at
- updated_at

**user_type:**

- admin
- provider
- customer

## Table: password_reset_tokens

- email
- token
- created_at

## Table: failed_jobs

- id
- uuid
- connection
- queue
- payload
- exception
- failed_at

## Table: jobs

- id
- queue
- payload
- attempts
- reserved_at
- available_at
- created_at

## Table: sessions

- id
- user_id
- ip_address
- user_agent
- payload
- last_activity

The sessions table is only for admin login because admin uses Laravel Blade + session authentication. Provider and customer authentication uses JWT and does not depend on the sessions table.

## Table: cache

- key
- value
- expiration

## Table: cache_locks

- key
- owner
- expiration

## Total Business Tables

- users
- user_devices
- otp_verifications
- app_versions
- provider_profiles
- customer_profiles
- categories
- subscription_plans
- provider_subscriptions
- payments
- promo_codes
- job_listings
- rent_listings
- hostel_listings
- service_listings
- listing_media
- saved_listings
- reviews
- notifications
- banners
- moderation_logs
- activity_logs
- whatsapp_logs
- fcm_logs

**Total:** 24 Business Tables

## Laravel System Tables

- password_reset_tokens
- failed_jobs
- jobs
- sessions
- cache
- cache_locks

**Total:** 6 System Tables

## Grand Total

- 24 Business Tables
- 6 Laravel Tables

**Total:** 30 Tables

## Schema Evolution Migrations

These follow-up migrations are required on deployed environments:

- `2026_05_30_000021_create_otp_verifications_table.php`
  - Adds shared hashed OTP records for provider and customer authentication.
- `2026_05_30_000022_allow_incomplete_customer_profiles.php`
  - Makes `customer_profiles.name`, `customer_profiles.email`, and `customer_profiles.password` nullable for OTP-first onboarding.
- `2026_05_30_000023_expand_user_devices_device_token.php`
  - Changes `user_devices.device_token` from `VARCHAR(255)` to `TEXT` so JWT tokens can be stored safely.
- `2026_05_30_000024_add_customer_management_indexes.php`
  - Adds indexes used by the server-side admin customer directory.
- `2026_05_30_000025_allow_incomplete_provider_profiles.php`
  - Makes `provider_profiles.name`, `provider_profiles.email`, and `provider_profiles.password` nullable for OTP-first onboarding.
- `2026_05_30_000026_add_provider_management_indexes.php`
  - Adds indexes used by the server-side admin provider directory.
- `2026_05_30_000027_create_app_versions_table.php`
  - Adds the shared provider/customer mobile app release policy table.