Table descriptions

`Plants`

*Column name*	*Data type*	*Example*	*Additional information*
id	integer	355	PRIMARY KEY
unique_name	text	Allium schoenoprasum	NOT NULL
common_name_en	text[]	{chives}
common_name_de	text[]	{schnittlauch}
family	text	Alliaceae
edible_uses_en	text	NULL
functions	text	NULL
heat_zone	smallint	NULL
shade	shade	light shade
soil_ph	soil_ph[]	{acid}
soil_texture	soil_texture[]	{sandy,loamy,clay}
ecosystem_niche	text	NULL
deciduous_or_evergreen	deciduous_or_evergreen	NULL
herbaceous_or_woody	herbaceous_or_woody	herbaceous
life_cycle	life_cycle[]	{perennial}
growth_rate	growth_rate[]	{moderate}
height	integer	30
fertility	fertility[]	{"self fertile"}
created_at	timestamp without time zone	2024-05-31 12:03:49.820927	NOT NULL, defaults to current time
updated_at	timestamp without time zone	2024-05-31 12:03:49.820927	NOT NULL, defaults to current time
has_drought_tolerance	boolean	false
tolerates_wind	boolean	NULL
preferable_permaculture_zone	smallint	NULL	-1..6 (-1 should be printed as 00)
hardiness_zone	text	[3,12)
light_requirement	light_requirement[]	{"full sun","partial sun/shade"}
water_requirement	water_requirement[]	{moist}
propagation_method	propagation_method[]	{"seed - direct sow","seed - transplant",division}
alternate_name	text	Wild chives, Flowering onion
edible	boolean	true
edible_parts	text[]	{bulb,flowers,leaves,root}
spread	integer	30
warning	text	NULL
version	smallint	24
external_source	external_source	permapeople
sowing_outdoors	smallint[]	NULL
harvest_time	smallint[]	NULL
seed_weight_1000	double precision	NULL

`Spatial Relations`

Store relations between plants.

*Column name*	*Data type*	*Example*	*Additional information*
plant1	integer	355	PRIMARY KEY, FOREIGN KEY to `plants.id`, id of first plant
plant2	integer	1920	PRIMARY KEY, FOREIGN KEY to `plants.id`, id of second plant
relation	relation_type	companion	NOT NULL, type of relation
note	text	Imported via scraper	comment about the relation

`Temporal Relation`

Store possible temporal relations between plants, which make up the crop rotation.

*Column name*	*Data type*	*Example*	*Additional information*
plant_earlier	integer	355	PRIMARY KEY, FOREIGN KEY to `plants.id`, id of earlier plant
plant_later	integer	1920	PRIMARY KEY, FOREIGN KEY to `plants.id`, id of later plant
min_duration	integer	6	the absolute minimum duration of the effect in months (min. 6)
max_duration	integer	12	the absolute maximum duration of the effect in months
effect	temporal_relation_effect	positive	`positive` or `negative` effect between two plants

`Maps`

*Column name*	*Data type*	*Example*	*Additional information*
id	integer	1	PRIMARY KEY
name	text	My Map	NOT NULL, only alphanumerical values
deletion_date	date	2024-04-04
last_visit	date	2023-04-04
is_inactive	boolean	false	NOT NULL
zoom_factor	smallint	100	NOT NULL, value used in formula "X by X cm", e.g. 100 would mean "100 x 100 cm", range from 10 to 100000
honors	smallint	0	NOT NULL, 0 to infinity
visits	smallint	0	NOT NULL, 0 to infinity
harvested	smallint	0	NOT NULL, 0 to infinity, amount of plants harvested on this map
privacy	privacy_option	protected	NOT NULL
description	text	Our first map
location	geography	NULL	PostGIS Geodata, location of the map
geometry	geometry	0103000020E61000000100...	NOT NULL, boundaries of the map
created_by	uuid	affb3987-0d75-42b8-b34a-cf7eec4e151b	NOT NULL, is also the map owner
creation_at	timestamp	2023-04-04 10:11:12	NOT NULL, defaults to current time
modified_by	uuid	affb3987-0d75-42b8-b34a-cf7eec4e151b	NOT NULL
modified_at	timestamp	2023-04-04 10:11:12	NOT NULL, defaults to current time

`Seeds`

*Column name*	*Data type*	*Example*	*Additional information*
id	integer	1	PRIMARY KEY
name	text	Some tomato seeds	NOT NULL, at least 1 alphanumeric character, no dashes
harvest_year	smallint	2018	NOT NULL
use_by	date	2028
origin	text	Vienna, Austria
taste	text	Sweet
yield	text	high
quantity	quantity	more than enough	NOT NULL
quality	quality	Organic
price	smallint	10.99
generation	smallint	7
notes	text	NULL
plant_id	integer	5557	NOT NULL, FOREIGN KEY to `plants.id`
created_by	uuid	affb3987-0d75-42b8-b34a-cf7eec4e151b	NOT NULL
archived_at	timestamp	NULL

`Layers`

*Column name*	*Data type*	*Example*	*Additional information*
id	uuid	9b77d9b1-02c3-4fac-97bf-686b9f4ea38d	PRIMARY KEY
map_id	integer	1	NOT NULL, FOREIGN KEY to `maps.id`
type	layer_type	plants	NOT NULL
name	text	Plants Layer	NOT NULL
is_alternative	boolean	false	NOT NULL, true if the layer is an user-created alternative layer
mark_deleted	timestamp	NULL
order_index	integer	1	NOT NULL

`Plantings`

A single plant or an area of plants that has been placed on a map.

*Column name*	*Data type*	*Example*	*Additional information*
id	uuid	440d518c-dc50-4ff3-9943-9f578a1b7e7d	PRIMARY KEY
plant_id	integer	355	NOT NULL, FOREIGN KEY to `plants.id`
is_area	boolean	false	NOT NULL, true if the planting is an area of plants instead of a single plant
x	integer	714	NOT NULL, x coordinate on map
y	integer	538	NOT NULL, y coordinate on map
size_x	integer	30	NOT NULL, size on x-axis on map
size_y	integer	30	NOT NULL, size on y-axis on map
rotation	real	0	NOT NULL, rotation on map
add_date	date	2024-03-01	Date when the plant was planted
remove_date	date	NULL	Date when the plant was removed
seed_id	integer	NULL	FOREIGN KEY to `seeds.id`
notes	text	NULL	NOT NULL
created_at	timestamp	2024-01-01 10:11:12	NOT NULL, defaults to current time
modified_at	timestamp	2024-01-01 10:11:12	NOT NULL, defaults to current time
created_by	uuid	affb3987-0d75-42b8-b34a-cf7eec4e151b	NOT NULL
modified_by	uuid	affb3987-0d75-42b8-b34a-cf7eec4e151b	NOT NULL
layer_id	uuid	b8e3ea1c-5432-459b-a9c9-736a29ea60f0	NOT NULL, FOREIGN KEY to `layer.id`

`Users`

*Column name*	*Data type*	*Example*	*Additional information*
id	uuid	00000000-0000-0000-0000-000000000000	NOT NULL, PRIMARY KEY, the UUID from Keycloak
salutation	salutation	Mr	NOT NULL
title	text	NULL
country	text	Austria	NOT NULL
phone	text	NULL
website	text	NULL
organization	text	NULL
experience	experience	beginner
membership	membership	contributing
member_years	integer[]	{2023}	years of active membership
member_since	date	2023-07-12
permacoins	integer[]	{0}	array of positive integers

`Guided Tours`

*Column name*	*Data type*	*Example*	*Additional information*
user	uuid	00000000-0000-0000-0000-000000000000	PRIMARY KEY
editor_tour_completed	boolean	false	has the user completed the Map Editor Tour

`Blossoms`

*Column name*	*Data type*	*Example*	*Additional information*
title	text	Novice Gardener	PRIMARY KEY
description	text	Plant your first plant
track	track	Beginners Track	the track (category) this blossom belongs to
icon	text	NULL
is_seasonal	boolean	false	NOT NULL, if blossom resets and repeats every year

`Gained Blossoms`

*Column name*	*Data type*	*Example*	*Additional information*
user_id	uuid	00000000-0000-0000-0000-000000000000	PRIMARY KEY
blossom	text	1	PRIMARY KEY
times_gained	integer	1	NOT NULL, 0 to infinity
gained_date	date	2023-04-10	NOT NULL

`Base Layer Images`

*Column name*	*Data type*	*Example*	*Additional information*
id	uuid	ae245486-fe22-4e59-aed8-d5981d245d1c	PRIMARY KEY
path	text	/Photos/Vineyard.jpg	NOT NULL
rotation	real	0	NOT NULL
scale	real	100	NOT NULL
layer_id	uuid	e09a5af0-2f84-4cef-ae75-8bcc66862be3	NOT NULL, FOREIGN KEY to `layers.id`

`Drawings`

*Column name*	*Data type*	*Example*	*Additional information*
id	uuid	ae245486-fe22-4e59-aed8-d5981d245d1c	PRIMARY KEY
shape_type	drawing_shape_type	rectangle	NOT NULL
add_date	date	2024-04-04
remove_date	date	NULL
rotation	real	0	NOT NULL
scale_x	real	1	NOT NULL
scale_y	real	1	NOT NULL
x	integer	460	NOT NULL
y	integer	645	NOT NULL
properties	jsonb	{"color": "#000000", "width": 275.0, "height": 233.0, "fillPattern": "none", "strokeWidth": 3.0}	NOT NULL
layer_id	uuid	9b77d9b1-02c3-4fac-97bf-686b9f4ea38d	NOT NULL, FOREIGN KEY to `layers.id`

PermaplanT