Database Schema

The MarsAI platform uses MySQL with Sequelize ORM to manage a comprehensive database schema supporting film submissions, jury voting, events, and user management.

Entity Relationship Overview

Users ──────┬───── Movies ────── Awards
            │         │
            │         ├───── Votes
            │         │
            │         └───── Categories (N:N)
            │         └───── Collaborators (N:N)
            │         └───── Juries (N:N with Users)
            │
            └───── Reservations ───── Events

Votes ────── VoteHistories

Sponsors (independent)
NewsletterSubscribers (independent)

Core Models

User Model

Represents all platform users: producers, jury members, and administrators. File: ~/workspace/source/back/src/models/User.js:1

User Fields

Field	Type	Constraints	Description
`id_user`	INTEGER	PRIMARY KEY, AUTO_INCREMENT	Unique user identifier
`first_name`	STRING(100)	NOT NULL	User’s first name
`last_name`	STRING(100)	NOT NULL	User’s last name
`email`	STRING(255)	NOT NULL, UNIQUE	Login email address
`password`	STRING(255)	NOT NULL	Bcrypt hashed password
`phone`	STRING(20)	NULL	Landline phone number
`mobile`	STRING(20)	NULL	Mobile phone number
`birth_date`	DATE	NULL	Date of birth
`street`	STRING(50)	NULL	Street address
`postal_code`	STRING(10)	NULL	Postal/ZIP code
`city`	STRING(50)	NULL	City
`country`	STRING(50)	NULL	Country
`biography`	STRING(255)	NULL	Professional biography
`job`	ENUM	NULL	ACTOR, DIRECTOR, PRODUCER, WRITER, OTHER
`portfolio`	STRING(50)	NULL	Portfolio URL
`youtube`	STRING(50)	NULL	YouTube channel
`instagram`	STRING(50)	NULL	Instagram handle
`linkedin`	STRING(50)	NULL	LinkedIn profile
`facebook`	STRING(50)	NULL	Facebook profile
`tiktok`	STRING(50)	NULL	TikTok handle
`known_by_mars_ai`	ENUM	NULL	Discovery method
`role`	ENUM	NOT NULL, DEFAULT: ‘PRODUCER’	ADMIN, JURY, PRODUCER
`createdAt`	TIMESTAMP	AUTO	Creation timestamp
`updatedAt`	TIMESTAMP	AUTO	Last update timestamp

User Model Code

export default (sequelize, DataTypes) => {
  const User = sequelize.define('User', {
    id_user: {
      type: DataTypes.INTEGER,
      primaryKey: true,
      autoIncrement: true
    },
    first_name: {
      type: DataTypes.STRING(100),
      allowNull: false
    },
    last_name: {
      type: DataTypes.STRING(100),
      allowNull: false
    },
    email: {
      type: DataTypes.STRING(255),
      allowNull: false,
      unique: true
    },
    password: {
      type: DataTypes.STRING(255),
      allowNull: false  // Bcrypt hashed
    },
    role: {
      type: DataTypes.ENUM('ADMIN', 'JURY', 'PRODUCER'),
      allowNull: false,
      defaultValue: 'PRODUCER'
    },
    // ... other fields
  }, {
    tableName: 'users'
  });

  User.associate = function(models) {
    User.hasMany(models.Movie, { foreignKey: 'id_user' });
    User.hasMany(models.Vote, { foreignKey: 'id_user' });
    User.belongsToMany(models.Movie, {
      as: 'JuryMovies',
      through: 'movies_juries',
      foreignKey: 'id_user',
      otherKey: 'id_movie'
    });
  };

  return User;
};

Associations:

hasMany → Movie (as producer)
hasMany → Vote (as jury member)
belongsToMany → Movie (through movies_juries, as assigned jury)

Movie Model

Represents films submitted to the festival. File: ~/workspace/source/back/src/models/Movie.js:1

Movie Fields

Field	Type	Constraints	Description
`id_movie`	INTEGER	PRIMARY KEY, AUTO_INCREMENT	Unique movie identifier
`title`	STRING	NOT NULL	Film title
`description`	TEXT	NULL	Film description
`duration`	INTEGER	NULL	Duration in minutes
`main_language`	STRING	NULL	Primary language
`release_year`	INTEGER	NULL	Year of release
`nationality`	STRING	NULL	Country of origin
`display_picture`	STRING	NULL	Main poster image
`picture1`	STRING	NULL	Additional image 1
`picture2`	STRING	NULL	Additional image 2
`picture3`	STRING	NULL	Additional image 3
`trailer`	STRING	NULL	Trailer video file
`youtube_link`	STRING(255)	NULL	YouTube video URL
`production`	STRING	NULL	Production company
`workshop`	STRING(255)	NULL	Workshop name
`translation`	STRING(255)	NULL	Translation language
`synopsis`	TEXT	NULL	French synopsis
`synopsis_anglais`	TEXT	NULL	English synopsis
`subtitle`	STRING(255)	NULL	Subtitle language
`ai_tool`	STRING(255)	NULL	AI tool used in production
`thumbnail`	STRING(255)	NULL	Video thumbnail
`admin_comment`	TEXT	NULL	Admin notes
`jury_comment`	TEXT	NULL	Jury candidacy message
`selection_status`	ENUM	NOT NULL, DEFAULT: ‘submitted’	submitted, assigned, to_discuss, candidate, awarded, refused, selected, finalist
`youtube_status`	STRING(255)	NULL	YouTube upload status
`youtube_movie_id`	STRING(255)	NULL	YouTube video ID
`id_user`	INTEGER	NOT NULL, FOREIGN KEY	Producer who submitted
`createdAt`	TIMESTAMP	AUTO	Creation timestamp
`updatedAt`	TIMESTAMP	AUTO	Last update timestamp

Movie Selection Status Flow

The selection_status field tracks a film through the jury process:

submitted: Initial submission state
assigned: Assigned to jury members for first round voting
to_discuss: Moved to second round discussion
candidate: Selected as award candidate
awarded: Film received an award
refused: Rejected by jury
selected/finalist: Legacy statuses

Movie Model Code

export default (sequelize, DataTypes) => {
  const Movie = sequelize.define('Movie', {
    id_movie: {
      type: DataTypes.INTEGER,
      primaryKey: true,
      autoIncrement: true
    },
    title: {
      type: DataTypes.STRING,
      allowNull: false
    },
    selection_status: {
      type: DataTypes.ENUM(
        'submitted',
        'assigned',
        'to_discuss',
        'candidate',
        'awarded',
        'refused',
        'selected',
        'finalist'
      ),
      allowNull: false,
      defaultValue: 'submitted'
    },
    id_user: {
      type: DataTypes.INTEGER,
      allowNull: false
    }
    // ... other fields
  }, {
    tableName: 'movies',
    timestamps: true
  });

  Movie.associate = function(models) {
    Movie.belongsTo(models.User, {
      as: 'Producer',
      foreignKey: 'id_user'
    });
    Movie.hasMany(models.Award, { foreignKey: 'id_movie' });
    Movie.hasMany(models.Vote, { foreignKey: 'id_movie' });
    Movie.belongsToMany(models.Categorie, {
      through: 'movies_categories',
      foreignKey: 'id_movie',
      otherKey: 'id_categorie'
    });
    Movie.belongsToMany(models.Collaborator, {
      through: 'collaborators_movies',
      foreignKey: 'id_movie',
      otherKey: 'id_collaborator'
    });
    Movie.belongsToMany(models.User, {
      as: 'Juries',
      through: 'movies_juries',
      foreignKey: 'id_movie',
      otherKey: 'id_user'
    });
  };

  return Movie;
};

Associations:

belongsTo → User (as Producer)
hasMany → Award
hasMany → Vote
belongsToMany → Categorie (through movies_categories)
belongsToMany → Collaborator (through collaborators_movies)
belongsToMany → User as Juries (through movies_juries)

Vote Model

Jury votes on submitted films. File: ~/workspace/source/back/src/models/Vote.js:1

Vote Fields

Field	Type	Constraints	Description
`id_vote`	INTEGER	PRIMARY KEY, AUTO_INCREMENT	Unique vote identifier
`id_user`	INTEGER	NOT NULL, FOREIGN KEY	Jury member
`id_movie`	INTEGER	NOT NULL, FOREIGN KEY	Film being voted on
`note`	ENUM	NOT NULL	YES, NO, TO DISCUSS
`comments`	TEXT	NULL	Jury comments
`modification_count`	INTEGER	DEFAULT: 0	Number of vote changes
`createdAt`	TIMESTAMP	AUTO	Creation timestamp
`updatedAt`	TIMESTAMP	AUTO	Last update timestamp

export default (sequelize, DataTypes) => {
  const Vote = sequelize.define('Vote', {
    id_vote: {
      type: DataTypes.INTEGER,
      primaryKey: true,
      autoIncrement: true
    },
    note: {
      type: DataTypes.ENUM('YES', 'NO', 'TO DISCUSS'),
      allowNull: false
    },
    modification_count: {
      type: DataTypes.INTEGER,
      defaultValue: 0,
      allowNull: false
    }
  }, { tableName: 'votes', timestamps: true });

  Vote.associate = function(models) {
    Vote.belongsTo(models.User, { foreignKey: 'id_user' });
    Vote.belongsTo(models.Movie, { foreignKey: 'id_movie' });
    Vote.hasMany(models.VoteHistory, {
      foreignKey: 'id_vote',
      as: 'history'
    });
  };

  return Vote;
};

Associations:

belongsTo → User (jury member)
belongsTo → Movie
hasMany → VoteHistory

The modification_count field tracks how many times a jury member changed their vote.

Award Model

Awards given to films. File: ~/workspace/source/back/src/models/Award.js:1

Field	Type	Constraints	Description
`id_award`	INTEGER	PRIMARY KEY, AUTO_INCREMENT	Unique award identifier
`id_movie`	INTEGER	NOT NULL, FOREIGN KEY	Awarded film
`award_name`	STRING(100)	NULL	Name of the award
`createdAt`	TIMESTAMP	AUTO	Creation timestamp
`updatedAt`	TIMESTAMP	AUTO	Last update timestamp

export default (sequelize, DataTypes) => {
  const Award = sequelize.define('Award', {
    id_award: {
      type: DataTypes.INTEGER,
      primaryKey: true,
      autoIncrement: true
    },
    id_movie: {
      type: DataTypes.INTEGER,
      allowNull: false
    },
    award_name: DataTypes.STRING(100)
  }, { tableName: 'awards', timestamps: true });

  Award.associate = function(models) {
    Award.belongsTo(models.Movie, { foreignKey: 'id_movie' });
  };

  return Award;
};

Event Model

Festival events (conferences, workshops, webinars). File: ~/workspace/source/back/src/models/Event.js:1

Field	Type	Constraints	Description
`id_event`	INTEGER	PRIMARY KEY, AUTO_INCREMENT	Unique event identifier
`name`	STRING(255)	NULL	Event name
`description`	TEXT	NULL	Event description
`start_date`	DATE	NULL	Start date/time
`end_date`	DATE	NULL	End date/time
`location`	STRING(255)	NULL	Event location
`event_type`	ENUM	NULL	CONFERENCE, WORKSHOP, MEETUP, WEBINAR
`createdAt`	TIMESTAMP	AUTO	Creation timestamp
`updatedAt`	TIMESTAMP	AUTO	Last update timestamp

export default (sequelize, DataTypes) => {
  const Event = sequelize.define('Event', {
    event_type: DataTypes.ENUM('CONFERENCE', 'WORKSHOP', 'MEETUP', 'WEBINAR'),
    // ... other fields
  }, { tableName: 'events', timestamps: true });

  Event.associate = function(models) {
    Event.hasMany(models.Reservation, { foreignKey: 'id_event' });
  };

  return Event;
};

Associations:

hasMany → Reservation

Reservation Model

Event reservations/registrations. File: ~/workspace/source/back/src/models/Reservation.js:1

Field	Type	Constraints	Description
`id_reservation`	INTEGER	PRIMARY KEY, AUTO_INCREMENT	Unique reservation identifier
`id_event`	INTEGER	NOT NULL, FOREIGN KEY	Event being reserved
`first_name`	STRING(100)	NULL	Attendee first name
`last_name`	STRING(100)	NULL	Attendee last name
`email`	STRING(255)	NULL	Contact email
`number_seats`	INTEGER	NULL	Number of seats reserved
`reservation_date`	DATE	NULL	Reservation timestamp
`createdAt`	TIMESTAMP	AUTO	Creation timestamp
`updatedAt`	TIMESTAMP	AUTO	Last update timestamp

Associations:

belongsTo → Event

Categorie Model

Film categories/genres (many-to-many with Movies). File: ~/workspace/source/back/src/models/Categorie.js:1

Field	Type	Constraints	Description
`id_categorie`	INTEGER	PRIMARY KEY, AUTO_INCREMENT	Unique category identifier
`name`	STRING	NULL	Category name
`createdAt`	TIMESTAMP	AUTO	Creation timestamp
`updatedAt`	TIMESTAMP	AUTO	Last update timestamp

Categorie.associate = function(models) {
  Categorie.belongsToMany(models.Movie, {
    through: 'movies_categories',
    foreignKey: 'id_categorie',
    otherKey: 'id_movie'
  });
};

Join Table: movies_categories

Collaborator Model

Film collaborators/crew members (many-to-many with Movies). File: ~/workspace/source/back/src/models/Collaborator.js:1

Field	Type	Constraints	Description
`id_collaborator`	INTEGER	PRIMARY KEY, AUTO_INCREMENT	Unique collaborator identifier
`first_name`	STRING(150)	NULL	First name
`last_name`	STRING(150)	NULL	Last name
`email`	STRING(255)	NULL	Email address
`job`	STRING(100)	NULL	Role/job title
`createdAt`	TIMESTAMP	AUTO	Creation timestamp
`updatedAt`	TIMESTAMP	AUTO	Last update timestamp

Collaborator.associate = function(models) {
  Collaborator.belongsToMany(models.Movie, {
    through: 'collaborators_movies',
    foreignKey: 'id_collaborator',
    otherKey: 'id_movie'
  });
};

Join Table: collaborators_movies

VoteHistory Model

Tracks changes to votes for audit purposes.

Field	Type	Constraints	Description
`id_vote_history`	INTEGER	PRIMARY KEY, AUTO_INCREMENT	Unique history record
`id_vote`	INTEGER	NOT NULL, FOREIGN KEY	Original vote
`id_user`	INTEGER	NOT NULL	Jury member
`id_movie`	INTEGER	NOT NULL	Film
`note`	FLOAT	NOT NULL	Vote value
`comments`	TEXT	NULL	Comments
`createdAt`	TIMESTAMP	AUTO	When vote was modified

Associations:

belongsTo → Vote

Festival sponsors. File: ~/workspace/source/back/src/models/Sponsor.js:1

Field	Type	Constraints	Description
`id_sponsor`	INTEGER	PRIMARY KEY, AUTO_INCREMENT	Unique sponsor identifier
`name`	STRING(255)	NOT NULL	Sponsor name
`logo`	STRING(255)	NOT NULL	Logo file path
`url`	STRING(255)	NULL	Website URL
`category`	STRING(100)	NULL	Sponsor tier/category

NewsletterSubscriber Model

Newsletter email subscriptions.

Field	Type	Constraints	Description
`id_subscriber`	INTEGER	PRIMARY KEY, AUTO_INCREMENT	Unique subscriber identifier
`email`	STRING(255)	NOT NULL, UNIQUE	Email address
`subscribed_at`	TIMESTAMP	AUTO	Subscription date

Join Tables (Many-to-Many)

movies_categories

Links movies to their categories.

Field	Type	Description
`id_movie`	INTEGER	Foreign key to movies
`id_categorie`	INTEGER	Foreign key to categories

collaborators_movies

Links collaborators to movies they worked on.

Field	Type	Description
`id_collaborator`	INTEGER	Foreign key to collaborators
`id_movie`	INTEGER	Foreign key to movies

movies_juries

Assigns jury members to specific movies for voting.

Field	Type	Description
`id_user`	INTEGER	Foreign key to users (jury)
`id_movie`	INTEGER	Foreign key to movies

This join table enables the platform to assign specific movies to specific jury members rather than all jury members seeing all movies.

Model Loading System

All models are dynamically loaded in ~/workspace/source/back/src/models/index.js:1:

import fs from "fs";
import path from "path";
import { Sequelize } from "sequelize";
import configFile from "../../config/config.cjs";

const env = process.env.NODE_ENV || "development";
const config = configFile[env];
const db = {};

let sequelize = new Sequelize(
  config.database,
  config.username,
  config.password,
  config
);

// Load all model files dynamically
async function loadModels() {
  const files = fs.readdirSync(__dirname)
    .filter(file => file.slice(-3) === ".js" && file !== "index.js");

  for (const file of files) {
    const modelModule = await import(path.join(__dirname, file));
    const model = modelModule.default || modelModule;
    
    if (typeof model === "function") {
      const definedModel = model(sequelize, Sequelize.DataTypes);
      db[definedModel.name] = definedModel;
    }
  }

  // Initialize associations
  Object.keys(db).forEach((modelName) => {
    if (db[modelName].associate) {
      db[modelName].associate(db);
    }
  });
}

await loadModels();

db.sequelize = sequelize;
db.Sequelize = Sequelize;

export default db;

Models are loaded asynchronously using ES modules, and associations are initialized after all models are defined.

Querying with Associations

Example queries using the relationships:

const movie = await db.Movie.findByPk(1, {
  include: [
    { 
      model: db.User, 
      as: 'Producer',
      attributes: ['first_name', 'last_name', 'email']
    }
  ]
});

Database Best Practices

Always use transactions for multi-table operations
Eager load associations to avoid N+1 query problems
Use indexes on foreign keys and frequently queried fields
Validate data at both model and database levels
Use timestamps for audit trails (createdAt, updatedAt)
Soft deletes can be added via paranoid: true if needed

Architecture - System architecture overview
Migrations - Database migration system

Get Started

Core Features

User Guides

Technical

Database Schema

Database Schema

Entity Relationship Overview

Core Models

User Model

Movie Model

Vote Model

Award Model

Event Model

Reservation Model

Categorie Model

Collaborator Model

VoteHistory Model

NewsletterSubscriber Model

Join Tables (Many-to-Many)

movies_categories

collaborators_movies

movies_juries

Model Loading System

Querying with Associations

Database Best Practices

Get Started

Core Features

User Guides

Technical

​Database Schema

​Entity Relationship Overview

​Core Models

​User Model

​Movie Model

​Vote Model

​Award Model

​Event Model

​Reservation Model

​Categorie Model

​Collaborator Model

​VoteHistory Model

​Sponsor Model

​NewsletterSubscriber Model

​Join Tables (Many-to-Many)

​movies_categories

​collaborators_movies

​movies_juries

​Model Loading System

​Querying with Associations

​Database Best Practices

​Related Documentation

Database Schema

Entity Relationship Overview

Core Models

User Model

Movie Model

Vote Model

Award Model

Event Model

Reservation Model

Categorie Model

Collaborator Model

VoteHistory Model

Sponsor Model

NewsletterSubscriber Model

Join Tables (Many-to-Many)

movies_categories

collaborators_movies

movies_juries

Model Loading System

Querying with Associations

Database Best Practices

Related Documentation