[Integration] Supabase and Drizzle ORM Integration

[Bran18]

Issue: Supabase and Drizzle ORM Integration for Escrow State Sync

Difficulty: Intermediate
Estimated Time: 6-8 hours
Documentation:

• Drizzle ORM: https://orm.drizzle.team/docs
• Supabase Docs: https://supabase.com/docs

Goal

Integrate Supabase and Drizzle ORM to store and retrieve escrow-related data such as engagements, milestones, approvals, and user metadata. Build reusable hooks to enable interaction with the database using a modular and maintainable approach.

Expected Outcome

• Schemas for escrows, users, milestones, statuses, and approvals.
• Custom hooks to create, update, delete, and fetch data from Supabase.
• Integration with frontend components.

Tasks

1. Set Up Drizzle + Supabase

   • Connect Drizzle ORM to Supabase using the proper configuration.

   • Create schema definitions for the following tables:

     • escrows
     • milestones
     • approvals
     • users

2. Create Custom Hooks

   • Create the following hooks under /apps/webapp/hooks/escrow/:

     • useCreateEscrow
     • useUpdateEscrow
     • useDeleteEscrow
     • useGetEscrows
     • useAddMilestone
     • useApproveMilestone

3. Organize Services

   • Place database access logic under /services/supabase/
   • Follow RORO (Receive an object, return an object) pattern

4. Real-Time Updates

   • Use Supabase real-time subscriptions or implement polling to update UI on status changes

5. Secure Access

   • Ensure data is scoped per user using Supabase auth
   • Protect routes and hook logic from unauthorized access

6. Write Seed Script

   • Add a utility to populate the database with sample escrows and milestone records

7. Testing

   • Add tests to verify custom hooks perform the correct DB actions
   • Simulate end-to-end usage of creating and updating escrow records

[Oluwatos94]

Hi @Bran18 ,

May I take on this task? I would like to implement this.

Looking forward to be assigned. Thanks!

[Her-Code]

I have experience in building ecommerce sites and working with languages like JavaScript and Typescript

[kabugatti]

Hey there! I'd love to take on this issue.

I have hands-on experience with both Supabase and Drizzle ORM and I'm excited by the chance to integrate them for this escrow feature.

My plan is to start by setting up the Drizzle config and defining the schemas for escrows, milestones, and users. From there, I'll build the custom hooks (useCreateEscrow, etc.) and the corresponding service layer logic, ensuring data access is secure with RLS. I'll use Supabase's real-time subscriptions for UI updates and will write a seed script and tests for the hooks to ensure everything is robust.

This project aligns perfectly with my skills and my passion for contributing to open source. I'm confident I can deliver a clean, maintainable solution.

Let me know if you'd like me to get started!

Recommended by OnlyDust for outstanding experience and quality contributions.

[salazarsebas]

Hey @kabugatti , what are the updates on this issue?

[kabugatti]

Hi @salazarsebas , I was setting up yesterday, and when I tried to retrieve the API key from trustless work, I was told I had to have some funds in my freight wallet to use the API key. Is there a demo or test API key I can use instead of my own?

[salazarsebas]

Hi @salazarsebas , I was setting up yesterday, and when I tried to retrieve the API key from trustless work, I was told I had to have some funds in my freight wallet to use the API key. Is there a demo or test API key I can use instead of my own?

If you use your account to test. Be sure to test with this link which is currently functional:

demo.dev.trustlesswork.com

[kabugatti]

Hi @salazarsebas , I was setting up yesterday, and when I tried to retrieve the API key from trustless work, I was told I had to have some funds in my freight wallet to use the API key. Is there a demo or test API key I can use instead of my own?

If you use your account to test. Be sure to test with this link which is currently functional:

demo.dev.trustlesswork.com

I clicked the link, but I'm told I have to have access to it, to open it. Looks like only the creator of the project or the team, has access to it.

[kabugatti]

Hi @salazarsebas @Bran18,

Still experiencing this issue. Any updates or direction on how to proceed?