Enhancing Twenty for Supabase Compatibility: Custom Database Configurations, Schema Adjustments, and S3 Storage Integration

[rickylabs]

Scope & Context

We aim to integrate Twenty into an existing ecosystem utilizing a Supabase instance that already supports various applications like Medusa, Strapi, and Directus. The integration requires customization of Twenty's database configurations to ensure compatibility with Supabase's infrastructure, including schemas, user permissions, extensions location, and storage options.

Current Behavior and Process

Adjustments in the database setup and Docker configuration were necessary to align Twenty with our self-hosted Supabase instance. The process highlighted the potential for compatibility with some codebase adjustments:

1. Database Migration Adjustments: Created a restricted Twenty user to avoid dropping existing schemas. Manually set up required schemas and utilized pre-installed extensions in Supabase, indicating a need for customizable schema and extension locations.

2. Docker Compose Modifications: Updated the docker-compose.yml to connect Twenty to the external PostgreSQL managed by Supabase, including network and storage configuration adjustments. Removed the built-in db service and tailored environment variables to fit our specific requirements.

3. Supabase Integration Points:

   • Demonstrated manual interventions to create schemas and grant permissions.
   • Highlighted the limitation with Supabase storage integration due to lacking configurations for private bucket access.

Expected Behavior

For full compatibility with ecosystems utilizing Supabase, we propose several enhancements:

1. Customizable Database Configurations: Enabling detailed customization in Twenty for database schemas, user permissions, and the location of database extensions. Users should be able to define target schemas and extension locations to align with their Supabase setup.

2. Bypass Option for Database Reset: Introducing an option to bypass or customize the behavior of the truncate-db.ts script to safeguard shared database instances.

3. Supabase Storage Integration: Support for using Supabase's S3-compatible storage as the default option, including configurations for connecting to private buckets using access keys and secrets.

4. Enhanced Docker Configuration Support: Guidance on customizing docker-compose.yml for external service integration, covering network settings, storage options, and environmental variable adjustments.

Technical Inputs

• Implement a configuration system in Twenty for adjustable database and storage setups required for Supabase integration, including specifying schema and extension locations.

• Develop storage configuration options for Supabase's S3-compatible storage, adding necessary fields for a complete and secure connection.

• Revise and expand the official documentation to cover these integration scenarios comprehensively, providing step-by-step guidance for Docker and database setup modifications for Supabase compatibility.

By addressing these issues and enabling these adjustments, Twenty can significantly broaden its applicability and value for businesses and developers seeking flexible CRM solutions within a Supabase-powered ecosystem.

[rickylabs]

Thank you for considering my feature request. To complement the request and provide further context, I'd like to detail the steps I took in the process of integrating Twenty with my Supabase instance, leading up to the workspace creation form. The purpose is to underscores the necessity for the proposed enhancements and demonstrates the potential for successful integration with some (small) adjustments.

1. Initial Database Connection:

   • Issue: The default Twenty setup expects a local PostgreSQL database as specified in the provided docker-compose.yml. My Supabase instance uses a different setup.
   • Resolution: Modified the docker-compose.yml to connect to my external Supabase-managed PostgreSQL database, including updating environment variables to reflect my database credentials and removing the db service.

2. Database Migration Scripts:

   • Issue: The truncate-db.ts script risked dropping existing schemas in my Supabase database, potentially affecting other integrated applications.
   • Resolution: Created a restricted Twenty user with limited permissions to prevent unintended schema drops. I opted to manually handle schema creation and extension setup to preserve the integrity of my existing database setup.

3. Schema and Extensions Setup:

   • Issue: The setup-db.ts script attempts to create new schemas and requires extensions that were not in the expected "public" schema but in the "extensions" schema of Supabase.
   • Resolution: Manually created the necessary schemas and ensured that Twenty used the existing extensions installed in my Supabase instance. This step required direct database manipulation to align with Twenty's expectations.

4. Workspace Creation:

   • Issue: Upon reaching the workspace creation form, Twenty attempted to create a specific schema for my new workspace. This operation failed due to the restricted permissions of the Twenty user, designed to protect my existing database schemas.
   • Resolution: Manually ran the necessary SQL commands to create the workspace-specific schema and granted the Twenty user the required access. This workaround allowed me to proceed but highlighted the need for more granular control over schema creation within Twenty's setup process.

5. Extension Usage in Workspace Schema Creation:

   • Issue: After manually creating the schema for the new workspace, I encountered an error when Twenty attempted to utilize PostgreSQL extensions, specifically uuid_generate_v4(). The error message was driverError: error: function public.uuid_generate_v4() does not exist. This issue arose because Twenty expects these functions to be in the "public" schema, whereas my Supabase instance has them installed in "extensions" schema, leading to a halt in the process as Twenty couldn't create tables in the new workspace schema.
   • Resolution: This issue remains unresolved and is a critical blocker. The root of the problem is Twenty's hardcoded expectation of extension locations within the "public" schema. A customizable setting in Twenty to specify the schema where it should look for extensions would allow bypassing this issue. Adjusting this setting would enable Twenty to correctly reference the extensions installed in my Supabase instance, facilitating the further integration process and table creation within the workspace-specific schema.

While I managed to navigate these issues to a certain extent, the process showcase the broader need for flexibility in Twenty's database configuration options, specifically around schema management, extension usage, and storage integration. A comprehensive "How-To" guide in Twenty's documentation for integrating with Supabase would be immensely beneficial, providing step-by-step instructions and highlighting customizable settings for a smoother integration process.

[FelixMalfait]

Thanks a lot for providing all this. This is a well documented and very clear issue, would be great if they were all like this 😁

Also a big fan of Supabase and would love to collaborate with them at some point, but I wonder what's your use-case exactly? It feels like there'd be overlap and I'm not what would be the long-term vision to support swapping the DB here. We're not really prepared for the fact that things could happen at the DB layer so I'm not sure it would always play well with a supabase setup in parallel (e.g. if you modify an object through supabase api then our application layer wouldn't catch those events because unlike them we don't get them from Postgres - only from our application layer)

We plan to support Foreign data wrappers soon with the remote objects feature (e.g. ongoing PRs https://github.com/twentyhq/twenty/pulls?q=remote+objects+is%3Apr+is%3Aclosed) I wonder if that could be a better direction for your use-case. You would be able to plug the supabase DB within a wraper and create crm objects for each table

[rickylabs]

Thank you, @FelixMalfait, for your open and constructive feedback. It's great to hear about your interest in Supabase and the ongoing efforts to enhance Twenty's capabilities. Let me address the points you raised and add further context to our use-case and needs:

1. Advantages of Using a DB Provider Like Supabase: Utilizing a service like Supabase offers significant benefits, including managed backups, simplified local development, and branch-specific environments. These features streamline development workflows and ensure data integrity across different stages of the development lifecycle.

2. Separation of the DB Layer: From a security and governance standpoint, separating the database layer from application logic is crucial. This separation also addresses regulatory and compliance requirements, particularly concerning data sovereignty and location-specific regulations.

3. Supabase’s Enhanced Functionality: While Supabase provides a robust set of features beyond basic database hosting, such as Functions, Realtime, and Row-Level Security policies, our focus here is not to leverage these functionalities directly. Instead, we aim to utilize Supabase as a reliable and scalable PostgreSQL provider that aligns with Twenty's database needs.

4. Foreign Data Wrappers and Remote Objects: I've reviewed the ongoing PRs regarding remote objects and appreciate the direction Twenty is taking. This approach aligns with our understanding that there's shared ground in terms of database configuration flexibility between Twenty and Supabase. It reinforces our belief in the feasibility of our integration objectives.

5. Our Specific Use-Case: In Switzerland, where our servers are hosted, we manage not only CRM data but also financial and other sensitive information within our self-hosted Supabase instance. Compliance with local regulations necessitates that this data remain within our control, on-premises. Thus, solutions like foreign data wrappers, which might imply external data management, do not align with our regulatory requirements.

6. Cross-Tool Workflows with Directus: Our setup includes Directus, which acts as a central data layer across different schemas. This allows us to link various tools, creating workflows and gathering insights through unified dashboards and analytics. It highlights the need for a database configuration that's versatile enough to handle complex setups involving multiple schemas.

While some of our needs may be unique to our company, the core requirement to choose our database hosting solution—be it Supabase, Neon Postgres, or any other PostgreSQL provider—is critical. Supabase aligns closely with Twenty's database setup in terms of extensions and functionalities, making it a fitting choice for our environment.

A few crucial modifications are needed for seamless integration: enabling configuration for extension schema locations, facilitating connections to external databases complete with configuration guidance and necessary SQL permissions without requiring superuser status, and providing the ability to skip database truncation. Additionally, integrating Supabase (or more generally any S3 compatible) storage support for secure connections to private buckets is vital. Implementing these updates would enhance Twenty's compatibility with current databases and storage systems, significantly boosting its flexibility.

Your ORM and data management practices are commendable, and I'm convinced that with these small yet impactful enhancements, Twenty can become even more versatile and powerful.

Looking forward to your thoughts and happy to assist in any way I can.