Tietokantarakenne

Tätä sivua käytetään kehityksen tarkistamiseen.

Tietokantatesti Kirjautumiseen

users

Field Type Null Key Default Extra
id int(10) unsigned NO PRI auto_increment
email varchar(180) NO UNI
password_hash varchar(255) NO
full_name varchar(180) NO
role enum('super_admin','country_admin','org_owner','org_staff','consumer') NO MUL
country_id int(10) unsigned YES MUL
preferred_language char(2) NO MUL fi
is_active tinyint(1) NO 1
last_login_at datetime YES
created_at timestamp NO current_timestamp()
updated_at timestamp YES on update current_timestamp()

user_organizations

Field Type Null Key Default Extra
id int(10) unsigned NO PRI auto_increment
user_id int(10) unsigned NO MUL
organization_id int(10) unsigned NO MUL
profile_id int(10) unsigned YES MUL
can_manage_profiles tinyint(1) NO 0
can_manage_locations tinyint(1) NO 0
can_manage_lost_items tinyint(1) NO 1
can_manage_users tinyint(1) NO 0
is_active tinyint(1) NO 1
created_at timestamp NO current_timestamp()

organizations

Field Type Null Key Default Extra
id int(10) unsigned NO PRI auto_increment
country_id int(10) unsigned NO MUL
name varchar(180) NO
business_id varchar(80) YES
billing_email varchar(180) YES
phone varchar(80) YES
status enum('pending','active','suspended','closed') NO pending
created_at timestamp NO current_timestamp()
updated_at timestamp YES on update current_timestamp()

organization_subscriptions

Field Type Null Key Default Extra
id int(10) unsigned NO PRI auto_increment
organization_id int(10) unsigned NO MUL
service_package_id int(10) unsigned NO MUL
status enum('trial','active','past_due','cancelled','expired') NO trial
billing_period enum('monthly','yearly','manual') NO manual
starts_at datetime NO
ends_at datetime YES
external_payment_reference varchar(180) YES
created_at timestamp NO current_timestamp()
updated_at timestamp YES on update current_timestamp()

service_packages

Field Type Null Key Default Extra
id int(10) unsigned NO PRI auto_increment
country_id int(10) unsigned YES MUL
name varchar(140) NO
description text YES
monthly_price_cents int(10) unsigned NO 0
yearly_price_cents int(10) unsigned NO 0
currency_code char(3) NO EUR
max_locations int(10) unsigned YES
max_users int(10) unsigned YES
includes_public_search tinyint(1) NO 1
includes_private_link tinyint(1) NO 1
includes_machine_translations tinyint(1) NO 1
includes_archive tinyint(1) NO 1
is_active tinyint(1) NO 1
created_at timestamp NO current_timestamp()
updated_at timestamp YES on update current_timestamp()

profiles

Field Type Null Key Default Extra
id int(10) unsigned NO PRI auto_increment
organization_id int(10) unsigned NO MUL
country_id int(10) unsigned NO MUL
name varchar(180) NO
public_slug varchar(180) NO UNI
logo_path varchar(255) YES
email varchar(180) YES
phone varchar(80) YES
website varchar(255) YES
municipality varchar(140) YES
default_admin_language char(2) NO MUL fi
default_public_language char(2) NO MUL fi
default_publish_scope enum('public','private','both') NO public
pickup_instructions text YES
is_active tinyint(1) NO 1
created_at timestamp NO current_timestamp()
updated_at timestamp YES on update current_timestamp()

locations

Field Type Null Key Default Extra
id int(10) unsigned NO PRI auto_increment
organization_id int(10) unsigned YES MUL
country_id int(10) unsigned NO MUL
name varchar(180) NO MUL
municipality varchar(140) YES
address_line varchar(220) YES
postal_code varchar(40) YES
city varchar(140) YES
latitude decimal(10,7) YES
longitude decimal(10,7) YES
is_active tinyint(1) NO 1
public_board_title varchar(180) YES
public_board_instructions text YES
intranet_board_title varchar(180) YES
intranet_board_instructions text YES
pickup_instructions text YES
contact_email varchar(180) YES
contact_phone varchar(80) YES
logo_path varchar(255) YES
is_public_board_enabled tinyint(1) NO 1
is_intranet_board_enabled tinyint(1) NO 0
created_at timestamp NO current_timestamp()
updated_at timestamp YES on update current_timestamp()

lost_items

Field Type Null Key Default Extra
id bigint(20) unsigned NO PRI auto_increment
public_id varchar(40) NO UNI
organization_id int(10) unsigned NO MUL
profile_id int(10) unsigned YES MUL
location_id int(10) unsigned NO MUL
profile_location_id int(10) unsigned YES MUL
category_id int(10) unsigned NO MUL
created_by_user_id int(10) unsigned NO MUL
source_language char(2) NO MUL
title varchar(180) NO MUL
public_description text YES
internal_marks text YES
found_at datetime NO
public_expires_at datetime NO
publish_scope enum('public','private','both') NO public
status enum('active','returned_to_owner','transferred_to_police','transferred_to_lost_property_office','archived','unclaimed','handover_agreed','returned','archived_unclaimed','archived_handover_agreed','archived_returned','archived_transferred_to_police','archived_transferred_to_lost_property_office') NO unclaimed
status_updated_at datetime YES
show_public_image tinyint(1) NO 1
is_public_visible tinyint(1) NO MUL 1
police_transfer_note text YES
lost_property_office_transfer_note text YES
transferred_at datetime YES
returned_at datetime YES
handover_agreed_at datetime YES
archived_at datetime YES
created_at timestamp NO current_timestamp()
updated_at timestamp YES on update current_timestamp()