Space table

The Space table represents a collaborative environment where users can manage their assets and share access with other users. Each space is associated with a profile and can have multiple members.

Database schema

create table public.space (
  id uuid not null default gen_random_uuid (),
  created_at timestamp with time zone not null default now(),
  updated_at timestamp with time zone null,
  deleted_at timestamp with time zone null,
  profile_id uuid null,
  preferred_currency text not null,
  group_id uuid null
)

Key fields

• id: Unique identifier for the space (UUID)
• profile_id: Reference to the profile that owns the space
• preferred_currency: Three-letter currency code (e.g., 'EUR', 'USD')
• group_id: Reference to the group managing access control

Constraints

• space_pkey: Primary key on id
• space_group_id_key: Unique constraint on group_id
• space_currency_check: Ensures currency code is exactly 3 characters
• fk_space_group: Foreign key to group table
• space_profile_id_fkey: Foreign key to profile table

Timestamps

• created_at: Automatically set on record creation
• updated_at: Automatically updated via trigger
• deleted_at: Soft deletion timestamp

Database triggers

1. before_insert_space_create_group: Automatically creates a group for new spaces
2. handle_updated_at: Updates the updated_at timestamp on record modification

TypeScript interfaces

Base Space Interface

interface Space extends Entity {
  profileId: string;
  preferredCurrency: Currency;
  groupId: string;
}

User space interface

interface UserSpace extends Space {
  firstName: string;
  lastName: string;
  avatar: string;
}

Space member interface

interface SpaceMember extends Profile {
  role: SpaceMemberRole;
  spaceMemberStatus: SpaceMemberStatus;
  groupInviteId: string;
}

Member management

Roles

enum SpaceMemberRole {
  Owner = "owner",
  Admin = "admin",
  Member = "member",
}

Status

enum SpaceMemberStatus {
  Active = "active",
  Invited = "invited",
  Declined = "declined",
}

Space member view

The space_member view combines information from:

• group_user
• group_invite
• profile
• space

It provides a unified view of all members and invitees in a space, including their:

• Membership status
• Role
• Personal information
• Space relationship

Key operations

Queries

1. getSpaceByProfileId(profileId): Retrieves space by profile ID
2. getSpaceById(spaceId): Retrieves space by ID
3. getSpaceMembers(spaceId): Lists all members of a space
4. getUserSpaces(): Lists all spaces available to the current user

Mutations

1. updateSpacePreferredCurrency({spaceId, preferredCurrency}): Updates space currency
2. removeSpaceMember(spaceGroupId, userId, spaceOwnerUserId): Removes a member from space

Automatic space creation

When a new profile is created, a space is automatically created with:

• Default currency: 'EUR'
• New group for access control
• Owner role assigned to the profile

Relationships

• Many-to-One with profile through profile_id
• One-to-One with group through group_id
• Many-to-Many with users through the space_member view