Customer Announcement System

Overview

The Customer Announcement System is a simplified implementation inspired by announcement notification platforms.

The system allows administrators or systems to create announcements and enables customers to:

• View unread announcements
• Mark announcements as read

The project demonstrates database relationship handling using ORM.

Tech Stack

• Backend: PHP (Laravel Framework)
• Database: MySQL 8.4
• Frontend: HTML, CSS, JavaScript, jQuery
• Containerization: Docker
• ORM: Laravel Eloquent

---

Question 4 – Environment Setup

Architecture Overview

The application is containerized using Docker with a multi-service setup:

• PHP 8.2 (FPM) for running Laravel
• NGINX as the web server
• MySQL 8.4 as the database

The services communicate through a dedicated Docker network.

Source code is mounted into the container using Docker volumes, allowing development directly from the host machine.

The database uses a persistent Docker volume to prevent data loss when containers are restarted.

This setup mirrors a production-style architecture, separating the web server, PHP runtime, and database.

Docker was chosen to:

• Avoid local PHP version conflicts
• Ensure reproducible development environment
• Simplify setup on Mac M1 (Apple Silicon)

Setup Instructions

1. Clone the repository

2. Build containers

   run docker compose build in the terminal of the project dir

3. Start containers

   run docker compose up -d in the terminal of the project dir

4. Ensure application key generated and migrations running

   run docker logs -f announcekit_app in the terminal of the project dir Notes: enter ctrl + c to exit the logs

5. Access the application at:

   http://localhost:8000

Notes

• The project was developed and tested on Mac M1.
• All dependencies are installed inside Docker containers.
• No local PHP installation is required.

Question 1 – Announcement Mechanism

For Question 1, the system seeds initial data and establishes the relationship between:

• Announcements
• Customers
• Announcement read status tracking

Database Structure

We will use 3 tables which are:

1. announcements
2. customers
3. announcement_customer (pivot table)

To tracks which customer has read which announcement, we use column field read_at with timestamp datatype

Seed Data

We implement seeder (that can be found in AnnouncementCustomerSeeder.php) to generate data based on the following:

• 5 announcement records are generated
• 2 customer records are generated
• The first announcement is marked as "read" by the first customer

Intructions and code execution

1. Run the migration and seeding: php artisan migrate --seed

2. Verify the data by querying in the database based on the following step:

   1. Run docker cmd in project dir: docker exec -it announcekit_db mysql -u sysadmin -p syadmin
   2. Run the query to proceed with intended db schema: use announcekit;
   3. Run the following sql query:
      • select * from customers;
      • select * from announcements;
      • select * from announcement_customer;

Question 2 – Fetch Unread Announcements

Planning:

1. Need to query list of announcement. Announcement is considered unread if:

   • No pivot record exists for the customer, OR
   • A pivot record exists but read_at is NULL

2. Announcements that have read_at NOT NULL for the customer are considered read and are excluded

3. This codes are implemented using Laravel Eloquent ORM and basic HTML and Laravel Blade for frontend.

Generated SQL Query

The following SQL was generated by Eloquent (retrieved using DB::enableQueryLog()):

select * from `announcements` where not exists (select * from `customers` inner join `announcement_customer` on `customers`.`id` = `announcement_customer`.`customer_id` where `announcements`.`id` = `announcement_customer`.`announcement_id` and `customers`.`id` = ? and `announcement_customer`.`read_at` is not null) order by `created_at` desc

How to Test

Navigate to http://localhost:8000/customers/1/unread-announcements

This will display all unread announcements for Customer 1

Question 3 – Mark Announcement as Read

Implementation

Objective : To provide a mechanism to mark an announcement as "read" for a specific customer. Details:

1. Added validation layer : Announcement ID and Customer ID need to be exist in their respective table. (Used Laravel Form Request)
2. Modify controller logic : The logic is to check whether the relationship record already exist in pivot table.

How to Test

1. Navigate to http://localhost:8000/mark-as-read
2. Enter both Announcement ID and Customer ID
3. Click "Mark as Read" button

A success message displayed result to successful submission

---

AI usage disclosure

The following AI tools were used during development:

1. ChatGPT was used to assist in:

   • Git workflow structure suggestion
   • Query optimization idea suggestions
   • README documentation drafting

2. Windsurf AI was used as a code editor assistant for:

   • Autocomplete suggestions
   • Template scaffolding for controller and HTML structure
   • Development productivity support

Important clarification:

• No AI-generated code was blindly copied without understanding.
• All controller logic, queries, and frontend implementations were reviewed, adjusted, and tested manually.

Total time spend

Approximately: ~ 6 hours ( with multiple breaks )