kaiunrepo

Review service for Japanese transport companies

Comment

Inspiration

The logistics industry in Japan faces unique challenges: long working hours, driver shortages, and a lack of transparent feedback mechanisms. As someone who has worked in this industry, I noticed that while consumer-facing businesses benefit from review platforms like Google Reviews or TripAdvisor, the B2B logistics sector had no equivalent system for quality assessment.

This gap inspired me to create Kaiunrepo (改運レポ), a specialized review platform where logistics stakeholders—shippers, receivers, and partner companies—can provide structured feedback to transportation companies. The name combines "Kai" (改, improvement) and "Un" (運, transportation), reflecting our mission to improve the industry through honest feedback.

What it does

Understanding the Industry's Needs Before writing a single line of code, I conducted interviews with various stakeholders in the logistics industry. I discovered that:

Companies wanted feedback but feared public criticism Reviewers wanted anonymity but also accountability The industry needed structured, category-specific evaluations rather than free-form comments These insights shaped our approach: creating a semi-private platform with structured reviews and controlled visibility of reports.

Technical Stack Selection For this project, I chose:

React with TypeScript for the frontend Supabase for authentication, database, and storage Stripe for payment processing Vite as the build tool Tailwind CSS for styling This stack offered the perfect balance of development speed, scalability, and maintainability for a hackathon project.

How we built it

Database Schema Design The first challenge was designing a database schema that could handle:

Multiple review categories (customer service, driver performance, partner relations) Anonymous but accountable reviews Point-based access to reports Administrative oversight I created a comprehensive schema with tables for users, companies, reviews, points, reports, and more, with careful attention to relationships and constraints.

Authentication and User Management Implementing secure authentication with email verification was straightforward with Supabase Auth. However, I faced challenges with session persistence across devices. After debugging, I discovered the issue was related to cookie settings and JWT expiration times. Adjusting these parameters in the database migrations solved the problem:

UPDATE auth.users SET raw_app_meta_data = COALESCE(raw_app_meta_data, '{}'::jsonb) || jsonb_build_object( 'jwt_exp_days', 365, 'use_pkce', true, 'session_retention_days', 365, 'cookie_options', jsonb_build_object( 'name', 'sb-auth-token', 'lifetime', 31536000, 'httpOnly', false, 'secure', true ) ); Review System Implementation The review system was designed to be simple yet informative. Instead of free-text reviews that could become toxic, I implemented a star-rating system across nine specific criteria divided into three categories. This approach provides actionable insights while minimizing the risk of harmful content.

Point System and Stripe Integration The biggest challenge came with implementing the point system and Stripe integration. Users need points to view reports, which they can earn through activities or purchase.

The Stripe integration proved particularly challenging. Initially, I encountered issues with the webhook handling:

The webhook would receive events but fail to process them correctly Points weren't being credited to user accounts The transaction status wasn't updating properly After extensive debugging, I discovered the root cause: the payment processing function was expecting amounts in yen, but Stripe was sending amounts in the smallest currency unit (sen, or 1/100 yen). I fixed this with a conversion function:

-- IMPORTANT: Adjust the amount if it's too small v_adjusted_amount := CASE WHEN p_amount < 100 THEN p_amount * 100 -- If amount is suspiciously small, multiply by 100 ELSE p_amount -- Otherwise use as is END; Another challenge was ensuring idempotency in payment processing. I implemented transaction locks and status checks to prevent double-processing:

-- Lock the payment record SELECT * INTO v_payment_record FROM stripe_payments WHERE session_id = p_session_id FOR UPDATE;

-- Check if payment already exists and is completed IF FOUND AND v_payment_record.status = 'completed' THEN RETURN; -- Payment already processed END IF; Edge Functions for Secure API Calls To securely handle Stripe API calls without exposing API keys, I implemented Supabase Edge Functions. This allowed me to create a serverless checkout session creation endpoint and a webhook handler for payment events.

The Edge Functions were deployed directly from the Supabase dashboard, providing a seamless integration with the rest of the application.

Challenges we ran into

Challenge 1: Row Level Security Policies Implementing proper Row Level Security (RLS) policies in Supabase was tricky, especially for admin-only tables. I initially encountered circular dependency issues when checking if a user is an admin:

-- This caused infinite recursion CREATE POLICY "Admin users are viewable by admins" ON admin_users FOR SELECT USING (auth.uid() IN (SELECT user_id FROM admin_users)); I solved this by creating more specific policies:

CREATE POLICY "Users can view their own admin status" ON admin_users FOR SELECT TO authenticated USING (user_id = auth.uid());

CREATE POLICY "Super admins can manage admin users" ON admin_users FOR ALL TO authenticated USING ( EXISTS ( SELECT 1 FROM admin_users a2 WHERE a2.user_id = auth.uid() AND a2.role = 'super_admin' AND a2.user_id != admin_users.user_id -- Prevent recursion ) ); Challenge 2: Real-time Notifications Implementing real-time notifications for system announcements was challenging. I needed to ensure that:

New users would see all relevant announcements Users could mark announcements as read The unread count would update in real-time I solved this by creating a user_announcements junction table and implementing Postgres triggers and Supabase Realtime subscriptions:

CREATE OR REPLACE FUNCTION create_announcement_notifications() RETURNS TRIGGER AS $$ BEGIN INSERT INTO user_announcements (user_id, announcement_id) SELECT DISTINCT u.id, NEW.id FROM auth.users u WHERE u.deleted_at IS NULL AND u.banned_until IS NULL ON CONFLICT (user_id, announcement_id) DO NOTHING;

RETURN NEW; END; $$ LANGUAGE plpgsql; Challenge 3: Account Deactivation Implementing account deactivation while preserving review data required careful consideration. When a user deactivates their account, I needed to:

Prevent future logins Preserve their reviews anonymously Maintain data integrity I solved this with a comprehensive deactivation function:

CREATE OR REPLACE FUNCTION deactivate_user(p_user_id uuid) RETURNS void LANGUAGE plpgsql SECURITY DEFINER SET search_path = public AS $$ BEGIN -- Update auth.users to mark as deactivated UPDATE auth.users SET deactivated = true, raw_app_meta_data = COALESCE(raw_app_meta_data, '{}'::jsonb) || jsonb_build_object('deactivated', true) WHERE id = p_user_id;

-- Revoke all active sessions DELETE FROM auth.sessions WHERE user_id = p_user_id;

-- Convert reviews to anonymous guest reviews UPDATE reviews SET user_id = NULL, ip_address = '0.0.0.0', is_guest_review = true WHERE user_id = p_user_id; END; $$;

Accomplishments that we're proud of

Building Kaiunrepo for this hackathon has been a rewarding journey. The project addresses a real need in the logistics industry and demonstrates how technology can bring transparency and improvement to traditional sectors.

The challenges faced—particularly with Stripe integration and database design—provided valuable learning opportunities. I'm proud of creating a functional, production-ready application that could genuinely help improve the logistics industry in Japan.Finally, I am not an engineer, and I only had some interest and ability. However, my desire to create an app by any means gave birth to my passion for using bolt.new, and I ended up creating a web application of this scale. My greatest achievement was the experience of being able to give shape to my ideas through bolt.new.

What we learned

1.　Database-driven application logic: I learned to leverage PostgreSQL's powerful features for complex business logic, reducing the need for application-level code.

2.　Supabase Edge Functions: These proved invaluable for secure server-side operations without managing a separate backend.

3.　Payment processing complexity: Implementing a payment system requires careful attention to edge cases, idempotency, and error handling.

4.　Row Level Security: Properly designed RLS policies are powerful but require careful planning to avoid circular dependencies.

5.　Realtime features: Supabase's realtime capabilities enabled interactive features like notifications with minimal code.

6.　I can't speak English and only understand Japanese, but thanks to meeting bolt.new, I was able to take on this exciting challenge. I learned that with generative AI, it's possible to overcome language barriers and create global apps.

What's next for kaiunrepo

While the hackathon version of Kaiunrepo is functional, several improvements could enhance the platform:

1.　AI-powered insights: Analyzing review data to provide actionable recommendations to companies.

2.　Enhanced reporting: More detailed analytics and trend visualization for companies.

3.　Mobile app: A dedicated mobile application for on-the-go reviews and reports.

4.　Integration with logistics systems: API connections to TMS (Transportation Management Systems) for seamless data flow.

5.　Expanded verification system: Additional methods to verify the authenticity of reviews.

Built With

Share this project:

Updates