Home

Build a User Management App with Flutter

This tutorial demonstrates how to build a basic user management app. The app authenticates and identifies the user, stores their profile information in the database, and allows the user to log in, update their profile details, and upload a profile photo. The app uses:

  • Supabase Database - a Postgres database for storing your user data and Row Level Security so data is protected and users can only access their own information.
  • Supabase Auth - users log in through magic links sent to their email (without having to set up passwords).
  • Supabase Storage - users can upload a profile photo.

Supabase User Management example

note

If you get stuck while working through this guide, refer to the full example on GitHub.

Project setup#

Before we start building we're going to set up our Database and API. This is as simple as starting a new Project in Supabase and then creating a "schema" inside the database.

Create a project#

  1. Create a new project in the Supabase Dashboard.
  2. Enter your project details.
  3. Wait for the new database to launch.

Set up the database schema#

Now we are going to set up the database schema. We can use the "User Management Starter" quickstart in the SQL Editor, or you can just copy/paste the SQL from below and run it yourself.

  1. Go to the SQL Editor page in the Dashboard.
  2. Click User Management Starter.
  3. Click Run.

Get the API Keys#

Now that you've created some database tables, you are ready to insert data using the auto-generated API. We just need to get the Project URL and anon key from the API settings.

  1. Go to the API Settings page in the Dashboard.
  2. Find your Project URL, anon, and service_role keys on this page.

Building the App#

Let's start building the Flutter app from scratch.

Initialize a Flutter app#

We can use flutter create to initialize an app called supabase_quickstart:


_10
flutter create supabase_quickstart

Then let's install the only additional dependency: supabase_flutter

Copy and paste the following line in your pubspec.yaml to install the package:


_10
supabase_flutter: ^1.10.3

Run flutter pub get to install the dependencies.

Now that we have the dependencies installed let's setup deep links. Setting up deep links is required to bring back the user to the app when they click on the magic link to sign in. We can setup deep links with just a minor tweak on our Flutter application.

We will use io.supabase.flutterquickstart as the scheme, and login-callback as the host for our deep link in this example, but you can change it to whatever you would like.

First, add io.supabase.flutterquickstart://login-callback/ as a new redirect URL in the Dashboard.

Supabase console deep link setting

That is it on Supabase's end and the rest are platform specific settings:

Edit the ios/Runner/Info.plist file.

Add CFBundleURLTypes to enable deep linking:

ios/Runner/Info.plist"

_20
<!-- ... other tags -->
_20
<plist>
_20
<dict>
_20
<!-- ... other tags -->
_20
_20
<!-- Add this array for Deep Links -->
_20
<key>CFBundleURLTypes</key>
_20
<array>
_20
<dict>
_20
<key>CFBundleTypeRole</key>
_20
<string>Editor</string>
_20
<key>CFBundleURLSchemes</key>
_20
<array>
_20
<string>io.supabase.flutterquickstart</string>
_20
</array>
_20
</dict>
_20
</array>
_20
<!-- ... other tags -->
_20
</dict>
_20
</plist>

Main function#

Now that we have deep links ready let's initialize the Supabase client inside our main function with the API credentials that you copied earlier. These variables will be exposed on the app, and that's completely fine since we have Row Level Security enabled on our Database.

lib/main.dart

_10
Future<void> main() async {
_10
await Supabase.initialize(
_10
url: 'YOUR_SUPABASE_URL',
_10
anonKey: 'YOUR_SUPABASE_ANON_KEY',
_10
authFlowType: AuthFlowType.pkce,
_10
);
_10
runApp(MyApp());
_10
}
_10
_10
final supabase = Supabase.instance.client;

AuthFlowType.pkce on authFlowType parameter indicates that we are using a secure PKCE flow to perform our magic link login.

Set up Splash Screen#

Let's create a splash screen that will be shown to users right after they open the app. This screen retrieves the current session and redirects the user accordingly.

lib/pages/splash_page.dart

_38
import 'package:flutter/material.dart';
_38
import 'package:supabase_quickstart/main.dart';
_38
_38
class SplashPage extends StatefulWidget {
_38
const SplashPage({super.key});
_38
_38
@override
_38
_SplashPageState createState() => _SplashPageState();
_38
}
_38
_38
class _SplashPageState extends State<SplashPage> {
_38
@override
_38
void initState() {
_38
super.initState();
_38
_redirect();
_38
}
_38
_38
Future<void> _redirect() async {
_38
await Future.delayed(Duration.zero);
_38
if (!mounted) {
_38
return;
_38
}
_38
_38
final session = supabase.auth.currentSession;
_38
if (session != null) {
_38
Navigator.of(context).pushReplacementNamed('/account');
_38
} else {
_38
Navigator.of(context).pushReplacementNamed('/login');
_38
}
_38
}
_38
_38
@override
_38
Widget build(BuildContext context) {
_38
return const Scaffold(
_38
body: Center(child: CircularProgressIndicator()),
_38
);
_38
}
_38
}

Set up a Login page#

Let's create a Flutter widget to manage logins and sign ups. We'll use Magic Links, so users can sign in with their email without using passwords. Notice that this page sets up a listener on the user's auth state using onAuthStateChange. A new event will fire when the user comes back to the app by clicking their magic link, which this page can catch and redirect the user accordingly.

lib/pages/login_page.dart

_98
import 'dart:async';
_98
_98
import 'package:flutter/foundation.dart';
_98
import 'package:flutter/material.dart';
_98
import 'package:supabase_flutter/supabase_flutter.dart';
_98
import 'package:supabase_quickstart/main.dart';
_98
_98
class LoginPage extends StatefulWidget {
_98
const LoginPage({super.key});
_98
_98
@override
_98
_LoginPageState createState() => _LoginPageState();
_98
}
_98
_98
class _LoginPageState extends State<LoginPage> {
_98
bool _isLoading = false;
_98
bool _redirecting = false;
_98
late final TextEditingController _emailController = TextEditingController();
_98
late final StreamSubscription<AuthState> _authStateSubscription;
_98
_98
Future<void> _signIn() async {
_98
try {
_98
setState(() {
_98
_isLoading = true;
_98
});
_98
await supabase.auth.signInWithOtp(
_98
email: _emailController.text.trim(),
_98
emailRedirectTo:
_98
kIsWeb ? null : 'io.supabase.flutterquickstart://login-callback/',
_98
);
_98
if (mounted) {
_98
ScaffoldMessenger.of(context).showSnackBar(
_98
const SnackBar(content: Text('Check your email for a login link!')),
_98
);
_98
_emailController.clear();
_98
}
_98
} on AuthException catch (error) {
_98
SnackBar(
_98
content: Text(error.message),
_98
backgroundColor: Theme.of(context).colorScheme.error,
_98
);
_98
} catch (error) {
_98
SnackBar(
_98
content: const Text('Unexpected error occurred'),
_98
backgroundColor: Theme.of(context).colorScheme.error,
_98
);
_98
} finally {
_98
if (mounted) {
_98
setState(() {
_98
_isLoading = false;
_98
});
_98
}
_98
}
_98
}
_98
_98
@override
_98
void initState() {
_98
_authStateSubscription = supabase.auth.onAuthStateChange.listen((data) {
_98
if (_redirecting) return;
_98
final session = data.session;
_98
if (session != null) {
_98
_redirecting = true;
_98
Navigator.of(context).pushReplacementNamed('/account');
_98
}
_98
});
_98
super.initState();
_98
}
_98
_98
@override
_98
void dispose() {
_98
_emailController.dispose();
_98
_authStateSubscription.cancel();
_98
super.dispose();
_98
}
_98
_98
@override
_98
Widget build(BuildContext context) {
_98
return Scaffold(
_98
appBar: AppBar(title: const Text('Sign In')),
_98
body: ListView(
_98
padding: const EdgeInsets.symmetric(vertical: 18, horizontal: 12),
_98
children: [
_98
const Text('Sign in via the magic link with your email below'),
_98
const SizedBox(height: 18),
_98
TextFormField(
_98
controller: _emailController,
_98
decoration: const InputDecoration(labelText: 'Email'),
_98
),
_98
const SizedBox(height: 18),
_98
ElevatedButton(
_98
onPressed: _isLoading ? null : _signIn,
_98
child: Text(_isLoading ? 'Loading' : 'Send Magic Link'),
_98
),
_98
],
_98
),
_98
);
_98
}
_98
}

Set up Account page#

After a user is signed in we can allow them to edit their profile details and manage their account. Let's create a new widget called account_page.dart for that.

lib/pages/account_page.dart"

_154
import 'package:flutter/material.dart';
_154
import 'package:supabase_flutter/supabase_flutter.dart';
_154
import 'package:supabase_quickstart/main.dart';
_154
_154
class AccountPage extends StatefulWidget {
_154
const AccountPage({super.key});
_154
_154
@override
_154
_AccountPageState createState() => _AccountPageState();
_154
}
_154
_154
class _AccountPageState extends State<AccountPage> {
_154
final _usernameController = TextEditingController();
_154
final _websiteController = TextEditingController();
_154
_154
var _loading = true;
_154
_154
/// Called once a user id is received within `onAuthenticated()`
_154
Future<void> _getProfile() async {
_154
setState(() {
_154
_loading = true;
_154
});
_154
_154
try {
_154
final userId = supabase.auth.currentUser!.id;
_154
final data = await supabase
_154
.from('profiles')
_154
.select<Map<String, dynamic>>()
_154
.eq('id', userId)
_154
.single();
_154
_usernameController.text = (data['username'] ?? '') as String;
_154
_websiteController.text = (data['website'] ?? '') as String;
_154
} on PostgrestException catch (error) {
_154
SnackBar(
_154
content: Text(error.message),
_154
backgroundColor: Theme.of(context).colorScheme.error,
_154
);
_154
} catch (error) {
_154
SnackBar(
_154
content: const Text('Unexpected error occurred'),
_154
backgroundColor: Theme.of(context).colorScheme.error,
_154
);
_154
} finally {
_154
if (mounted) {
_154
setState(() {
_154
_loading = false;
_154
});
_154
}
_154
}
_154
}
_154
_154
/// Called when user taps `Update` button
_154
Future<void> _updateProfile() async {
_154
setState(() {
_154
_loading = true;
_154
});
_154
final userName = _usernameController.text.trim();
_154
final website = _websiteController.text.trim();
_154
final user = supabase.auth.currentUser;
_154
final updates = {
_154
'id': user!.id,
_154
'username': userName,
_154
'website': website,
_154
'updated_at': DateTime.now().toIso8601String(),
_154
};
_154
try {
_154
await supabase.from('profiles').upsert(updates);
_154
if (mounted) {
_154
const SnackBar(
_154
content: Text('Successfully updated profile!'),
_154
);
_154
}
_154
} on PostgrestException catch (error) {
_154
SnackBar(
_154
content: Text(error.message),
_154
backgroundColor: Theme.of(context).colorScheme.error,
_154
);
_154
} catch (error) {
_154
SnackBar(
_154
content: const Text('Unexpected error occurred'),
_154
backgroundColor: Theme.of(context).colorScheme.error,
_154
);
_154
} finally {
_154
if (mounted) {
_154
setState(() {
_154
_loading = false;
_154
});
_154
}
_154
}
_154
}
_154
_154
Future<void> _signOut() async {
_154
try {
_154
await supabase.auth.signOut();
_154
} on AuthException catch (error) {
_154
SnackBar(
_154
content: Text(error.message),
_154
backgroundColor: Theme.of(context).colorScheme.error,
_154
);
_154
} catch (error) {
_154
SnackBar(
_154
content: const Text('Unexpected error occurred'),
_154
backgroundColor: Theme.of(context).colorScheme.error,
_154
);
_154
} finally {
_154
if (mounted) {
_154
Navigator.of(context).pushReplacementNamed('/login');
_154
}
_154
}
_154
}
_154
_154
@override
_154
void initState() {
_154
super.initState();
_154
_getProfile();
_154
}
_154
_154
@override
_154
void dispose() {
_154
_usernameController.dispose();
_154
_websiteController.dispose();
_154
super.dispose();
_154
}
_154
_154
@override
_154
Widget build(BuildContext context) {
_154
return Scaffold(
_154
appBar: AppBar(title: const Text('Profile')),
_154
body: _loading
_154
? const Center(child: CircularProgressIndicator())
_154
: ListView(
_154
padding: const EdgeInsets.symmetric(vertical: 18, horizontal: 12),
_154
children: [
_154
TextFormField(
_154
controller: _usernameController,
_154
decoration: const InputDecoration(labelText: 'User Name'),
_154
),
_154
const SizedBox(height: 18),
_154
TextFormField(
_154
controller: _websiteController,
_154
decoration: const InputDecoration(labelText: 'Website'),
_154
),
_154
const SizedBox(height: 18),
_154
ElevatedButton(
_154
onPressed: _loading ? null : _updateProfile,
_154
child: Text(_loading ? 'Saving...' : 'Update'),
_154
),
_154
const SizedBox(height: 18),
_154
TextButton(onPressed: _signOut, child: const Text('Sign Out')),
_154
],
_154
),
_154
);
_154
}
_154
}

Launch!#

Now that we have all the components in place, let's update lib/main.dart:

lib/main.dart

_44
import 'package:flutter/material.dart';
_44
import 'package:supabase_flutter/supabase_flutter.dart';
_44
import 'package:supabase_quickstart/pages/account_page.dart';
_44
import 'package:supabase_quickstart/pages/login_page.dart';
_44
import 'package:supabase_quickstart/pages/splash_page.dart';
_44
_44
Future<void> main() async {
_44
await Supabase.initialize(
_44
url: 'YOUR_SUPABASE_URL',
_44
anonKey: 'YOUR_SUPABASE_ANON_KEY',
_44
);
_44
runApp(MyApp());
_44
}
_44
_44
final supabase = Supabase.instance.client;
_44
_44
class MyApp extends StatelessWidget {
_44
@override
_44
Widget build(BuildContext context) {
_44
return MaterialApp(
_44
title: 'Supabase Flutter',
_44
theme: ThemeData.dark().copyWith(
_44
primaryColor: Colors.green,
_44
textButtonTheme: TextButtonThemeData(
_44
style: TextButton.styleFrom(
_44
foregroundColor: Colors.green,
_44
),
_44
),
_44
elevatedButtonTheme: ElevatedButtonThemeData(
_44
style: ElevatedButton.styleFrom(
_44
foregroundColor: Colors.white,
_44
backgroundColor: Colors.green,
_44
),
_44
),
_44
),
_44
initialRoute: '/',
_44
routes: <String, WidgetBuilder>{
_44
'/': (_) => const SplashPage(),
_44
'/login': (_) => const LoginPage(),
_44
'/account': (_) => const AccountPage(),
_44
},
_44
);
_44
}
_44
}

Once that's done, run this in a terminal window to launch on Android or iOS:


_10
flutter run

Or for web, run the following command to launch it on localhost:3000


_10
flutter run -d web-server --web-hostname localhost --web-port 3000

And then open the browser to localhost:3000 and you should see the completed app.

Supabase User Management example

Bonus: Profile photos#

Every Supabase project is configured with Storage for managing large files like photos and videos.

Making sure we have a public bucket#

We will be storing the image as a publicly sharable image. Make sure your avatars bucket is set to public, and if it is not, change the publicity by clicking the dot menu that appears when you hover over the bucket name. You should see an orange Public badge next to your bucket name if your bucket is set to public.

Adding image uploading feature to Account page#

We will use image_picker plugin to select an image from the device.

Add the following line in your pubspec.yaml file to install image_picker:


_10
image_picker: ^0.8.4

Using image_picker requires some additional preparation depending on the platform. Follow the instruction on README.md of image_picker on how to set it up for the platform you are using.

Once you are done with all of the above, it is time to dive into coding.

Create an upload widget#

Let's create an avatar for the user so that they can upload a profile photo. We can start by creating a new component:

lib/components/avatar.dart

_99
import 'package:flutter/material.dart';
_99
import 'package:image_picker/image_picker.dart';
_99
import 'package:supabase_flutter/supabase_flutter.dart';
_99
import 'package:supabase_quickstart/main.dart';
_99
_99
class Avatar extends StatefulWidget {
_99
const Avatar({
_99
super.key,
_99
required this.imageUrl,
_99
required this.onUpload,
_99
});
_99
_99
final String? imageUrl;
_99
final void Function(String) onUpload;
_99
_99
@override
_99
_AvatarState createState() => _AvatarState();
_99
}
_99
_99
class _AvatarState extends State<Avatar> {
_99
bool _isLoading = false;
_99
_99
@override
_99
Widget build(BuildContext context) {
_99
return Column(
_99
children: [
_99
if (widget.imageUrl == null || widget.imageUrl!.isEmpty)
_99
Container(
_99
width: 150,
_99
height: 150,
_99
color: Colors.grey,
_99
child: const Center(
_99
child: Text('No Image'),
_99
),
_99
)
_99
else
_99
Image.network(
_99
widget.imageUrl!,
_99
width: 150,
_99
height: 150,
_99
fit: BoxFit.cover,
_99
),
_99
ElevatedButton(
_99
onPressed: _isLoading ? null : _upload,
_99
child: const Text('Upload'),
_99
),
_99
],
_99
);
_99
}
_99
_99
Future<void> _upload() async {
_99
final picker = ImagePicker();
_99
final imageFile = await picker.pickImage(
_99
source: ImageSource.gallery,
_99
maxWidth: 300,
_99
maxHeight: 300,
_99
);
_99
if (imageFile == null) {
_99
return;
_99
}
_99
setState(() => _isLoading = true);
_99
_99
try {
_99
final bytes = await imageFile.readAsBytes();
_99
final fileExt = imageFile.path.split('.').last;
_99
final fileName = '${DateTime.now().toIso8601String()}.$fileExt';
_99
final filePath = fileName;
_99
await supabase.storage.from('avatars').uploadBinary(
_99
filePath,
_99
bytes,
_99
fileOptions: FileOptions(contentType: imageFile.mimeType),
_99
);
_99
final imageUrlResponse = await supabase.storage
_99
.from('avatars')
_99
.createSignedUrl(filePath, 60 * 60 * 24 * 365 * 10);
_99
widget.onUpload(imageUrlResponse);
_99
} on StorageException catch (error) {
_99
if (mounted) {
_99
ScaffoldMessenger.of(context).showSnackBar(
_99
SnackBar(
_99
content: Text(error.message),
_99
backgroundColor: Theme.of(context).colorScheme.error,
_99
),
_99
);
_99
}
_99
} catch (error) {
_99
if (mounted) {
_99
ScaffoldMessenger.of(context).showSnackBar(
_99
SnackBar(
_99
content: const Text('Unexpected error occurred'),
_99
backgroundColor: Theme.of(context).colorScheme.error,
_99
),
_99
);
_99
}
_99
}
_99
_99
setState(() => _isLoading = false);
_99
}
_99
}

Add the new widget#

And then we can add the widget to the Account page as well as some logic to update the avatar_url whenever the user uploads a new avatar.

lib/pages/account_page.dart

_195
import 'package:flutter/material.dart';
_195
import 'package:supabase_flutter/supabase_flutter.dart';
_195
import 'package:supabase_quickstart/components/avatar.dart';
_195
import 'package:supabase_quickstart/main.dart';
_195
_195
class AccountPage extends StatefulWidget {
_195
const AccountPage({super.key});
_195
_195
@override
_195
_AccountPageState createState() => _AccountPageState();
_195
}
_195
_195
class _AccountPageState extends State<AccountPage> {
_195
final _usernameController = TextEditingController();
_195
final _websiteController = TextEditingController();
_195
_195
String? _avatarUrl;
_195
var _loading = true;
_195
_195
/// Called once a user id is received within `onAuthenticated()`
_195
Future<void> _getProfile() async {
_195
setState(() {
_195
_loading = true;
_195
});
_195
_195
try {
_195
final userId = supabase.auth.currentUser!.id;
_195
final data = await supabase
_195
.from('profiles')
_195
.select<Map<String, dynamic>>()
_195
.eq('id', userId)
_195
.single();
_195
_usernameController.text = (data['username'] ?? '') as String;
_195
_websiteController.text = (data['website'] ?? '') as String;
_195
_avatarUrl = (data['avatar_url'] ?? '') as String;
_195
} on PostgrestException catch (error) {
_195
SnackBar(
_195
content: Text(error.message),
_195
backgroundColor: Theme.of(context).colorScheme.error,
_195
);
_195
} catch (error) {
_195
SnackBar(
_195
content: const Text('Unexpected error occurred'),
_195
backgroundColor: Theme.of(context).colorScheme.error,
_195
);
_195
} finally {
_195
if (mounted) {
_195
setState(() {
_195
_loading = false;
_195
});
_195
}
_195
}
_195
}
_195
_195
/// Called when user taps `Update` button
_195
Future<void> _updateProfile() async {
_195
setState(() {
_195
_loading = true;
_195
});
_195
final userName = _usernameController.text.trim();
_195
final website = _websiteController.text.trim();
_195
final user = supabase.auth.currentUser;
_195
final updates = {
_195
'id': user!.id,
_195
'username': userName,
_195
'website': website,
_195
'updated_at': DateTime.now().toIso8601String(),
_195
};
_195
try {
_195
await supabase.from('profiles').upsert(updates);
_195
if (mounted) {
_195
const SnackBar(
_195
content: Text('Successfully updated profile!'),
_195
);
_195
}
_195
} on PostgrestException catch (error) {
_195
SnackBar(
_195
content: Text(error.message),
_195
backgroundColor: Theme.of(context).colorScheme.error,
_195
);
_195
} catch (error) {
_195
SnackBar(
_195
content: const Text('Unexpected error occurred'),
_195
backgroundColor: Theme.of(context).colorScheme.error,
_195
);
_195
} finally {
_195
if (mounted) {
_195
setState(() {
_195
_loading = false;
_195
});
_195
}
_195
}
_195
}
_195
_195
Future<void> _signOut() async {
_195
try {
_195
await supabase.auth.signOut();
_195
} on AuthException catch (error) {
_195
SnackBar(
_195
content: Text(error.message),
_195
backgroundColor: Theme.of(context).colorScheme.error,
_195
);
_195
} catch (error) {
_195
SnackBar(
_195
content: const Text('Unexpected error occurred'),
_195
backgroundColor: Theme.of(context).colorScheme.error,
_195
);
_195
} finally {
_195
if (mounted) {
_195
Navigator.of(context).pushReplacementNamed('/login');
_195
}
_195
}
_195
}
_195
_195
/// Called when image has been uploaded to Supabase storage from within Avatar widget
_195
Future<void> _onUpload(String imageUrl) async {
_195
try {
_195
final userId = supabase.auth.currentUser!.id;
_195
await supabase.from('profiles').upsert({
_195
'id': userId,
_195
'avatar_url': imageUrl,
_195
});
_195
if (mounted) {
_195
const SnackBar(
_195
content: Text('Updated your profile image!'),
_195
);
_195
}
_195
} on PostgrestException catch (error) {
_195
SnackBar(
_195
content: Text(error.message),
_195
backgroundColor: Theme.of(context).colorScheme.error,
_195
);
_195
} catch (error) {
_195
SnackBar(
_195
content: const Text('Unexpected error occurred'),
_195
backgroundColor: Theme.of(context).colorScheme.error,
_195
);
_195
}
_195
if (!mounted) {
_195
return;
_195
}
_195
_195
setState(() {
_195
_avatarUrl = imageUrl;
_195
});
_195
}
_195
_195
@override
_195
void initState() {
_195
super.initState();
_195
_getProfile();
_195
}
_195
_195
@override
_195
void dispose() {
_195
_usernameController.dispose();
_195
_websiteController.dispose();
_195
super.dispose();
_195
}
_195
_195
@override
_195
Widget build(BuildContext context) {
_195
return Scaffold(
_195
appBar: AppBar(title: const Text('Profile')),
_195
body: _loading
_195
? const Center(child: CircularProgressIndicator())
_195
: ListView(
_195
padding: const EdgeInsets.symmetric(vertical: 18, horizontal: 12),
_195
children: [
_195
Avatar(
_195
imageUrl: _avatarUrl,
_195
onUpload: _onUpload,
_195
),
_195
const SizedBox(height: 18),
_195
TextFormField(
_195
controller: _usernameController,
_195
decoration: const InputDecoration(labelText: 'User Name'),
_195
),
_195
const SizedBox(height: 18),
_195
TextFormField(
_195
controller: _websiteController,
_195
decoration: const InputDecoration(labelText: 'Website'),
_195
),
_195
const SizedBox(height: 18),
_195
ElevatedButton(
_195
onPressed: _loading ? null : _updateProfile,
_195
child: Text(_loading ? 'Saving...' : 'Update'),
_195
),
_195
const SizedBox(height: 18),
_195
TextButton(onPressed: _signOut, child: const Text('Sign Out')),
_195
],
_195
),
_195
);
_195
}
_195
}

Storage management#

If you upload additional profile photos, they'll accumulate in the avatars bucket because of their random names with only the latest being referenced from public.profiles and the older versions getting orphaned.

To automatically remove obsolete storage objects, extend the database triggers. Note that it is not sufficient to delete the objects from the storage.objects table because that would orphan and leak the actual storage objects in the S3 backend. Instead, invoke the storage API within Postgres via the http extension.

Enable the http extension for the extensions schema in the Dashboard. Then, define the following SQL functions in the SQL Editor to delete storage objects via the API:


_34
create or replace function delete_storage_object(bucket text, object text, out status int, out content text)
_34
returns record
_34
language 'plpgsql'
_34
security definer
_34
as $$
_34
declare
_34
project_url text := '<YOURPROJECTURL>';
_34
service_role_key text := '<YOURSERVICEROLEKEY>'; -- full access needed
_34
url text := project_url||'/storage/v1/object/'||bucket||'/'||object;
_34
begin
_34
select
_34
into status, content
_34
result.status::int, result.content::text
_34
FROM extensions.http((
_34
'DELETE',
_34
url,
_34
ARRAY[extensions.http_header('authorization','Bearer '||service_role_key)],
_34
NULL,
_34
NULL)::extensions.http_request) as result;
_34
end;
_34
$$;
_34
_34
create or replace function delete_avatar(avatar_url text, out status int, out content text)
_34
returns record
_34
language 'plpgsql'
_34
security definer
_34
as $$
_34
begin
_34
select
_34
into status, content
_34
result.status, result.content
_34
from public.delete_storage_object('avatars', avatar_url) as result;
_34
end;
_34
$$;

Next, add a trigger that removes any obsolete avatar whenever the profile is updated or deleted:


_29
create or replace function delete_old_avatar()
_29
returns trigger
_29
language 'plpgsql'
_29
security definer
_29
as $$
_29
declare
_29
status int;
_29
content text;
_29
begin
_29
if coalesce(old.avatar_url, '') <> ''
_29
and (tg_op = 'DELETE' or (old.avatar_url <> new.avatar_url)) then
_29
select
_29
into status, content
_29
result.status, result.content
_29
from public.delete_avatar(old.avatar_url) as result;
_29
if status <> 200 then
_29
raise warning 'Could not delete avatar: % %', status, content;
_29
end if;
_29
end if;
_29
if tg_op = 'DELETE' then
_29
return old;
_29
end if;
_29
return new;
_29
end;
_29
$$;
_29
_29
create trigger before_profile_changes
_29
before update of avatar_url or delete on public.profiles
_29
for each row execute function public.delete_old_avatar();

Finally, delete the public.profile row before a user is deleted. If this step is omitted, you won't be able to delete users without first manually deleting their avatar image.


_14
create or replace function delete_old_profile()
_14
returns trigger
_14
language 'plpgsql'
_14
security definer
_14
as $$
_14
begin
_14
delete from public.profiles where id = old.id;
_14
return old;
_14
end;
_14
$$;
_14
_14
create trigger before_delete_user
_14
before delete on auth.users
_14
for each row execute function public.delete_old_profile();

Congratulations, you've built a fully functional user management app using Flutter and Supabase!

See also#